In my series of new posts on old topics, I decided to gather today several pieces of information that I think will help in debugging SQL Server login failures. Although most information should remain useful for future versions as well, some of it may become outdated, so I tagged this article as 2005 specific.
Login failures can be broadly divided in two categories: failures to connect to SQL Server and failures to authenticate with SQL Server - I will refer to the first as protocol failures and to the second as security failures. Security failures can only occur after successfully establishing connection to the database server. Most security failures result in the 18456 error, which will always be logged by SQL Server. This means that to determine if you are likely hitting a security error, you should check the current ERRORLOG file to see if an 18456 error was logged there - the absence of such an error usually indicates the problem is elsewhere - you either failed connecting or there was some application error that was reported as a login error.
The ERRORLOG file is a text file located in the LOG folder, usually situated in the same place as the DATA folder containing the system databases. You can always find the actual location by checking the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.OOO\CPE\ErrorDumpDir, where OOO should be replaced by the proper database instance number.
For protocol errors, I recommend two resources:
MSDN Forum for the SQL Server Protocols TeamSQL Server Protocols Team Blog
In what follows, I will focus more on discussing the 18456 error. This error can be raised for a variety of reasons and the most important information in debugging it is to know the state value for the error. This is not obvious because the error state is always displayed to clients with a value of 1, to prevent information disclosure to unauthorized parties. The real error state value, however, is always logged in the ERRORLOG file, as in the examples below:
2008-01-28 10:34:19.02 Logon Error: 18456, Severity: 14, State: 8.2008-01-28 10:34:19.02 Logon Login failed for user 'sa'. [CLIENT: <local machine>]...2008-02-14 18:23:10.10 Logon Error: 18456, Severity: 14, State: 11.2008-02-14 18:23:10.10 Logon Login failed for user 'Domain\User'. [CLIENT: <local machine>]
Some of the most obvious states are described in this classic 18456 post from the Protocols Team Blog, which pretty much already covered this topic. The comments to the post uncover a few more states. With this knowledge, the first failure above is easily explained as being due to the use of an incorrect password, while the second failure is due to the account not having been granted access to the server.
Knowledge of the error state is crucial in finding out the possible reason of an 18456 failure, so it should be the first piece of information you gather before reporting the problem.
For security issues, the main resources are:
MSDN Forum for SQL Server Security TeamSQL Server Security Team Blog
So, when dealing with some sort of login failure, try to determine where it happened: in the application, in establishing a connection to the server, or in authenticating with the server. Checking the SQL Server error log can help you determine if you reached the server or not, and it can also provide you with additional information about the error, which is not available elsewhere. If you are hitting an 18456 error and the state is described in the Protocols post, then you should have a good idea of what the problem is; if the state is not listed, then you should post the state when you are reporting the error - I suggest to try the SQL Server Security Forum first.