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>'."
Additional Error Information
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.
Examples
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>] |
.gif) 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. |
See Also