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 4 and 5 and type the answer here:
  • Post
  • This Error is completly related to SQL Server authontication. This will ome where there will be a mismatch in the Existing Password and the Given passowrd.

    I faced this issue when I changed the SQL Server start up account.

    sinishkg@hotmail.com
  • I have error state 10 what does that mean?
  • Hi, Nam

    This forum can help you.

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

    Ming.
  • Hi,

    just a hint for those with state 8 (wrong password):

    With SQL 2005, the passwords are CASE-SENSITIVE, see http://support.microsoft.com/kb/907284
  • Hi,

    08/01/2006 02:28:39,Logon,Unknown,Error: 18456 Severity: 14 State: 23.

    08/01/2006 02:28:47,Logon,Unknown,Error: 18456 Severity: 14 State: 16.

    We had the problem with error state 16 and 23 on our SQL 2005 (64 bits). The error came and gone by itself, any ideas?

    Tan

  • We get this error trying to connect using tcp/ip. sa can connect using tcp/ip, but all other sql logins fail. This began after creating a TCP ENDPOINT listening on port 1090. The endpoint has been dropped, server restarted. Cannot get sql logins to connect using tcp/ip.

    Login failed for user 'PrefAdmin'. [CLIENT: ...]
    Error: 18456, Severity: 14, State: 12.

  • re: can't connect using tcp/ip - when the logins are in the sysadmin role, they can connection via tcp/ip - otherwise they cannot.
  • Hi, Steve

       The error state 12 indicates that your sql account has no access to the server, have you grant login to it? Can you also try connecting over named pipe, what happens?

       Overall, this is not connectivity issue, the following forum can help you w/ the specifiy sql security problem.

       http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

    Good Luck!
    ming.
  • I get the following error messages in the sql server error log

    Source Logon

    Message
    Error: 18456, Severity: 14, State: 11.

    and

    Source Logon

    Message
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]

    The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages.

    Thanks for your help in advance...
  • I tried to connect to the SQL Server 2005 Express edition Microsoft Server Management Studio.

    I gave the followign:
    Authentication: SQL Sever Authentication
    Login: sa
    Password:

    It gave me error 18456
    On checking the log, following entry was found
    2006-09-12 14:18:19.20 Logon       Error: 18456, Severity: 14, State: 7.
    2006-09-12 14:18:19.20 Logon       Login failed for user 'sa'. [CLIENT: <local machine>]

    Please let me know how to allow sa to log in using SQL authentication.

    Regards
    Vineet Dewan
  • I resolved my issue with the sa login 'state 8' by unticking the enforce password policy in the properties of the sa user, this is for SQL2005 only.
  • Did someone figure out the State: 1 issue?
    I am starting my SQL server on sigle mode and trying to login under the account that installed the server but still I get this error:

    2006-09-19 13:52:29.29 Logon       Error: 18461, Severity: 14, State: 1.
    2006-09-19 13:52:29.29 Logon       Login failed for user 'ASSUREONB\AssureonManager'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

    In
    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
  • Hi, Carmen

       This is more SQL security area, you can post your question in following forum, many experts can help you shortly:

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

    Good Luck!
    Ming
  • I have an issue with the report server.  We used to have a web farm and not scaled down to a single  sql server with the reporting components.

    The problem is when I enable windows authetication through IIS it is trying to access the page via "domain\servername$" from client IP x.x.x.x.  The server name is previous sql reporting services install.  It is no longer installed on there.  

    I never had reporting services configured until now.  How can I have the report server use the domain user credentials rather than "domain\servername$"?

  • Error: 18456, Severity: 14, State: 5

    would anyone know how to correct this error?

Page 2 of 27 (399 items) 12345»