Error 18456, Level 14, state – SQL Server login errors

(Magyarul itt)
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:

ERRORDESCRIPTION
2, 5Invalid username
6SQL authentication was attempted with Windows login name
7Disabled login
8Bad password
9Invalid password
11, 12correct login but no server access
13SQL Server service paused
16Correct login but no access to the selected (or default) database
18User must change password
23Server is shutting down, only sysadmins can log in
27Correct login but server cannot determine an initial database
38[2008] Opening explicitly specified database failed (16 in SQL 2005)
40[2008] 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.

4 Comments

  1. Vizardo:

    I was getting the following error message when I try to loging into SQLServer 2005 using userid:sa (SQLServer Credentials).

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Miscrosoft SQL Server, Error:233)

    Server logs the following error message
    Login failed to user ’sa’[CLIENT ]
    Login Error:18456. Severity:14, State:8

    After spending almost day with frustration and looking into various postings, just before un-installing and re-installing I tried the following

    – Logged into the SQLServer using Widnows authentication with admin permissions on the databse
    – Changed the password (Make sure that do not cut past, just type the password)
    – Tried logging into the database as user id ’sa’ (Make sure that do not cut past, just type the password).
    – Everything went successful

    I guess that problem was, I was cutting and pasting the password. which SQLServer doesn’t like it. When you reset the password,
    do not cut and paste the password and just type it.

    Thanks,
    Soma.

  2. Erik:

    Cool hint, thanks. An additional one: if you’re dealing with login/connection issues, use sqlcmd if possible. It helps you sorting out protocol and password issues because it’s just so simple.

    Erik

  3. Troubleshoot Repeated Login Failures | Kevin E. Kline:

    [...] Microsoft provides a pretty good description of the states of error 18456 here, but it leaves out a few things.  (You’ll get more useful info if you read all of the comments too).  But again, Erik comes to the rescue by providing a complete and concise list of error 18456 states here. [...]

  4. Kevin Kline : Troubleshooting Repeated Login Failures on SQL Server:

    [...] Microsoft provides a pretty good description of the states of error 18456 here, but it leaves out a few things.  (You’ll get more useful info if you read all of the comments too).  But again, Erik comes to the rescue by providing a complete and concise list of error 18456 states here. [...]

Leave a comment