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 7 and type the answer here:
  • Post
  • Hello if u face Microsoft SQL server, Error:18456

    Then open management studio as administrator.

    I hope that this works as it did for me.

  • The post is really helping me in undertstanding the error happening in our Production SQL Server 2005.

    Below are the Error Message Details :

    Source        : Logon

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

    Date            : 6/14/2010 8:03:19 AM

    Log              : SQL Server (Current - 6/16/2010 2:02:00 AM)

    Source         : Logon

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

    I am getting this error every minute logged in the SQL Server Error Log.

    One question : You have mentioned we may need assistance from CSS. I am not familiar with CSS before. May I know something about CSS?

  • I have the same error with state 16 and the user NT Authority\System on the local SQL server.

    The database where user tries to log on is marked as suspect by some restore.

    - What is the error, what is the consequenz? suspect db causes login error or login error causes suspect db??

    - What can I do to get things straight again?

  • I have the same error with state 16 and the user NT Authority\System on the local SQL server.

    The database where user tries to log on is marked as suspect by some restore.

    - What is the error, what is the consequenz? suspect db causes login error or login error causes suspect db??

    - What can I do to get things straight again?

  • I am getting

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    could someone help me how to

    deal with this problem?

    I am running SQL Server 2008

    I can log on using windows authentication but

    having this problem when using sql server authentication

    sa account

    thanks for the response!!!

  • I am trying to sign up for www.Caring4Cancer.com--i keep getting login failed

  • I met this error:Error:

    18456, Severity: 14, State: 16.

    Login failed for user 'NT AUTHORITY\SYSTEM'.

    At last i found that the reason for this error When a replication-job run, after carefully checked,i found that i dropped publication and subscriber database , and i forgot to delete theses replication jobs.

    So when theses jobs run, SQL can't log NT AUTHORITY\SYSTEM in to my dropped  publication and subscriber database

  • Im having this problem with sql server 2005 in the first example of c#:

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

    asifsab@yahoo.com

  • A little trick I found after I was getting Login errors using management studio on the localhosst (win2008R2)

    You need to "run as adminstrator"

  • What about the same error but for:

    - windows authentication

    - user which has sysadmin role

    - connection is local

    - state is 1 ?

  • the error state...  INvalid attempt to read when no data is found... how could I be able to resolve it???????

  • I really need your response... thank you... email me at gladztinosa@yahoo.com

  • Hi,

    Just change the server authentication mode to "SQL Server and Windows Authentication mode" in Server Properties -> Security Tab.

    Sure It will be working fine.

    Thnx

  • Tank you.  You have help yet another.

  • Thank you.  You have help yet another.

Page 22 of 27 (396 items) «2021222324»