Error/Event 18456 explained

  • Article

Sometimes you may experience that your application, stand alone or web, can't connect to SQL Server.
This error is simply reported as 18456, and the error text is "Login failed for user ‘username'"

Well, you already had understood that you could not login in, since your login failed.
So the error message is not very helpful. Now, there is good reason for this, and that is security.
For example, if the message would be something like ‘Password incorrect for user ‘User'", then a person
that is trying to gain access to your server would have gotten a confirmation that this user exists, making
it easier for them potentially gain access since they now only have to focus on the password instead of figuring
out if the user exists at all.

So, for example, if trying to access with an existing user called ‘AUser' with an incorrect password using sqlcmd,
you will in the command prompt see the following:
 
Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user 'AUser'.
 
and in the Event Viewer you would have:
 
Event Type: Failure Audit
Event ID: 18456
Login failed for user 'AUser'. [CLIENT: xxx.xxx.xxx.xxx]

It does not tell us much, except that the login failed for the user 'AUser'.
 
Hopefully this is your SQL Server, and you know the DBA (or you are the DBA), then in order to get a little bit more
information on why you failed to login is to consult the SQL Server log.
This is normally found at: C:\Program Files\Microsoft SQL Server\<instancename>\MSSQL\LOG\ERRORLOG
 
Checking the last entry in the log, we will will see the following:

Logon Error: 18456, Severity: 14, State: 8.
Logon Login failed for user 'AUser'. [CLIENT: xxx.xxx.xxx.xxx]
 
As you can see, this contains a littlbe bit more information, such as Severity and State.
Now, have a look at:
"SQL Server 2005 Books Online (September 2007) - Troubleshooting: Login failed for user 'x'"
https://msdn.microsoft.com/en-us/library/ms366351.aspx
 
Here it is stated that State 8 means "The password is incorrect."
So while this not a complete list of potential reasons for the failed login, this should make it a lot easier to understand why your login fails.