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 2 and type the answer here:
  • Post
  • I get this in the logs:

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

    Did a scan through your blog page (very helpful, btw), but didn't see any recommendations for State = 11.

    Any suggestions?  

    Thx.

  • States 11 and 12 simply mean the Windows user coming in does not have login access to the server.  SQL Server validated the Windows user's token, figured out who it is, but the Windows user has not been granted access to the server.

  • Today it is all about security relating to SQL Server. The first session is being taught by a Software

  • For State 11 - the login ID did not have a profile...I logged in to create the profile and all is right with the world!

  • Hi.  I renamed my SQL Server 2005 machine (within the same domain), and any jobs (e.g. backups) I try to run from Mgt. Studio are now getting:

    05/08/2007 03:28:04,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 10.70.40.55]

    05/08/2007 03:28:04,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.

    I have done the sp_dropserver/sp_addserver (w/ local) dance.  This is a standalone server with no links.  Service accounts are all using SYSTEM.

    Any ideas what I can do to repair this?

  • I m also facing the same problem while connecting the server in single user mode..

    Error: 18461, Severity: 14, State: 1.

    Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

  • Hi,

    I tried to log in Database engine but it doesn't allow me to.  It keeps giving me this message:

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

    I have no idea how to fix this problem. Could you please help me out? Thanks.

  • Hi.  I have a problem with my connection. I checked the error log, it shows:

    2007-05-19 22:19:27.47 Logon       Error: 18456, Severity: 14, State: 11.

    2007-05-19 22:19:27.47 Logon       Login failed for user 'SQLServer\Andy'. [CLIENT: <local machine>]

    Please help to solve this problem. Thanks so much.

  • Hi,Satish

      Can you be more specific how you create the login and how you make connection by using which authentication, basically, the full connection string.

    Thanks!

    Ming.

  • The stiuation is:

    Database server : SQl Server 2005

    databse mode: Single user mode

    user (used to connect to database server):

    sa (for sql authentication), local admin level user for windows authentication

    Error: Error: 18461, Severity: 14, State: 1.

    Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

    utilities tried to connect to database server: MS SQL SERVER managment studio and sqlcmd (the command line utility)

    please suggest on the same. if i m missing any thing or for any other detail feel free to contact...

  • See this link if your error is related to:

    Re: The AcquireConnection method call failed with error code 0xC0202009.

    http://forums.microsoft.com/msdn/showpost.aspx?postid=160340&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=2

    (Very Good)

    Read the whole blog. The 'post 20' blog from akeiii solved my problem with running 32-bit apps on 64-bit SQL and for connection issues to MS Access 2003 databases (must run in 32-bit mode).  Besure to look at changing Step package type to "Operating System (Cmdexe)" and entering a command string line like:  "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "D:\Projects\Fremont Dialer SSIS\DMFDailyDataFeed\DMF_Daily_Data_Feed\DMFDailyDataFeed.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E.

    Also look at using the dtexecui.exe to help create the command string that appears after the DTExec.exe program above.

    The DTExec.exe in the "program files (x86)" directory.

    If you need more clarification or help email me on akmsweb@gmail.com

     Was this post helpful ?

  • Ok,

    I just installed Sql Server Deveplopment Edition along with SP2 on Vista. The login method chosen at the time of installation was Windows Authentication.

    Installation was successful but I can not connect to Database engine and keep getting "Login failed for user" with State being 11 (sql error log). The sql server related services are running under LocalSystem account.

    Any ideas?

  • Figured out :)

    The Sql Server 2005 needed to be run as Administrator. Dang it - Vista !!

  • Error 18456

    Severity 14

    State 16

    SQL 2005 SP2

    Mixed Mode

    Failed login is a domain account but the error message says the client is <local machine>

    Also, the failed login successfully logs in many times during the day.

    There are no error messages in the SQL Agent log, just the SQL Server logs. Profiler didn't pick up any unsuccessful logins(despite the login failure showing up every minute on the SQL Server logs). I've confirmed that the same user account successfully  logins to all of the databases that it's mapped to. No user complaints, just tons of failed login attempts in the SQL logs.  

    I'm stumped.

  • Wow, I can't believe I resolved this issue by luck!

    I encountered this error on my local machine.   So what I did was ...

    1.  Logon to SQL Server using windows authentication.

    2.  Right click in the query window that appears and select "Open Server in Object Explorer"

    3.  Go to object explorer and open the "Security" folder and then the "Logins" folder.

    4.  Double-click the "sa" user and change the password.   Also, like another user mentioned above, untick the "Enforce Password Policy" in addition to resetting the password.

    Now if you will all excuse me,   I must go and play with myself.

Page 6 of 27 (396 items) «45678»