Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Rate This

In continuing with the theme of understanding error messages I'll discuss the "login failed" messages that are surfaced by the client and written to the server's error log (if the auditlevel is set to log failures on login which is the default) in the event of an error during the login process.

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 <server name>, Line 1
Login failed for user '<user name>'

Note that the message is kept fairly nondescript to prevent information disclosure to unauthenticated clients.   In particular, the 'State' will always be shown to be '1' regardless of the nature of the problem.  To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written.  An example of an entry is:

2006-02-27 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.

2006-02-27 00:02:00.34 Logon     Login failed for user '<user name>'. [CLIENT: <ip address>]

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

 
Other error states indicate an internal error and may require assistance from CSS.
 
Il-Sung Lee
Program Manager, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post
  • check your sql server authentication mode.

  • On one machine we have This problem:

    SQL Server on server log reports failed login codes:

    Error: 1846

    Severity: 14

    State: 11

    But on other we do not.

    The machine...

    SQL Server 2008 R2 64x V10.50.2500.0

    Running on VMWare

  • Please visit [URL="http://www.happysql.com/"]www.happysql.com[/URL] . Login failed for user 'sa'. Reason: Password did not match that for the login provided.  My servers were 32 and 64 bits I purchased happysql software from happysql.com because this Software to create IP security Policy automatically and Block IP attack over SQL server for 24 x 7. Safety from SQL Hackers and Your answer to Hacking Attempts. The program is designed to loop through the event log and locate IP’s that attempt to hack your SQL database. It gives guaranteed water tight security for blocked IP. Once the IP is put on the list, you should not be seeing any additional attacks from that IP since the IPSec policy handles connections at the lowest level.

  • Hello,

    On SQL Server 2005 SP3

    One time I had the same error 18456, but with no Level and State.

    After a look at Windows Events it seems to be a rights trouble.

    I create my domain\account to the SQL Server logins with sysadmin role.

    Then start SQL Agent like a charm !

  • Here's another link that covers the same subject with slightly more explanation (screenshots) www.katieandemil.com/microsoft-sql-server-error-18456-login-failed-for-user

  • hi, I have a problem sqlstate 28000, sql error server 18456. what i should do??? i really need to run this program. with this program do the orders of the day to day maintenance of the company. thanks, regards.

  • Love it!! The errors are kept non descript for security reasons to keep people from understanding why it's failing, but here are the reasons for the failures. Thank goodness hackers can't use google :) Here's an even better option, to stop the possibility of the wrong people having access, stop all authentication. Sheez MS, could you get any dumber??

  • I've got a state: 14 with Sql Server 2008 R2 accessing from Excel 2003. Can you help? Thanks a  lot

  • right click mouse->run as administrator...

  • Excellent post.  I was fumbling until I saw this.   It would be good to repost and update the list of States or at least point to where to find the full list.   I found that 58 means the user is logging in with SQL User credentials but the database server is configured to only use Windows Login credentials.

  • Reset the password; try it and change the passw back. worked for me.

  • try RUN AS ADMINISTRATOR  in order to connect using windows authentication

  • Dear All,

    I had faced same issue in my SQL Server 2008R2 SSMS and i have fixed the same have gone through SQL logs and I have analyzed that "Issue is related to authentication".

    So I have run SSMS.exe as administrator and it’s allowed me to login.

    Cheer guys!!!!

    Regards

    Gaurav Chauhan

  • sql server 2005 has error 18456 severity 14 state 16 login failed for user 'NT AUTHORITY\SYSTEM' - this user has sysadm and everything else, why is there an error several times a minute?  Ive checked security / roles / ... All is in place.  

  • I have the problem in sql server 2008

    error:18456

Page 25 of 27 (396 items) «2324252627