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 8 and type the answer here:
  • Post
  • Using Sql Studio Express to connect to a Sql Server 2005, I'm getting Error: 1846, State: 8 -- which should be password mismatch.  Yet, when I use the same user name and password from a jdbc based client like Squirrel, it works fine.  I have another machine where I have the login details saved where things work fine -- I am loath to try retyping the password there because if it doesn't work, then I can no longer support this system.  Anyway, why might I be getting a password mismatch error when the password is correct?

  • J'ai un problème avec le LAN  du programme docteur connectivité . J' aimerais résoudre ce problème. Pouriez -vous m'indiquer toutes les étapes qui me permettront de résoudre le problème? Je vous remercie!!!

  • erver Authentication. The 'sa' login details are correct but still getting the following error message:

    Quote:

    Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

    In order to resolve the issue, please refer to the following steps:

    1. Login to the MSSQL Server Management Studio with Windows Authentication.

    2. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

    3. Under the Server Properties, select a page of "Security".

    4. Select the Server authentication as "SQL Server and Windows Authentication mode" and click Ok.

    5. Restart the SQL Services and then try to login with 'sa' details.

  • Login failed for user 'sa'. Reason: Password did not match that for the login provided I installed happysql.com software to block hackers attack for brute force password. It works great. I tried to find solution but this one is only helped me to save my sql.

  • The most common I encounter in my case is sql authentication not enabled. This helped www.sql-server-business-intelligence.com/.../microsoft-sql-server-error-18456-login-failed-for-user

  • Sounds like your database server isn't configured for both NT and SQL authentication. or you can use sa user that have access over target database.

  • The following link has details of this error sqlserverlearner.com/.../microsoft-sql-server-error-18456

  • i have error  18456 in windows 7.please give me solution

  • Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536         Login failed for user  

    plz  give me solution as soon as possible

  • ALTER LOGIN sa ENABLE ;

       GO

       ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;

       GO

  • stackoverflow.com/.../how-to-change-from-sql-server-windows-mode-to-mixed-modesql-server-2008

  • I have one more reference for this <a href="www.sql-server-business-intelligence.com/.../microsoft-sql-server-error-18456-login-failed-for-user">sql error 18456</a>, check this out !!!

  • Hi Sam,

    I go through the following link: www.sql-server-business-intelligence.com/.../microsoft-sql-server-error-18456-login-failed-for-user

    as you provided in the conversation, it its going to help.

    Thanks

  • Hi!

    I have the vry same problem with status 16 and it appears that my DB are in recovery every 5 seconds. Why is that?

    While the DB are in recovery mode they won't accept logins.

    What to do?

    MRamirez

  • hi

    this is an issue if you are running window 7

    please right click on the icon of sql server management studio and then click on run as administrator

    after that login menu appear

    just choose ->

    Server Type : Database Engine

    Server Name:   ( . )      // don't put any brackets only type single dot here (if you didn't given any user name for more information or for sql server installation just see this video tutorial)

    Authentication : Windows Authentication

    it could be possible you gave an instance name if you remember than put it as

    computername/instance

    type your computer name and after slash type the instance if you remember

    watch this tutorial complete guide to run sql for begginers

    www.youtube.com/watch

    this is part 1

    www.youtube.com/watch

    and this is part 2

    it will really work

Page 24 of 27 (396 items) «2223242526»