Login Failed - SQL 2005

If the server encounters an error that prevents a login from succeeding, the client will display the following error mesage.

Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user ...

But the same message would be logged on Server Error log as

2009-02-05 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8
2009-02-05 00:02:00.34 Logon Login failed for user ''. [CLIENT: ]

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. The common error states and their descriptions are provided in the following table:


ERROR STATE ERROR DESCRIPTION
2 and 5 Invalid userid
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
13 SQL Server service paused
18 Change password required