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 7 and 1 and type the answer here:
  • Post
  • I am getting an Error: 18456, Severity: 14, State: 11. when my users are triing to make an ODBC connection to SQL 2005 STD from Acess 2007. I have sqitched the SQL server from Windows Authentication mode to SQL server and Windows Authentication mode, this did not help. Any ideas? Thank you.

  • PingBack from http://ghillie-suits.info/?p=23716

  • I have added the sqlserver 2005 instance using sp_addlinkedserver. And when I try to use the linked server inside my query it says login failed for user 'sa'. When I checked at the error log on remote server(where the SQL Server 2005 is installed) the State was 8.

    But I am able to connect to the SQL Server instance from Mgmt Studio Express and also using openrowset distributed queries.

    Can I know the actual problem?

  • I am getting Error 18456 State 8 sporadically for many users. Sometimes they can log on OK, and sometimes not, using the same password. The logins and passwords were migrated from SQL2000 using sp_help_revlogins.

    If I change the password, they can log on using the new password, but later that same new password is rejected. If they try again later, it may work again!

  • I'm having the same problem as Ken.  My databases were restored to sql 2005 server and are in 2000 compatibility mode.  Usually the logins work but occasionally for no apparent reason I get Error 18456 State 8.  I know the password is correct as it is coming from the config file and not changing.

    I tried deleting the users and recreating them by hand but still have the same sporadic problem.

  • SQL Server build is 9.0.3159 by the way

  • Hello,

    I have the same error. What is stange is that the ODBC connection was working last week when I used it and today when I tried again it failed... Any corruption?

    Thanks,

  • Hello

    I have the error 18456 with any users when one specific user is login in to the PC only???

    Test1 on PC 1:

    User A log to the PC

    ODBC with UserA is fine

    ODBC with UserB is fine

    User B log to the PC

    ODBC with UserB failed error 28000-18456

    ODBC with UserB failed error 28000-18456

    Test1 on PC 2

    User A log to the PC

    ODBC with UserA is fine

    ODBC with UserB is fine

    User B log to the PC

    ODBC with UserB is fine

    ODBC with UserB is fine

    What is going on???

    Thanks

    Dominique

  • We have this strange Error:

    [298] SQLServer Error: 18456, Login failed for user 'Domain\DOMAINAdministrator'. [SQLSTATE 28000] We have windows authentication turned on this happens on a MOSS Server with SQL Server 2005

  • hi all,

    i have encountered this login failed error with State 1 using SQL Server 2000. What does state 1 indicate?

    While using the server explorer i had to make a connection where i encountered this error message.

    Please mail me the replies asap to dency_opus911@yahoo.com

  • Hello, I have read technet forum about this error but I still have no answer about state 12. I have tried to give all permissions that I know for 'testlogin' except sysadmin and it has no effect! Please help!

  • I have an Error -18456 but no state level given. It just states Login failed to user.

    Microsoft SQL server Error-18456.

    I am trying to get access to sql server authentication mode. While I am able to get access to windows authentication mode.

    I made shure to choose Mixed authentication mode while installing my sql server 2005 software.

    Can any one help me thanx.

  • I have an Error -18456 but no state level given. It just states Login failed to user.

    Microsoft SQL server Error-18456.

    I am trying to get access to sql server authentication mode. While I am able to get access to windows authentication mode.

    I made shure to choose Mixed authentication mode while installing my sql server 2005 software.

    Can any one help me thanx.

  • I think you will find that you have a sql security group or user which has a default database set which it no longer has access to.

    If you look in the logs are you getting a successful login followed immediately by a failed login? that's what I had and it was a local group that the user was a member of which had that error. Gave public access to the db and done.

  • All I just received the same error and found the problem was related to Reporting Services.  You may want to open SQL Server Configuration Manager, shutdown reporting services and retry the sqlcmd.

    I am not certain if this has been mentioned.  I am running Enterprise on Win2003 server.  

    Good luck.

Page 9 of 27 (396 items) «7891011»