0

I am writing a C# application which connects to a Microsoft SQL Server, chosen by the user, either using Integrated Security or a user-provided Username and Password to connect to the database.

Currently if the user inputs an incorrect password or is using a windows account which does not have permission to access the server, a SqlException is thrown when the SqlConnection is opened.

I wish to provide different feedback to the user dependent on whether a SqlException was thrown due to a failed login, or it was thrown because of another error, for example the server name being incorrect. Currently the only way of finding out why a SqlException was thrown is by looking at the message string, which isn't a very good solution. Is there a way of determining whether the username and password is correct before opening the SqlConnection, or finding out why a SqlException has been thrown?

Currently my connection string is Data Source=ServerName\SQLExpress;User=userName;Password=userPassword; or Data Source=ServerName\SQLExpress;Integrated Security=true; depending on the user's choice.

The code which opens the connection is:

const string ConnectionStringTemplate = "Data Source={0}\\SQLExpress;"; //Basic part of the connection string
const string UserLogInTemplate = "User={0};Password={1}";  //Template for adding user authentication
const string NoAuthString = "Integrated Security=true;"; //If no username/password provided use this

string connectionString;
connectionString = String.Format(ConnectionStringTemplate, serverName);
if (usesAuthentication)
{
    connectionString += String.Format(UserLogInTemplate, userName, password);
}
else
{
    connectionString += NoAuthString;
}
sqlcnn = new SqlConnection(connectionString);

