Troubleshooting: Login Failed for User 'x'
When a connection attempt is rejected because of an authentication failure that involves a bad password or user name, a message similar to the following is returned to the client: "Login failed for user '<user_name>'. (Microsoft SQL Server, Error: 18456)".
Additional information returned to the client includes the following:
"Login failed for user '<user_name>'. (.Net SqlClient Data Provider)"
------------------------------
"Server Name: <computer_name>"
"Error Number: 18456"
"Severity: 14"
"State: 1"
"Line Number: 65536"
The following message might also be returned:
"Msg 18456, Level 14, State 1, Server <computer_name>, Line 1"
"Login failed for user '<user_name>'."
To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.
State | Description |
|---|---|
2 | User ID is not valid. |
5 | User ID is not valid. |
6 | An attempt was made to use a Windows login name with SQL Server Authentication. |
7 | Login is disabled, and the password is incorrect. |
8 | The password is incorrect. |
9 | Password is not valid. |
11 | Login is valid, but server access failed. |
12 | Login is valid login, but server access failed. |
18 | Password must be changed. |
Other error states exist and signify an unexpected internal processing error.
In this example, the authentication error state is 8. This indicates that the password is incorrect.
Date | Source | Message |
|---|---|---|
2007-12-05 20:12:56.34 | Logon | Error: 18456, Severity: 14, State: 8. |
2007-12-05 20:12:56.34 | Logon | Login failed for user '<user_name>'. [CLIENT: <ip address>] |
 Note |
|---|
When SQL Server is installed using Windows Authentication mode and is later changed to SQL Server and Windows Authentication mode, the sa login is initially disabled. This causes the state 7 error: "Login failed for user 'sa'." To enable the sa login, see How to: Change Server Authentication Mode. |
