Understanding "login failed" (Error 18456) error messages in SQL Server 2005

MSDN Blogs > SQL Protocols > Understanding "login failed" (Error 18456) error messages in SQL Server 2005
Server & Tools Blogs > Data Platform Blogs > SQL Server Protocols Blog
Live Now on Server & Tools Blogs
Menu
Recent Posts
Tags
More ▼
Archives
Archives
More ▼

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Rate This
21 Feb 2006 11:47 AM

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 8 and 7 and type the answer here:
  • Post
Comments
  • Alan
    12 Mar 2006 11:14 PM
    Im developing the 5 stars programme in .NET
    Im having this problem with sql server 2005 in the first example of c#:

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

    What should I do?

    My email: alankind@hotmail.com

    Thanks!
  • Ignacio Abel
    13 Mar 2006 7:25 PM
    I have exactly the same problem using SQL Server 2005.

    Cheers,

    Ignacio Abel.
  • Matt Neerincx [MSFT]
    14 Mar 2006 2:24 AM
    State=16 means that the incoming user does not have permissions to log into the target database.  So for example say you create a user FOO and set FOO's default database to master, but FOO does not have permissions to log into master.

    This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).

    So to check on this theory, try logging the user into some other database and then try using the USE DATABASE command to switch to the target database, you will get a better error message as to the root cause of the failure.
  • EH
    14 Mar 2006 7:45 AM
    Hello,
    when connecting from ODBC with SQL security, all connections are failing.

    Error is 18456, Severity: 14, State: 8.

    Creating new logins, and triple-checking the passwords did not help...

    Thank you
    EH

  • Tom
    15 Mar 2006 8:20 PM
    I am having the same error.  The database engine will not allow any logons.  The other engines seem to be fine.  This still means that I do not have permissions for any of the databases.  This is using local or domain accounts.  

    I created this problem by detaching one database running on the server.  I went to connect the datafiles and I cannot get into the server at all.
  • Matt Neerincx [MSFT]
    15 Mar 2006 9:37 PM
    When you say "Same Error" I am assuming you mean:

     Error is 18456, Severity: 14, State: 8.

    This error is pretty clear, means that the password supplied does not match the password given.  Note that passwords in SQL 2005 are case-sensitive, this could be an issue.

    If you are a local administrator to the computer, then you should always be able to log into SQL.

    Also, try using the admin port if the normal ports are not working, for example, to connect to local default instance:

     sqlcmd -E -Sadmin:.

    To connect to local named instance:

     sqlcmd -E -Sadmin:.\Instance1
  • Tom
    17 Mar 2006 11:17 AM
    Thanks so much for your help.

    I was able to use SQLCMD to gain access and rebuild access for my database
    admin account.  I was then able to use this account to reattach the detached
    database.  When I did this the database and all user access to SQL2005 was
    back to normal.  I am not sure why this happened.  We have about ten other
    databases on this SQL server.  The same users had access to these tables as
    well as the detached table so the detached table was not the only table for
    which they had access.  The detached table was just a table of 'production'
    data.  

    I will try and see if I can recreate the problem but will wait for
    the weekend to try that!
  • gautam
    25 Mar 2006 4:08 AM
    soloution
  • Rolf
    10 Apr 2006 3:51 PM
    Re: state 16 - if there is no other database to log into, how do we determine what the problem is?  Users have installed SQL Server Express 2005 using Windows Authentication, where user id and password should not be an issue, right?
  • rm
    20 Apr 2006 4:43 PM
    hi there

    i'm getting a 'State: 1' (yes, in the server's event log). what could this mean?

    thanks in advance!
  • 20 Apr 2006 5:54 PM
    Regarding 'State: 1', are you running SQL Server 2005 or SQL Server 2000?  Supportability improvements were made to 2005 to make the states more unique but 2000 still reports 'State: 1' in every case.

    - Vaughn
  • rm
    20 Apr 2006 8:46 PM
    we're running SQL Server 2005 CTP. server authentication is set to "SQL Server and Windows Authentication mode". when connecting using windows accounts everything is fine, but any attempt to connect as e.g. 'sa' fails with this 'State: 1' error message... greets, rm
  • 21 Apr 2006 12:32 PM
    Another question, are you looking at the error message state that the client receives or the error message state in the server error log?  By default the server error log is at "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG".  Also, you mentioned you're running at SQL2K5 CTP rather than RTM.  Which CTP are you using?  If you're using an old CTP it may be the case that unique state improvement hadn't yet been made.
  • shi
    23 May 2006 4:53 AM
    I'm getting Error: 18456, Severity: 14, State: 27
    Could you help me on state 27, please.
    I'm running SQL 2005 version 9.00.1399.06
    Thx
  • Il-Sung Lee [MSFT]
    23 May 2006 2:03 PM
    Hi Shi,

    Error state 27 signifies the server could not determine the initial database for the session.  Did you specify a database name in your connection string?  Are you sure that the name was spelled correctly?

    Il-Sung.
Page 1 of 26 (382 items) 12345»