Understanding the “login failed (Error 18456) error” message in SQL Server 2005.
The “login Failed” messages that are surfaced by the client and written to the server’s error log (if the auditlevel is set to log failures on login which is the default) in the event of an error during the login process.
If the server encounters an error that prevents a login from succeeding, the client will display the following error message.
Msg 18456, Level 14, State 1, Server <server name>, Line 1
Login failed for user '<user name>'
Of note is the State; it will always be shown to be ‘1’ back to the client, regardless of the nature of the problem, to prevent information disclosure to unauthenticated clients. To determine the true reason for the failure, the administrator can look in the server’s error log where a corresponding entry will be written:
2006-02-27 00:02:00.34 LogonError: 18456, Severity: 14, State: 8.
2006-02-27 00:02:00.34 Logon Login failed for user '<user name>'. [CLIENT: <ip address>]
The key to the message is the state which the server will accurately set to reflect the source of the problem. In the example above, state 8 indicates that the authentication failed because the user provided an incorrect password.
Common Error States and Descriptions:
| Error State |
Error Description |
| 2 and 5 |
Invalid User |
| 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. SQL Account has not access to server, try named pipes? |
| 13 |
SQL Server service paused |
| 16 |
Incoming user does not have permissions to log into the target database. Check for default database / changing db id’s |
| 18 |
Change password required |
| 27 |
Server could not determine the initial database for the session. Was a database name specified? Correct spelling? |