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 3 and 5 and type the answer here:
  • Post
  • I have sharepoint 2007 .I am site collection administrator but I loose full control permission (now my permission is like disigner permission) I suspect my db sql 2005 express size I shtink wss_content  the size is now 2 .39 GB  but didn't work .when I show the  error log I found

    error:18456, security:14, state:16

    login faild for user 'NT AUTHORITY\NETWORK SEVICE'.[client :IP@]

    can someone help me

  • I am getting an error 18456,severity 14 and state 1. What I 've to do know

  • Hi Friends,

    Please help me with this error

    I have been getting this error:

    SQL Server Assertion: File: , line=440 Failed Assertion = '!PSess ()->FHasWorkspaceRef ()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

    I figured out that this error is due to a bug in sql server 2005 and it would be solved by installing SQL Server 2005 service pack 2, and I did install it but still I get these errors again and again..

    Pleasssse Help. Thankss..

  • I have got the following error:

    login failed for user 'sa'

    category: 4

    event id: 18456

    Can anyone came across this issue.......

  • HI,

    I am using sql server 2005.i denied the server\administrator to connect to database engine.Now the problem is that i am not able to connect to the database engine in windows authentication mode.

    plz tell me how to grant permission to admin.

  • Hi,

         I have a user that uses dynamics and can connect and will start working but then something will hang or report will not run. and i find 18456 severity 14 state 8 messages in in log on server. which points to a password mismatch , but if the password was incorrect user will not be able to login on dynamics/sql..

    any ideas?  I am think about reloading users SQL client to see if these clears up the errors.

  • I am getting the below error on sql 2008.  can some one help me with this. The domain account is a sysadmin on the server. This msg pops up every 1 min in the errorlog

    Logon        Error: 18456, Severity: 14, State: 38.

    Logon        Login failed for user 'domain\SPS2007'. Reason: Failed to open the explicitly specified database.

  • For those struggling with State 1.  State 1 means you dont have permissions to even get an error message.  In my case it was because the server was set to Windows Authentication mode only (not mxied mode or Windows & Sql mode).  Once I changed the server mode and restarted it worked fine.

  • hi all,

    me getting error 18456 state:1 and severity:14........what should i do???

    plz can anybody mail the solution at harneetsingh3@gmail.com

  • Any comments on why we're seeing the following in the ERRORLOG:

    04/13/2011 12:13:48,Logon,Unknown,Login failed for user 'DOMAIN\NODE1$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 172.21.0.228]

    04/13/2011 12:13:48,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.

    So it's on a newly built SQL Server 2008R2 Cluster with SPN configured.

  • Hi,

    i have Login failed for user "x" Error 18456 as error messages in SQL Server 2008.

    Plz what to do?

  • Hi,

    i have Login failed for user "x" Error 18456 as error messages in SQL Server 2008.

    Plz what to do?

  • I had state "1" after renaming a database!!

  • I had the same problem with state 38 ,

    I had an sql server pc that was running within a domain. (i wanted my app to connect to this instance)

    I resolved it by creating a login for each user via the sql server  Logins , also a user for the database with the domainname\user  

    (I was using windows auth  for my server and local instance false as I didnt want a local copy created for my users, the problem was that although my user could access the sql server via windows auth- but tconnecting to the database, I would still need a user and login for each person accessing my database.

    (also obviously make sure you have added the firewall ports for sql browser and sql server!)

  • I am getting this error in log

    Error : 18456, Severity : 14, State : 11

    Login failed for user 'NT AUTHORITY\NETWORK SERVICE'

    this error is generating in the front end .net application

    We ar using the use Integrated Security=SSPI with an IIS/ASP

    application.The DB is assigned to  NT AUTHORITY\SYSTEM  login

    Please help

    with regards

    Dilip D

Page 23 of 27 (396 items) «2122232425»