If you can establish a connection to a SQL Server, but are having problems logging in to it, you will get an 18456 error. This error is deliberately obfuscated in SQL Server so that the user can’t tell why the error failed (and thus try to crack into the server). All the user will get back is that the login failed, but not why.

However, being the DBA for this server, you have to troubleshoot this. Which means that you need to know why the login failed. The trick is to examine the SQL Server log. You can examine the log directly from SSMS, from the sp_readerrorlog stored procedure, or from the xp_readerrorlog extended stored procedure. What you are looking for is the error number (18456), and the state. The state is the code that you need for why the login can’t log in to the server.

In 2006, Microsoft published a partial list of the different error states on this msdn blog post: //blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx. In the years since several other error states have been talked about by Microsoft in the comments to this article. This blog post is an effort to consolidate some of this information to make it easier to find what your issue could be faster.

I’m going to start off with the table in the aforementioned blog post. I’ve added to it some of the other error states mentioned, and have elaborated on some of the descriptions. Those error states in orange have more details below the table.

Error State Description
1 Prior to 2005, this is all you would get. 2005+ – you will get this if you don’t have permission to even get an error message
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 (2005+: Passwords are case sensitive)
9 Invalid password
11 and 12 Valid login but server access failure (Windows login is valid, but doesn’t have login access to the server)
13 SQL Server service paused
16 Incoming user does not have permissions to log into the target database, or target database is offline.
18 Change password required
23 Server in process of shutting down while user is trying to log in.
27 Cannot determine initial database for the session
38 (2008+) Specified database is unavailable (security, otherwise). Formerly state 16
40 (2008+) Users default database is unavailable. Formerly state 16.
58 Attempting SQL Authentication on server set for Windows Authentication only.

There are still a lot of missing states – leave a comment below if you have a reference to any others, and I’ll update this list. I prefer a Microsoft Reference.

Troubleshooting the login errors

In the 25 pages of comments on this Microsoft blog are buried some good information for researching/fixing the causes of some of these login errors. Here is a consolidation of the ones that I found useful from what the posters determined to be the cause for them:

State 8

  1. In 2005+, passwords are case-sensitive. See //support.microsoft.com/kb/907284

State 11/12

  1. Check msdn.microsoft.com/…/dd207004.aspx for allowing Windows Login to access SQL Server when the sysadmins are locked out.

State 11

  1. If the account is in the Local Admin group, drop and re-add the account to this group.
  2. Is the account in a group with DENY CONNECT?

State 16

  1. Ensure all schemas in the default database are owned by a valid login.
  2. Ensure database name is properly cased in the connection string.
  3. This error can also be caused by jobs.
  4. Could be caused by the database being in single user (with another connection using it) or restricted user modes.
  5. Ensure that there is a default database for the user.
  6. Using BCP? Don’t use brackets around the server name parameter (-S).
  7. Check for dropped publication / subscriber databases, but replication jobs for those databases still running.
  8. Using SharePoint? Setting it up to utilize Single-Sign-On (SSO)?
    1. While attempting to enable SSO for the first time, if you fail to configure the Microsoft Single Sign-on Service with the proper ‘Service Account’ you will get an error. SharePoint creates a job looking for the ‘SSO’ database, even though it failed to create the database.
    2. Steps to correct the issue:
      1. Open Windows Services
      2. Configure Microsoft Single Sign-on Service to use the proper account
      3. Open Central Administration >> Operations >> Manage settings for single sign-on
      4. Configure properties to use the same account used for Microsoft ‘Single Sign-on Service’
    3. The database (SSO) should be created successfully and the Error: 18456 should stop.

If you have any other causes for this error (any state), please add a comment below.

I hope that this consolidation is helpful to you!