One of my favorite errors that I get paged for is the login failed (error 18456). I have a few users who are guaranteed to ping me at least once or twice a week for a failed login. Generally I can tell that the user is simply trying to login to the wrong server, since we have a standardized naming format of CCLLTT## for our servers that results in company/location/type/number for identification, developers can easily transpose one letter and attempt to login to a production server intending to login to a development one by a similar name.
Doing a search for the error will bring back the SQL Process Blog
which is where the below information comes from. The table on the blog entry however is not updated, so I am going to summarize and update here.
In SQL 2000, Error 18456 always returns State: 1 which is not very helpful. However, SQL 2005, and 2008 make use of the error state to identify what the specific login problem was.
But what about when you get a random failed login that you don't know about or expect. It is up to the DBA to decode the state of the error message to identify exactly what happened. Error's for a failed login are similar to:
2008-02-27 08:32:16.22 Logon Error: 18456, Severity: 14, State: 2.
2008-02-27 08:32:16.22 Logon Login failed for user ''. [CLIENT: < ip >]
|State ||Meaning |
|2 and 5 ||Invalid userid |
|6 ||Attempt to use a Windows login name with SQL Authentication |
|7 ||Login disabled and password mismatch |
|8 ||Password mismatch |
|9 ||Invalid password |
|11 and 12 ||Valid login but server access failure |
|13 ||SQL Server service paused |
|16 ||The target database could not be determined |
|18 ||Change password |
|23 ||The server is in the process of shutting down. No new connections are allowed. |
|27 ||The server could not determine the initial database for the session. |