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 |
|---|---|---|
|
2005-09-13 20:12:56.34 |
Logon |
Error: 18456, Severity: 14, State: 8. |
|
2005-09-13 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. |
