Error 18456 – Understanding “login failed” error messages in SQL Server

One of the almost daily pieces of DBA troubleshooting is to find out why someone (or something) couldn’t login – if you’re lucky, it’s a login failure, and will be in the SQL Server log.

SQL Server doesn’t given the full details back to the client as a security best practice, and even with SQL Server 2005 some of the error codes were cryptic…. hence every DBA will have run across Troubleshooting Error 18456 from the SQL protocols team. However, it hasn’t been updated.

SQL Server has got much much better at providing more informative error messages in the logs – SQL Server 2000 simply reported state 1 for almost everything!

 State Descripton
  
1SQL Server 2000 failure – no further information provided OR this error can occur when a SQL login is used, but the SQL Server is in Windows Authentication only mode
5No permission to login to SQL Server
8Incorrect password – since SQL Server 2005, passwords are case sensitive
10Issue with SQL logins? See KB925744
11Windows user validated, but no server access – sometimes a user is dropped and recreated in AD. Sometimes explicit DENY CONNECT
12Permission to connect to database engine is denied – eg Login failed for user ‘myUSER’
13SQL Server service paused
16No permission to default/specified database OR the default/specified database is not available OR the database name is mis-spelled OR the database is in restricted or single user mode OR this can occur in the time between SQL Server being ready for client connections and the specified database being available after recovery. OR this can occur in SQL2005 or later with schema permission issues
23SQL Server is shutting down
27Initial database could not be determined
38Initial database not available (SQL Server 2008)
40No permission to default database – either fix the default database, or specify the database to open. This error message is SQL Server 2008R2
57The password change failed. The password of the account must be changed.
58Are you trying to use a SQL login with SQL Server 2008 when the server only allows Windows authentication?