Help: SQL Server

“Login failed for user” is one of the most common error which everyone might have seen at least once. In this blog, I am going to share few possible causes of the error and their solution. First, we need to understand that due to security reasons, SQL Server doesn’t send more information about this error message to client. Here is what client would see in all situations (I have done it from SSMS)

TITLE: Connect to Server
——————————
Cannot connect to .\SQL2014.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘sa’. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

If we click on more details, we would see this

——————————
Server Name: .\SQL2014
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

This error doesn’t tell “exact” reason about login failed for user. SQL Server deliberately hides the nature of the authentication error and gives State 1.

The very first thing which I always ask is look at ERRORLOG and find error at exact same time. By default, auditing of failed logins is enabled. In this case, the true state of the 18456 error is reported in the SQL Server Errorlog file. You can verify them by Right Click on Server node > Properties > Security and check

Here is what we would see in ERRORLOG is failed login auditing (option 1 and 3 in above image) is enabled.

2014-07-08 04:37:07.910 Logon        Error: 18456, Severity: 14, State: 8.
2014-07-08 04:37:07.910 Logon        Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

It is important to note that in earlier version of SQL Server (before SQL 2008), the error message was something like below.

2014-07-08 04:37:07.910 Logon        Error: 18456, Severity: 14, State: 8.
2014-07-08 04:37:07.910 Logon        Login failed for user ‘sa’. [CLIENT: <local machine>]

Notice that reason was not shown in earlier version of SQL in ERRORLOG. In those days, we used to keep a track of all states and their meanings but thankfully, I don’t need it any more. Here is the quick cheat sheet which I have saved. You can easily reproduce them. I have given meaningful login name.

State 5: Incorrect login name provided.

2014-07-08 05:35:48.850 Logon        Error: 18456, Severity: 14, State: 5.
2014-07-08 05:35:48.850 Logon        Login failed for user ‘InvalidLogin’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

State 7: Account disabled AND incorrect password.

2014-07-08 05:41:30.390 Logon        Error: 18456, Severity: 14, State: 7.
2014-07-08 05:41:30.390 Logon        Login failed for user ‘DisabledLogin’. Reason: An error occurred while evaluating the password. [CLIENT: <local machine>]

Notice that state 7 would come if account is disabled and incorrect password is supplied. If correct password is supplied for disabled login then client would get below error (18470)

2014-07-08 05:41:22.950 Logon        Error: 18470, Severity: 14, State: 1.
2014-07-08 05:41:22.950 Logon        Login failed for user ‘DisabledLogin’. Reason: The account is disabled. [CLIENT: <local machine>]

State 58: SQL account used to attempt to login on “Windows Only” authentication mode SQL instance.

2014-07-08 06:21:36.780 Logon        Error: 18456, Severity: 14, State: 58.
2014-07-08 06:21:36.780 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>]

Above can be fixed by this blog by Pinal (b|t|f)

State 11: SQL login account getting deny permission via some group membership.

2014-07-08 06:21:36.780 Logon        Error: 18456, Severity: 14, State: 11.
2014-07-08 06:21:36.780 Logon        Login failed for user ‘SQL_Login’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

State 12: Windows login account getting deny permission via some group membership or UAC.

2014-07-08 06:21:34.840 Logon        Error: 18456, Severity: 14, State: 12.
2014-07-08 06:21:34.840 Logon        Login failed for user ‘domain\user’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

To solve state 11 and 12, it is important to find how we are getting deny permission for that account. In most of the cases, it might be due to UAC and this should work but it’s only for local connections.

GRANT CONNECT SQL TO [DOMAIN\User]

In few cases, we can run below query to find if any group has deny permission.

SELECT prin.[name]
    ,prin.type_desc
FROM sys.server_principals prin
INNER JOIN sys.server_permissions PERM ON prin.principal_id = PERM.grantee_principal_id
WHERE PERM.state_desc = 'DENY'

If you find any other state, post in comment, I would enhance the blog further and add their cause.

Hope this helped!