Understanding "login failed" (Error 18456) error messages in SQL Server 2005
When a client attempts to login to a SQL Server and if SQL server encounters an error that prevents a login from succeeding, the client will be displayed error message as below:
Msg 18456, Level 14, State 1, Server, Line 1
Login failed for user ''
This message is fairly nondescript to prevent information disclosure to unauthenticated clients. The 'State' will always be shown to be '1' regardless of the nature of the problem.
However, to determine the root cause for the failure, DBA can look in the server's error log where a corresponding entry will be written. This entry will have the correct details recorded.
An example of an entry is:
2008-12-27 01:02:00.34 Logon Error: 18456, Severity: 14, State: 2.
2008-12-27 01: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 root of the problem. In the example above, State 2 indicates that the authentication failed of Invalid userid. The common error states and their descriptions are provided in the following table:
Other error states indicate an internal error and may require assistance from Microsoft support. However probability of experiencing such error is extremely low.
Msg 18456, Level 14, State 1, Server
Login failed for user '
This message is fairly nondescript to prevent information disclosure to unauthenticated clients. The 'State' will always be shown to be '1' regardless of the nature of the problem.
However, to determine the root cause for the failure, DBA can look in the server's error log where a corresponding entry will be written. This entry will have the correct details recorded.
An example of an entry is:
2008-12-27 01:02:00.34 Logon Error: 18456, Severity: 14, State: 2.
2008-12-27 01:02:00.34 Logon Login failed for user '
The key to the message is the 'State' which the server will accurately set to reflect the root of the problem. In the example above, State 2 indicates that the authentication failed of Invalid userid. 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 |
16 | User does not have permission on target database |
18 | Change password required |
27 | Server could not recognize the initial database for the login session |
Other error states indicate an internal error and may require assistance from Microsoft support. However probability of experiencing such error is extremely low.
Comments