Occasionally it happens that someone is unable to log in to SQL Server because they mistyped the password, have no permission, etc. This is not a problem – as long as we know what is the blocking issue. But how about someone being cocksure they’re trying the correct user, password ,server, etc and still failing? The most straightforward solution is my favorite, reading the SQL errorlog, once again. If the server is set to audit failed logins (and this is the default), you can find the error in the errorlog as well, not on the client side only:
Msg 18456, Level 14, State 1, Server DEMOSQL1, Line 1
Login failed for user ‘tygger’
Actually, something better gets in to the log, but first, let’s analyze the first line, that is ,the first three numbers of the error. the first one is the error number, the identifier of the error; the second is the severity, that is, how bad it is. The bigger the number the worse it is. If you see something above 19, you might be in real trouble. The third number is the state, which is an interesting species. This can be used to provide diagnostic information, like throwing an error with state 1 from an SP and with state 2 from parameterized query. It can make DBAs (and developers) life easier. Now back to that errorlog!
2009-07-27 14:02:02.21 Logon Error: 18456, Severity: 14, State: 8. 2009-07-27 14:02:02.21 Logon Login failed for user 'tygger'. [CLIENT: 10.10.10.1]
Feel the difference: that state value is obviously different from what the client saw – and this is intentional. SQL Server won’t disclose any info which may help a malicious person during an attack. But if there’s a real need for help, you can go to the sysadmins and ask them to check the log what was the reason of the failure. They can check for the status values in the errorlog. And here’s the resolution:
|2, 5||Invalid username|
|6||SQL authentication was attempted with Windows login name|
|11, 12||correct login but no server access|
|13||SQL Server service paused|
|16||Correct login but no access to the selected (or default) database|
|18||User must change password|
|23||Server is shutting down, only sysadmins can log in|
|27||Correct login but server cannot determine an initial database|
|38|| Opening explicitly specified database failed (16 in SQL 2005)|
|40|| Opening login default database failed (16 in SQL 2005)|
Some of these are not obvious (some of them I have no idea about:), so here’s some help:
State 16 in SQL 2005, 38/40 in 2008 is caused most commonly by auto close databases. This is a great feature – for MSDE or Express but not for production. So I recommend you checking if you have any database with auto close enabled and if you have, turn this off – you have just problems with it. Other than this login issue (which will disappear if you retry as the database will be reopened in a few seconds, so a subsequent user can use it immediately) it pollutes your precious errorlog with messages like this: “Starting up database XYZ” .
State 11/12 are typical Windows login issues: SQL Server knows who tries to log in, authentication is successful, but there’s no SQL login for the Windows login, authorization fails.
This post was written based on the SQL Protocol team blog post and the discussion after it, as it was pretty embarrassing that there’s not a single consolidated list of errors, but many of them should be collected from the comments – so I put the list together.