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 3 and type the answer here:
  • Post
  • i like getting my ass licked by dirty whores.

  • I am getting this erro when trying to connect to a sql exoress 2005 db throught vb.net on one of my pc's but not the other.

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

    Assuming it is a permission problem, I created another DB on the PC that I cannot connect with & still cannot connect through the program. ANy suggestions would be appreciated. (I can be reached at gtinkel@profsft.com.

    Thanks

  • Hi,

    In one of our test environments we can connect locally through ODBC, but cannot connect from a vmware image or any remote box. This is a mixed mode SQL Server 2005 and we have a sqlserver user which we are using to connect (it is dbowner of all database

    We have set Show_errors in the main db service to 1 and restarted, but we do not see the state (another machine on the network does on their own copy of SQL2005SP1). We also have the setting "Audit login errors only", but nothing gets added to the ERRORLOG.

    How do we set this debug?

  • I had an "Error: 18456, Severity: 14, State: 11." in the log, meaning SQL was authenticating ok, but Windows was not.  I am running Windows Vista.  It turned out I needed to right-click on my app and run as Administrator.  It now works fine.

  • Hi,

    We use Sql Server 2000 SP4 on Windows 2000 SP4. The audit level is set to login failure for this server but we don't get any state informpation in the log file. here is an extract of the log file :

    2007-06-19 15:45:02.45 logon     Échec de la connexion de l'utilisateur 'sa'.

    2007-06-19 15:45:22.17 logon     Échec de la connexion de l'utilisateur 'sa'.

    2007-06-19 15:45:35.07 logon     Échec de la connexion de l'utilisateur 'sa'.

    2007-06-19 15:46:21.01 logon     Échec de la connexion de l'utilisateur 'sa'.

    Do you have an idea why I don't see any "state information" in the log file. I need this information to understand why I get this connection error.

    By the way, the connection is OK 99% of the time and Sql Server is used by an ASP web application.

    Best regards,

    Olivier

  • I am getting this erro when trying to run jobs in SQL Agent. I have confirmed the login account used to start Agent is valid. The account also has sysadmin privs....Stumped...

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

  • Like every one else, I was frustrated by the strange 18456 error with State=8. I store my password in a textfile, and when I need it, I do a copy and paste into the password field. I use a password something like "Ity@1%6$9#g".

    I tried a number of "strangies", but this one worked on more than one occasion: using the direction arrow key pointing left, I moved the cursor back to the beginning of the password. It worked!

    Try this at home, folks, it does not hurt a bit, and perhaps it might give the Microsoft boys a hint about the nature of the problem if you report the results.

    thanks bertie,this worked for me

  • I discovered a few stepstones when connecting via JDBC to a locally installed SQLSever Express 2005 database. Here are my observations:Activate &quot;Accepting TCP/IP&quot; connections in the SQL Server Configuration Manager. Otherwise the SQLServer Expr

  • Ralle,

    Yes, you are correct.  The Microsoft SQL Server 2005 JDBC driver requires SQL Server (any SKU) to have TCP/IP support enabled.

  • I am also getting Error: 18456, Severity: 14, State: 8 when my .NET 1.1 app attempts to log into the 2005 Server from a remote machine.  The app works against a 2000 Server. The password is correct, as the same login and password can be used to log into the 2005 server from the same remote machine with SSMS. The State: 8 is either bogus or too generic to be useful.  Any help?

  • exec sp_password @new = 'sqlpassword', @loginame = 'sa'

    alter login sa

    with password = 'sqlpassword' unlock,

    check_policy = off,

    check_expiration = off

  • Jaap@myprovider.com-

    THANKS! Don't know why that worked, but it did, and I thank you.

  • In Windows Vista: All Programs -> Microsoft SQL Server 2005 -> (Right Click) SQL Server Management Studio -> Run as administrator

  • Hi, Hermann

       What operation you did after you click SSMS? It seems you need to first type the target server name and credential to login then connect.

      Can you provide the error message say " 18456  Level ?? State ??" to help us identify the problem.

    Thanks!

    Ming.

  • Hi, Hermann

       Please check out the following blog:  http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx

    or install Yukon SP2, find out the article in Books online "How to: Connect to SQL Server from Windows Vista" for the issue understanding and resolution.

    Good Luck!

    Ming.

Page 7 of 27 (401 items) «56789»