MSSQLSERVER_18456
Details
Product Name |
SQL Server |
Product Version |
11.0 |
Product Build Number |
11 |
Event ID |
18456 |
Event Source |
MSSQLSERVER |
Component |
SQLEngine |
Symbolic Name |
LOGON_FAILED |
Message Text |
Login failed for user '%.*ls'.%.*ls |
Explanation
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 |
|---|---|
1 |
Error information is not available. This state usually means you do not have permission to receive the error details. Contact your SQL Server administrator for more information. |
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. One possible cause of this error is when the Windows user has access to SQL Server as a member of the local administrators group, but Windows is not providing administrator credentials. To connect, start the connecting program using the Run as administrator option, and then add the Windows user to SQL Server as a specific login. |
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.
An additional unusual possible cause
The error reason An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. can be returned in the following situations.
When the server is configured for mixed mode authentication, and an ODBC connection uses the TCP protocol, and the connection does not explicitly specify that the connection should use a trusted connection.
When the server is configured for mixed mode authentication, and an ODBC connection uses named pipes, and the credentials the client used to open the named pipe are used to automatically impersonate the user, and the connection does not explicitly specify that the connection should use a trusted connection.
To resolve this issue, include TRUSTED_CONNECTION = TRUE in the connection string.
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>] |
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 Change Server Authentication Mode.
User Action
If you are trying to connect using SQL Server Authentication, verify that SQL Server is configured in Mixed Authentication Mode.
If you are trying to connect using SQL Server Authentication, verify that SQL Server login exists and that you have spelled it properly.
If you are trying to connect using Windows Authentication, verify that you are properly logged into the correct domain.
If your error indicates state 1, contact your SQL Server administrator.
If you are trying to connect using your administrator credentials, start you application by using the Run as Administrator option. When connected, add your Windows user as an individual login.
If the Database Engine supports contained databases, confirm that the login was not deleted after migration to a contained database user.
When connecting locally to an instance of SQL Server, connections from services running under NT AUTHORITY\NETWORK SERVICE must authenticate using the computers fully qualified domain name. For more information, see How To: Use the Network Service Account to Access Resources in ASP.NET