Error 18456 – “Login Failed” messsages in SQL Server

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>'

The ‘State’ will always be shown to be ’1′ to the client regardless of the nature of the problem – this is to prevent disclosing sensative information to unauthorised clients.  To determine the true reason for the failure, check the server’s error log where a related entry will be written.  An example of an entry is:

2010-12-07 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.
2010-12-07 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.  The common error states and their descriptions are provided in the following table:

Error State Error Description
2 & 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 & 12 Valid login but server access failure
13 SQL Server Service paused
18 Change password required
Advertisements

2 Responses to Error 18456 – “Login Failed” messsages in SQL Server

  1. just used this at work cool!!

    Greetings my Asesino!!

  2. I’ve started to summarise all the error codes, and keep it updated, in one place.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: