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 8 and 1 and type the answer here:
  • Post
  • To check please open SQL Enterprise Manager and look under Properties-->Compatibility-->Privilege Level check the Run this program as an administrator

  • Suddenly started receiving a log full of login failed messages with Error: 18456, Severity: 14, State: 16.

    This happens for any login where the default db is not explicitly stated. Explicitly stating the default db in the connection works fine, but should not be required in a correctly working server (so long as the default db is correctly set).

    The users are using domain login AND access is granted by an AD group. The group has the default database attribute set, the default database is online, and the users have access to no other databases by membership in any other group.

    I've also tried to rule out schemas without owners and SQL Agent jobs referencing db's that don't exist. I remain baffled.

    Any ideas what I'm missing or how to troubleshoot this?

    Cheers,

    Eric

  • Hi dear,

    I am getting error 18456 after trying to log in,using windows authentication.What I have is sql server 2005, 90.It is installed on a window vista and I have been using it for ages.

    Any ideal what I can do to get connected?

    Thanks in advance!

    sam

  • Hi!

    As said before,the error I get is 18456.There was no state ,line or severity with the message.I hope this info will help.

    Thanks,

    sam

  • installed a copy of SQL Server 2005 on my PC. Now when I start it I select my own PC as the server name and the Authentication is set to "Windows Authentication". My username is already written and greyed out and the password box is greyed out. However when I click connect it fails with error 18456.  I get error state 11:Valid login but server access failure.

  • You just need to start the sql with admin previlage or run as administrator, hope should work.  

    Regards,

  • There could be much more resions, as the original says.

    1) Check the Server Property for the security configuration, If it is windows, bydefault SQL SA account should be disables.

    2) Try to enable SA account

    3) Match the password.

    4)Make sure other login user the Secure (Connect to SQL Server)

    5) and much more since the more error you get you have more solution to resolve that... See the BOL and other SQL forums..

    regards,

    Manoj

  • Hi,

    I'm getting Error:18456, Severity:14, State:1.

    Could any one help me on this issue.

    Thank you in advance,

    Jyothi

  • Hi,

    I'm getting Error:18456, Severity:14, State:1.

    Could any one help me on this issue.

    Thank you in advance,

    Jyothi

  • Hi.. i am using SQL Server 2008. 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

  • hi

    my error state is 1

    what can ido?

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

  • Who ever is facing any problem in sql while login in sql authentication mode, just try this trick,

    Step 1: Login in to windows authentication

    Step 2: Run following script:

    -- ***********************************

    -- SQL Query to Alter the [sa] login and reEnable it

    USE [tempdb]

    ALTER LOGIN [sa] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    ALTER LOGIN [sa] ENABLE

    -- ***********************************

    now close your SSMS and restart it and try to login again by userID 'sa' and pwd '123'...

    Enjoy...

    @BALA

  • Logon,Error: 18456, Severity: 14, State: 58.

    Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

    State 58 means An attempt to login using SQL authentication , where Server is configured for Windows authentication only.

  • Same problem here. Installed sqlserver locally to do some development testing. Wondering best way to setup permissions to get on the db.

  • What if I have a state 11 error on a LAN?

Page 21 of 27 (398 items) «1920212223»