sqlcnn.Open(); //SQLException occurs here if Password incorrect or server name wrong etc.
euanjt
  • 152
  • 10
  • 1
    yes there is a way.. can you show currently how you are validating the user name password in code..? – MethodMan Sep 27 '16 at 15:41
  • Be aware that using the user credentials to log into SQL server can cause [severe performance problems](https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx). – Erik Philips Sep 27 '16 at 15:42
  • in try catch ,based on the error,you can show customized message – TheGameiswar Sep 27 '16 at 15:43
  • @TheGameiswar but the exception is always SQLException and so there is no nice way of determining whether the exception was caused by a failed login or some other reason – euanjt Sep 27 '16 at 15:46
  • @MethodMan I would be curious to see how you can authenticate a sql server username/password combo without attempting to open a connection. – Sean Lange Sep 27 '16 at 15:48
  • @SeanLange: I think the OP's problem is something else, i.e. the connection works fine. – Victor Zakharov Sep 27 '16 at 15:49
  • if you want to validate a user without a db connection then you need to setup a Group in AD `Active Directory` that's one option.. – MethodMan Sep 27 '16 at 15:51
  • @euanjt why not just parse the exception string for "Login failed for" and if it is that then handle it the way you want. – Matt Sep 27 '16 at 15:51
  • and if you want the exact exception then you will need to catch on `SqlException ex for example and return ex.Message` and you can't validate any SQL user without making a connection.. I think you need to read up on Databases and understand a little bit more about connection strings work in regards to connecting to any Database.. – MethodMan Sep 27 '16 at 15:53
  • I highly recommend you don't just manually build the connection string, but instead use the built in [`SqlConnectionStringBuilder '](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder(v=vs.110).aspx) – Erik Philips Sep 27 '16 at 15:53
  • @MethodMan that would work mostly but is not totally robust. I am guessing you are thinking you would check if they are part of an AD group that has access to the sql server? This works in general until the AD group no longer has access. – Sean Lange Sep 27 '16 at 15:53
  • also if you are going to pass params to your connection string.. then were are you actually passing the user name and password.. ? also your issue looks like what I am asking earlier in this comment the connection string it's self.. without showing the password and username show us what the value of the connection string is for this variable when you debug your code.. `UserLogInTemplate ` – MethodMan Sep 27 '16 at 15:56
  • 1
    @Matt: This will work until someone decides to implement better wording in SQL server 2018 and suddenly say "Failed login for". I guess you could change it then and add a clause for SQL server version... :) – Victor Zakharov Sep 27 '16 at 15:56
  • also you can do the parameters all within the .Config File I currently do something like that so that we do not have to hard code 1 single user id and password.. put your connection string in a .Config file – MethodMan Sep 27 '16 at 15:58
  • @Neolisk that is why you have to maintain and update code as the base technologies it is built upon changes and is improved. For that matter what if they stopped calling passwords passwords and starting calling them keys..... – Matt Sep 27 '16 at 15:59
  • 1
    @Matt: That's not a good example. Connection string's API is well documented, people rely on it, nobody would change that. The error wording can be changed in no time, new errors added and old errors removed, as most people don't rely on this. – Victor Zakharov Sep 27 '16 at 16:01
  • @Matt but if there was a SQLUserAuthentificationException exception I could catch (for example), then it would be reasonably likely that my code would work with any version of SQL server, but trying to use human readable message strings is likely to be a maintenance nightmare – euanjt Sep 27 '16 at 16:02
  • @Neolisk okay take your own example from your link about dropping credentials. SQL 2005/2008 sys.database_principals was queried but for 2000 dbo.sysusers was queried......hmmm change of schema/name for system views would be highly relevant change that would if backwards comparability is not supported would break your method just like rewording the exception. Plus you can fuzzy match within the string such as for user/password/login and failed.....it is unlikely the change will be so dramatic as to fully remove some of the core words. Plus very small change if exception description changes – Matt Sep 27 '16 at 16:08
  • @Matt: Guess I would buy fuzzy matching for this purpose. But even then, fixing a connection string is easier. There is always more code to write and less time to do it. – Victor Zakharov Sep 27 '16 at 16:11
  • 1
    @euanjt you could also potentially use the SqlException Number Property (http://stackoverflow.com/questions/6221951/sqlexception-catch-and-handling) and then you would only have to maintain a list of error codes https://technet.microsoft.com/en-us/library/cc645603(v=sql.105).aspx but fuzzy string matching will probably work across multiple RDBMS – Matt Sep 27 '16 at 16:28

2 Answers2

0

Don't let the exception happen.

Validate the user before you try to do anything with it. Validate the server, validate everything you want to validate. Problem with this approach is that you'll never get out of the validation trap, there is always something that can go wrong. I suggest you analyze which issues are likely and try to prevent those, leave the rest out to a generic SqlException handler.

If you do it this way, there is still a small chance your exception will happen due to concurrency, but it will be very rare, so in most cases you don't need to worry about.

Regarding server name being incorrect, this situation is extremely unlikely. In other words, if something goes wrong on a daily basis with your application, <0.001% of the cases it will be the server name.

For a more specific solution, please show some of your code.

Victor Zakharov
  • 25,801
  • 18
  • 85
  • 151
  • how would you propose to "Validate the user before you try to do anything with it" assuming that user is how and under what credentials are being connected to the database and that security appears to be able to be both SQL Authentication and Windows. In other words no connection exists – Matt Sep 27 '16 at 15:56
  • @Matt: If it's a windows account, in most cases you need to validate against AD. For SQL users use smth like this - http://stackoverflow.com/a/356030/897326. For a custom auth, do a query against your custom users table. To check if connection exists - http://stackoverflow.com/questions/17853371/most-efficient-way-to-test-sql-connection-string-availibility – Victor Zakharov Sep 27 '16 at 15:58
  • 1
    yeah that second method requires a CONNECTION to the DB my point is you DON'T have a connection OP wants to connect to DB using the user credentials provided. In order to query system views you would have to establish a non user specific connection that you know will not fail then test the credentials via it. Now you can keep that connection alive to always test with but you have redundant connections. 1 for your lookups and 1 for user specifc....... – Matt Sep 27 '16 at 16:02
  • @Matt: There are two approaches to programming, one is called panic programming, where you validate every possible combination of every argument, validation is 99% of your code, and 1 simple change takes months. There is always overhead, one way or another. And there is another approach - you just write code that works in most cases and fix validation for issues that happen often. For mission critical projects, identify important areas and fix those also. Fixing connection string is almost never a problem in any serious application. – Victor Zakharov Sep 27 '16 at 16:05
  • 1
    @Neolisk but here I don't know what the connection string is until the user tells me the servername/username/password and so there is a reasonable chance they'll have hit the wong key, and I want to tell them which text box they likely made a mistake in (ie wrong server as opposed to wrong login) – euanjt Sep 27 '16 at 16:09
  • 1
    @euanjt: If you really want to validate connection string so badly, parse it yourself and validate over a trusted connection (one you hardcoded in your application that always works). Use regex for example. – Victor Zakharov Sep 27 '16 at 16:12
  • @Neolisk now that you have suggested a second connection I am in more agreement of your method. Was trying to help you suggest it for your method to work when considering SQL authentication. Now the only other issue I see is that it sounds from the last comment like OP is attempting to query multiple servers. So my guess is the OP may not know the servers ahead of time and therefore may not be able to maintain the dedication connection for validation purposes to every server.... But can probably still do the regex ping server or do AD lookup etc..... probably more overhead than letting fail – Matt Sep 27 '16 at 16:18
  • 1
    @Matt: Exactly. An alternative would be having a master server / service, which would know of all possible connections, exposed as dropdown in your app. If decentralized, I agree, it's easier to let it fail. Just think about it - if you let user specify a connection string, they are probably a certified master DBA anyway, they know what they are doing. If they are not, don't give them an option to specify connection string. Instead let them them specify user/password pair and compose a connection string yourself. It's better of course to use AD or custom auth, makes things so much simple. – Victor Zakharov Sep 27 '16 at 16:25
  • @Neolisk I am composing a connection string myself, based on user input, but I don't know whether that connection string will contain a valid server name or username/password combination – euanjt Sep 27 '16 at 18:19
  • @Matt Yes I don't know the servers ahead of time, and as part of the user requirements I have to handle a situation where the program is running on a computer with no internet connection and on which the program has not been run on before, with the only thing I can assume being that there is a running Microsoft SQL-Server instance on the computer- this is why I have to let the user input their server name, user name and password and then try to connect to the server. As a result I cannot use any solution which requires a user name or password to be stored before hand. – euanjt Sep 27 '16 at 18:24
  • 1
    @euanjt if you are requiring sql-server instance local you more than likely you can assume servername as "localhost" instead of asking for it. As far as user name and password can you require a trusted connection and windows credentials? You would rarely have a wrong servername exception for any kind of normal configuration and you won't have an incorrect user name/password anymore you would only have the access denied error to contend with.... just a thought – Matt Sep 27 '16 at 18:32
  • @Matt unfortunately I also have to deal with the server being on another computer on the same network, hence why I ask for the server name, and my users want to be able to use SQL authentication as well as windows credentials – euanjt Sep 27 '16 at 18:34
  • @euanjt: If you expect your users to have a configured SQL server in their network, you don't need to worry about validating their connection string, just show them the SQL error as it is. Using car analogy, if your user can disassemble the car engine, and assemble it back, you don't need to tell them if their oil is bad or not. For local connections, I believe a period "." should work instead of localhost, to save typing. Overall, I'm with Matt on this. – Victor Zakharov Sep 27 '16 at 18:37
0

Use SQLExcpetion.Number

The SQLException thrown contains a Number property.

If the login failed then Number is 18456, if the server could not be found then Number is -1. Other error numbers are the same as those given by SQL-Server and can be found at https://technet.microsoft.com/en-us/library/cc645603(v=sql.105).aspx

(with thanks to Matt)

euanjt
  • 152
  • 10