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 8 and type the answer here:
  • Post
  • Hi @all

    We have a SBS2K3 with SQL2k5.

    On every Startup we get the following Error:

    Ereignistyp: Fehlerüberw.

    Ereignisquelle: MSSQL$SHAREPOINT

    Ereigniskategorie: (4)

    Ereigniskennung: 18456

    Datum:  17.10.2006

    Zeit:  00:20:25

    Benutzer:  NT-AUTORITÄT\NETZWERKDIENST

    Computer: PDC

    Beschreibung:

    Fehler bei der Anmeldung für den Benutzer 'NT-AUTORITÄT\NETZWERKDIENST'. [CLIENT: 192.168.2.250]

    Weitere Informationen über die Hilfe- und Supportdienste erhalten Sie unter http://go.microsoft.com/fwlink/events.asp.

    Daten:

    0000: 18 48 00 00 0e 00 00 00   .H......

    0008: 0f 00 00 00 50 00 44 00   ....P.D.

    0010: 43 00 5c 00 53 00 48 00   C.\.S.H.

    0018: 41 00 52 00 45 00 50 00   A.R.E.P.

    0020: 4f 00 49 00 4e 00 54 00   O.I.N.T.

    0028: 00 00 07 00 00 00 6d 00   ......m.

    0030: 61 00 73 00 74 00 65 00   a.s.t.e.

    0038: 72 00 00 00               r...    

    The Client: 192.168.2.250 is the SBS itself with the internal LAN NIC (the SBS have 2 NIC´s for WAN and LAN)

    The NT-Authority\Networkservice is in Logon of the DB and have dbcreate and dbsecurity rights.

    What can we do?

    regards.

  • I have the following error, no severity displayed, I can't ping IP address and it does not belong to us.

    Any hel will be appreciated.

    Event Type: Failure Audit

    Event Source: MSSQLSERVER

    Event Category: (4)

    Event ID: 18456

    Date: 19/10/2006

    Time: 09:27:26

    User: N/A

    Computer: INET

    Description:

    Login failed for user 'sa'. [CLIENT: 81.27.111.162]

  • I have a VB5 app that's providing the login/password through an ADO connection string, and been unable to establish a successful remote server connection to SqlExpress. Mine keeps going back and forth between state 16 and state 8, and I assure you the password being provided is correct. What gives with this authentication?

  • We just migrated to a new sql server with SQL Server 2005 installed (we moved from 2000).

    We are experiencing two issues that we cannot resolve:

    1. The site and database clients that use this SQL Server run very slow now. We have noticed that the lsass.exe process consistantly uses 25% CPU, +/- 10%. When we stop SQL server the lsass.exe process goes down to 0. What can be causing this? The website uses a SQL user account, not a windows user account.

    2. We have an error in the event log that might be related to the issue above. It appears every few minutes:

    Event Type: Failure Audit

    Event Source: MSSQLSERVER

    Event Category: (4)

    Event ID: 18456

    Date:  10/25/2006

    Time:  11:54:42 AM

    User:  NT AUTHORITY\SYSTEM

    Computer: MSDB

    Description:

    Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]

    The error log from SQL gives more info:

    Error: 18456, Severity: 14, State: 16.

    What is causing this? How can this be traced?

    I am stuck here. All help greatly appreciated.

    Moshe

  • Moshe,

     One of the improvements in SQL 2005 is that all logins are always encrypted using SSL. If you have frequent connection logins, you will see lsass being quit *active*. Is it your case?

    For the error in the ERRORLOG, the STATE tell that the process doesn't have permission of the login database. It apears to use NT authentication, running as localsystem. You can find who is trying to login from your local machine and go from there...

  • I keep on getting this error intermittently and the user specified in the error log has got the correct permission.

    2006-10-31 08:58:36.01 Logon       Error: 18456, Severity: 14, State: 16.

    2006-10-31 08:58:36.01 Logon       Login failed for user 'AbsLayer'. [CLIENT: <local machine>]

    2006-10-31 08:58:37.28 spid53      Starting up database 'AbsLayerRepository'.

    I have added retry logic in my code which keeps on retrying the connection for the configurable amount of time, when I get this error I can see in my application log that its retrying the connection and most of the time it passes after 2 - 3 attempts but some time it goes up to 9-10 attempts so I feel it to be unreliable in the live system. Can some one please help me why this error occurs or is there any patch to resolve this issue.

  • I have found an Error:

    Server Name: UMAIR130786\SQLEXPRESS

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    It works well using Windows Authentication

    but with SQL Server Authentication it results in the above mentioned error.

  • I was getting the same error, and I try everything listed on the Forum, and could not get rid of the error. It looks like there is some funkiness on the ODBC Connection, and it will always use the current user logged on the computer, not the specified SQLSever account, to send over the network for the connection profile.

    To eliminate the error, I had to grant the Domain Users access to the SQL Database the program was trying to connect to. In my case, all users had access to the database, but security settings can be put in place to limit the users’ access.

    In other words, I could not fixed the problem, so I had to make a workaround.

  • Like every one else, I was frustrated by the strange 18456 error with State=8. I store my password in a textfile, and when I need it, I do a copy and paste into the password field. I use a password something like "Ity@1%6$9#g".

    I tried a number of "strangies", but this one worked on more than one occasion: using the direction arrow key pointing left, I moved the cursor back to the beginning of the password. It worked!

    Try this at home, folks, it does not hurt a bit, and perhaps it might give the Microsoft boys a hint about the nature of the problem if you report the results.

  • Il-Sung, thanks so much for writing this up -- definitely the most helpful source of debugging info for login failures to SQL!

  • What is State 16?  I am getting Error: 18456, Severity: 14, State: 16

  • Hi Shawn,

    State 16 indicates that target database could not be determined.  This may mean that the specified database is non-existent, you do not have permissions on the database, or the database may not be online.  Also, you can do as Matt mentioned in his comments on March 14 -- try connecting to a different database and then use the USE DATABASE to the problematic database and see what the error is.

    Il-Sung.

  • I have an SQL Server 2005 cluster. Windows Authentication works fine on the primary node but after failing over onto the secondary node I am getting the 18456 error State 11.  

    I noticed someone else posted concerning this error state but I do not see a response to this issue.

  • I am getting the state 16 error, but it is not recurring.  However there is a separate partition on the server that holds the logfiles which I noticed have not changed since the last time I received the state 16 error.  Also the partition is almost to capacity.  Are these two problems related or do the logfiles not overwrite themselves?  I will go ahead and apologize for dumb questions.  I have no experience in SQL and my job requires me to administer an enterprise GIS database!

  • Hi ,

    When I try to start the merge agents in SQL server 2000 I get the error message as below:

    ________________________________________

    The process could not connect to Subscriber 'ATELBSNT65'.

    (Source: ATELBSNT65 (Agent); Error number: 20084)

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

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    (Source: ATELBSNT65 (Data source); Error number: 18456)

    _______________________________________

    There are 3 servers ATELBSNT65,66,67.

    When I try to access merge agents through ATELBSNT67 this error occurs.

    ATELBSNT67 is the publisher and ATELBSNT65,66 are the two subscribers.

    So please help.

Page 3 of 27 (396 items) 12345»