It is now easy track Logon failures in SQL Server 2008

Almost every Production Support DBA spends considerable amount of time trying to find out the root cause of Error: 18456, Severity: 14. This is the famous error code for Login Failures. Suppose a user tries to connects to a SQL Server 2005 instance and reports that he is unable to connect. The user does not know whether he has access to the database or had entered the wrong password or the database he trying to connect is available or not. When the DBA is notified of this error, the first thing he would do is look in the SQL Server Error Log. Error Log shows this information.

Error: 18456, Severity: 14, State: 8
Login failed for user 'UserName'

Wah! DBA know the Login had failed for that user! He is left with only some Error Codes and he is supposed to know what they mean by heart. I had posted about these Error Codes sometime back here. After going through the Error Codes State:8 means Password mismatch. I always felt why this cannot be simplified. Instead of these Error Codes why there an be an entry in the error log which could be easily understood by a human being? Looks like while I was still thinking about it, someone in the SQL Server Development team had already did it.

Yes. The Error Log of SQL Server 2008 is more meaningful now. To simulate this I tried connecting to an instance using a wrong password for SA. The Error Log looked like this.

Doesn’t this make more sense? Now the Login failed for….. message also has a description that Password did not match that for the login provided. No more decoding the Error Codes. All the information the DBA needs in plain English!

This little extra feature for sure will save some time for the DBA troubleshooting a Login failure issue.

One thought on “It is now easy track Logon failures in SQL Server 2008

  1. Prabodh

    Truly useful for some lazy DBAs like me 🙂 i always do an google for error states for logon failure but never did an effort to keep them documented or get it by heart which is even more tough job for me ..

Comments are closed.