I've been learning a few things about troubelshooting login problems over the last few months. I first want to say that I've learned most of this through other blogs, comments etc. At the end of this blog post you will find an URL list where you can look for more details.
It started a few months back where I somewhere read a blog stating that we can use the state in a "login failed" error message to determine why the login failed. As you can imagine, there can be several reasons for login failure (user doesn't exist, pwd doesn't match, windows login name isn't a trusted login etc). For some reason, I didn't save that URL and of course I needed it some time later and couldn't find it.
One place where you can find such a list of states is... Books Online. BOL 2008 documents every error number, so it is a matter of knowing what error number to search for: 18456. You can also search for "login failed" (pretty easy to remember) and the first hit is a different page (from above) but with similar information. I just tried a search in 2005 BOL for the same and had similar hits. This information might have been added to BOL 2005 in a more recent BOL 2005 release, though.
Now, don't be surprised if the client app only receive state 1 for the login failed messages. This is a good thing, and on purpose. We don't want the system to disclosure too much information to a malicous user (app) about why a login fails. So what we do is to look in the errorlog or for the "real" state (the EventLog doesn't seem to carry this information, for some reason). Below is an example from SQL Server 2005, sp2, with timestamps removed:
Error: 18456, Severity: 14, State: 5.
Login failed for user 'gurka'. [CLIENT: <local machine>]
So, state 5 tell us, according to SQL Server 2005 BOL, that "User ID is not valid.".
Now, doing the same in SQL Server 2008 was interesting. Looking in the EventLog, we do see an explanation as to why the login failed. We still don't see the state in the EventLog, but we have an explanation so we might not need the state. And in the errorlog file, I had below message:
Error: 18456, Severity: 14, State: 5.
Login failed for user 'gurka'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
So, not only do we have a state, we also have a decription for why the login failed. Is it pathetic that such a thing can make you happy? Perhaps it is... ;-)
Here are some other blog posts on the subject:
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
http://jmkehayias.blogspot.com/2008/03/understanding-login-failed-error-18456.html
Source Click Here.
Post a Comment