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 2 and 7 and type the answer here:
  • Post
  • What is the corresponding error in the server's error log?

    Thanks,

    Il-Sung.

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

    I'm getting this error trying to connect a service to the database and the user I've verified has access to the database that it's trying to connect to.  Is there something I need to do differently for a service?  The service is related to MOSS 2007.  It's the Microsoft Single Signon Service.  Any suggestions?

    Thanks

    Robert

  • I am getting this same error as Error number 18452 Severity 14 State 1 with SQL2005, Windows Authentication ONLY.

    My user is permitted on the server, I can connect with Management Studio.

    No space in ServerName, Windows firewall disabled.

    The calling program is Delphi5

  •  I'm seeing error state 23 repeatedly in my logs:

    “Error: 18456, Severity: 14, State: 23.”

     Despite some scouring of the Web and MSDN I could find no information about this one.  Please  post the answer if possible.

  • State 23 is pretty rare.  What this means is the server was in the process of shutting down and at the same time some user was trying to log in.  SQL blocks new users from logging in when the server is shutting down.  So if you have fairly consistent logins I would expect to see this one from time to time when the server is shut down, it's harmless.

  • Hi Il-Sung,

    I'm receiving the Error 18456 in my Microsoft SQL Server  2000 - 8.00.818 Standard Edition but when looking in the ERRORLOG file I cannot see any state not severity number, Do I need to configure anything in order to obtain those?

    Regards

    Jesus

  • Hi Jesus,

    Unfortunately, the correct error state is not reported by the SQL Server 2000 server.  There is no configuration that can change this.

    Il-Sung.

  • Hi,

    My email is sergei@ssw.com.au.

    I have got SQL Server 2005 SP2 on WinXp SP2.

    I tried sqlcmd -S machine_name -U machine_name\user_name -P user_password.

    It failed with error: "Login failed for user machine_name\user_name"

    I can open SSMS using run as machine_name\user_name,connect using windows authentication (in this case machine_name\user_name) and it works fine.

    My question is why this command-line does not work

    sqlcmd -S machine_name -U machine_name\user_name -P user_password?

    Sergei.

  • The username and password you pass in as -U and -P are SQL Server usernames and passwords, not Windows usernames and passwords.

    So you cannot say:

    sqlcmd -S machine_name -U machine_name\user_name -P user_password

    If you want to log in as a specific Windows user, then you need to shell a command prompt as that user and use:

    sqlcmd -S machine_name -E

    You can shell a cmd prompt by using the RunAs command, like so:

    C:\>runas /user:machine1\user1 cmd.exe

    Enter the password for machine1\user1:

    Attempting to start cmd.exe as user "machine1\user1" ...

    This will open a new cmd window and from there you can run sqlcmd -S server -E

    Matt

  • Hi, Sergei

      The command line parameter " -U -P" is expected to use SQL Authentication which requires you log on as a SQL user. The SQL User can be a map of your windows account or non-windows account. In your case, you were using a machine account to access the DB. So, you can not use "-U -P", instead, do run as this machine account and execute "sqlcmd -S machine_name -E".

    Or you can create a SQL login by

    create login [login_name] with Password='...'

    Then use sqlcmd -S machine_name -U [login_name] -P <pwd> to log on.

    Good Luck!

    Ming.

  • Hi everybody, ige this error when i try to connect to sql server from studio express

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    i can connect from visual studio and other softwares and from studio express on other machines. Does anyone know what the problem could be?

    Fabrizio

  • Hi Fabrizio,

    Have you looked at the server's error log and determined the error state reported by the server as outlined above?

    Il-Sung.

  • Hi All

    I am having a similar problem where the state is 16. What is strange is that it occurs in the middle of a job and at intermittent intervals. The job would one day execute perfectly fine and the next day fail with error 18456 state 16. If you re-execute the job from the particular step that failed as the user that runns the job the job would succeed. The user that executes the job engine is a server admin and also starts all related SQL services. (SQL, SSAS, SSIS, etc)

    Any advice?

  • Hi, useful information, please add this to Books Online

    Thx

  • This article:

    http://groups.google.com/group/microsoft.public.inetserver.iis.security/browse_thread/thread/68c216b10e7fa70/69aacf4a582ec20c%2369aacf4a582ec20c

    They found a fix to the barrage of event log entries.  Basically there is a setting in SQL Enterprise Manager's Tools > Options menu to regularly "Poll" the server to find out its state.  Regardless of what auth method was used to register the server, it uses the client machine's user credentials to try to determine the state of the SQL Server's services.  Unchecking the box will stop the error messages.

    Still don't know WHY this happens, though - anyone?

    Post in reply to:

    Hi,

    I have SQL 2000 SP 4 running with both SQL & Windows Auth. selected.

    The moment I open Enterprise Manager from a computer that is not on the domain I get the following errors in the NT eventlog although I am using SQL Auth in Ent. Manager:

    Logon attempt by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0

    Logon account: [Remote NT User ID]

    Source Workstation: [Remote Workstation Name]

    Error Code: 0xC0000064

    Logon Failure:

    Reason: Unknown user name or bad password

    User Name: [Remote NT User ID]

    Domain: [Remote Workstation Name]

    Logon Type: 3

    Logon Process: NtLmSsp

    Authentication Package: NTLM

    Workstation Name: [Remote Workstation Name]

    Caller User Name: -

    Caller Domain: -

    Caller Logon ID: -

    Caller Process ID: -

    Transited Services: -

    Source Network Address: 10.2.5.16

    Source Port: 0

    I was under the impression that the whole point of SQL Auth. is not to try and Aut. the local logged on user?

    Any pointers would be appreciated.

    Adam

Page 5 of 27 (399 items) «34567»