Msg 18456, Level 14, State 1, Server <server name>, Line 1
Login failed for user '<user name>'


Nothing exiting so far, this is done on purpose, to provide users with as little information as possible. So, the "State"  wil allways be shown as "1".  This is misleading information. The true reason for the login failure can be found on the SQL server error logs, where a simular log exists, but with a little more information in it. It will look something like:


2009-10-15 09:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.2009-10-15 09:02:00.34 Logon     Login failed for user '<user name>'. [CLIENT: <ip address>]


Note that the "State" gives a lot more information than just the singel "1". Now for the real reason, Microsoft has provided a nice table of one of her sites. In the above example the state is 8, indicating that the password is wrong.

n

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 STATEERROR DESCRIPTION
2 and 5Invalid userid
6Attempt to use a Windows login name with SQL Authentication
7Login disabled and password mismatch
8Password mismatch
9Invalid password
11 and 12Valid login but server access failure
13SQL Server service paused
18Change password required


If there are other error states, this may be indicating an internal error. This can be in the SQL security, as well as in a corrupted Index or something like that. Solving that kind of issues may require some deeper debugging on the database itself. Most of the time, the information in the above tabel will be enough.



Printscreen: Not available.


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Ben OostdamBen Oostdam has been working with Windows systems since 1993. Worked for several companies as a system administrator, and is currently a Senior Support Engineer for Qurius Managed Services in the Netherlands specialized in System Center Solutions.

Disclaimer: The information contained in this website/article is for general information purposes only. The information is provided as is, by Ben Oostdam and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk. In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website. Through this website you are sometimes able to link to other websites which are not under my control. I have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them. Every effort is made to keep the website up and running smoothly. However, I take no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control. All entries in these articles, are my individual opinion, or from co-writers and they don't necessary reflect the opinion of my employer.