2 Common Errors and Reasons for SQL Server Login Failures

When asked about common errors encountered in a SQL Server environment, you might expect to hear about high CPU issues, but in reality, these are rare and few. In fact, many DBAs report connectivity issues with SQL Server as among the most frequently encountered errors. These errors can further be classified into two sub-categories:

1. Login request not reaching SQL Server.
2. Login request reaching SQL Server and then failing.

Let’s look at each of these scenarios in this article.

Scenario 1: Login request not reaching SQL Server

A typical error received by a client might be:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections

There can be multiple reasons for this error, including these, based on work I’ve done with clients and queries I’ve responded to in user forums:

1. Using the incorrect instance name.
2. The SQL Service is not running.
3. Port not open. (Note that telnet is the best test possible to detect this).
4. The SQL Browser Service not running. (This is needed to get port of named instances. IP, PortNumber – can be specified to identify this issue.)
5. Incorrect DNS entry and request going to different machine. (Note that ping is the best test to find name and IP address mapping).

Scenario 2: Login request reaching SQL Server and then failing

This second scenario results from authentication or security related errors. The error message received by the client would as shown below:

Login failed for user 'username'. (Microsoft SQL Server, Error: 18456)

 

For security reasons (and to a hacker’s disadvantage), SQL Server avoids revealing the exact cause of error message. The way to troubleshoot these errors is to look into the SQL Server Errorlog. The location of the file can be found using SQL Server Configuration Manager.

Under startup parameters, we need to look at -e which stands of path of Errorlog. Below are some error messages which we have seen a lot, taken from SQL Server 2014.

 

2015-06-21 10:58:19.400 Logon        Error: 18456, Severity: 14, State: 8.

2015-06-21 10:58:19.400 Logon        Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

State is very important in the error message. In earlier versions of SQL Server, the “Reason: … ” section was not available, and state was the only way to find the cause. Since it is not easy to remember all states and their meanings, Microsoft has enhanced the error messages in Errolog, and now shows reasons as well.

Here is another state – 38.

 

2015-06-21 11:02:34.150 Logon        Error: 18456, Severity: 14, State: 38.

2015-06-21 11:02:34.150 Logon        Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'foo'. [CLIENT: <local machine>]

 

State 5: Login is invalid.

 

2015-06-21 11:04:01.290 Logon        Error: 18456, Severity: 14, State: 5.

2015-06-21 11:04:01.290 Logon        Login failed for user 'sa1'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

 

State 6: Windows account used for SQL Authentication

2015-06-21 11:42:40.220 Logon        Error: 18456, Severity: 14, State: 6.

2015-06-21 11:42:40.220 Logon        Login failed for user 'domain\user'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: <local machine>]

 

 

State 1: Account is disabled.

2015-06-21 11:44:04.730 Logon        Error: 18470, Severity: 14, State: 1.

2015-06-21 11:44:04.730 Logon        Login failed for user 'foo'. Reason: The account is disabled. [CLIENT: <local machine>]

 

State 7: This would appear if login is disabled AND incorrect password is provided.

2015-06-21 12:02:50.690 Logon        Error: 18456, Severity: 14, State: 7.

2015-06-21 12:02:50.690 Logon        Login failed for user 'foo'. Reason: An error occurred while evaluating the password. [CLIENT: <local machine>]

State 58: SQL running under Windows only mode and SQL login is attempted.

2015-06-21 12:09:30.580 Logon        Error: 18456, Severity: 14, State: 58.

2015-06-21 12:09:30.580 Logon        Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

 

 

Most of the error messages are self-explanatory. There are a few which need some expert advice, including this classic example:

• SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security; the connection has been closed.
• Login failed for user ”. The user is not associated with a trusted SQL Server connection.
• Cannot generate SSPI context.

These error messages would mostly appear when there is a service trying to connect to SQL Server. In the second error message, the user name is empty. Such errors can easily be avoided by using SQL Authentication and using SQL Logins to connect to SQL rather than Integrated Security. This error is most frequently caused by delegation or SPN related issues. This great article from Microsoft has a comprehensive list of steps a DBA should follow in troubleshooting these: https://support.microsoft.com/en-us/kb/811889. The challenge with such messages is that the true causes of the error are generally not controlled by the SQL Server DBA, and require collaboration from other IT teams, such as domain administrators.

What common errors do you encounter? I would love to know some of the errors you have encountered in your environment and what you did to mitigate them.