I had strange problems today with “Login Failed” messages from an ASP.NET application using SQL Server authentication. Mixed mode is enabled in the server; the user ID and password were correct; it wasn’t a remote connection and those were enabled in any case. Exact roles / permissions were identical between development and production. All the usual suspects covered.
Since it was only an issue connecting to my test development database, I finally said “getting work done is better than understanding what’s happening here” and created a new user and login, and changed my connection strings. Now it worked on one of the two databases, but not the other.
For security reasons, the exception that SQL Server raises isn’t very informative, just “Login Failed for User FUBAR”. But the information exists: it’s in the MSSQL\LOG folder for your SQL Server instance (C:\Program Files\Microsoft SQL Server\MSSQL.1 in my case). Sort that folder by date / time and grab the most recent ERRORLOG*.* file, which is probably just named ERRORLOG without an extension. Open it in Notepad and look for the error, and it will show you something along these lines:
2007-12-13 13:42:13.92 Logon Error: 18456, Severity: 14, State: 8.
2007-12-13 13:42:13.92 Logon Login failed for user ‘FUBAR’. [CLIENT: 192.168.170.159]
The key here is the State code. Code 8 indicates “Invalid password”. In my case, duh, I had fat-fingered an extra character onto the password in my web.config file and although I’d looked at it twice, I simply could not see it until I looked the third time.
How about the other possible state codes? Glad you asked. They’re here.
{ 1 comment… read it below or add one }
Man, MS really do not understand helpful error message. How hard would it have been to tack a description at the end of the log entry.
I am glad that they have error codes, severity, state and the like, because that can be used to gain additional information (i.e. easier to search for on google). But diskspace is cheap, and a couple dozen extra characters in a log entry is not going to fill it that much faster.
I can’t decide if it is laziness or incompetence, but this seems to exemplify their attitude toward error reporting. (Although it’s not as bad as “Catastrophic Failure” in Visual Studio).