Msg 18456, Level 14, State 1, Server <ServerName>, Line 1 Login failed for user '<Name>'
You might have come across this instance of error within your environment:
Msg 18456, Level 14, State 1, Server <ServerName>, Line 1 Login failed for user '<Name>'
The above error used to drive me nuts when the users complained by specifying the error number.
But not now as I have developed a procedure to dig out more into the root cause of the error:
By default login failed error message is nothing but a client user connection has been refused by the server due to mismatch of login credentials. First task you might check is to see whether that user has relevant privileges on that SQL Server instance and relevant database too, thats good. Obviously if the necessary prvileges are not been set then you need to fix that issue by granting relevant privileges for that user login.
Althought if that user has relevant grants on database & server if the Server encounters any credential issues for that login then it will prevent in granting the authentication back to SQL Server, the client will get the following error message:
or
Msg 18456, Level 14, State 1, Server <ServerName>, Line 1
Login failed for user '<Name>'
Ok now what, by looking at the error message you feel like this is non-descriptive to understand the Level & state. By default the Operating System error will show 'State' as 1 regardless of nature of the issues in authenticating the login. So to investigate further you need to look at relevant SQL Server instance error log too for more information on Severity & state of this error. You might look into a corresponding entry in log as:
2007-05-17 00:12:00.34 Logon Error: 18456, Severity: 14, State: 8.
or
2007-05-17 00:12:00.34 Logon Login failed for user '<user name>'.
n
As defined above the Severity & State columns on the error are key to find the accurate reflection for the source of the problem. On the above error number 8 for state indicates authentication failure due to password mismatch. Books online refers: By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.
Sung Lee, Program Manager in SQL Server Protocols (Dev.team) has outlined further information on Error state description:
The common error states and their descriptions are provided in the following table:
|
ERROR STATE |
ERROR DESCRIPTION |
|
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 |
|
9 |
Invalid password |
|
11 and 12 |
Valid login but server access failure |
|
13 |
SQL Server service paused |
|
18 |
Change password required |
Well I'm not finished yet, what would you do in case of error:
2007-05-17 00:12:00.34 Logon Login failed for user '<user name>'.
You can see there is no severity or state level defined from that SQL Server instance's error log. So the next troubleshooting option is to look at the Event Viewer's security log as per the screen-shot below:
There you go you can see the reason for failure of this login due to bad or unknown password, check under relevant domain UserManager for complete status of that particular login.