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 7 and 5 and type the answer here:
  • Post
  • experienced this on a 6 node cluster - each of the nodes was trying to log into the others - it was just tons of chatter between the nodes, all returning the following msg:

    Message

    Login failed for user '<DomainName\ClusterNode$'. [CLIENT: xx.xx.xx.xx]

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

    I created a login for each node on all the others - no perms other than public to master.  Errors had been going on at least every minute for some time.  

    Immediately after the creation of the logins, the errors stopped.

    cool.

  • You have follow these steps :

    go to Management studio/enterprise manager=>rightclick on it go to Properties =>

    go to security => Server authentication=> select SQL Server  and windows authentication mode.

    That it.

    hope this will work for U.

    cheers

  • I keep getting the following event log error on my SQL2005 server:

    Event Type: Failure Audit

    Event Source: MSSQLSERVER

    Event Category: (4)

    Event ID: 18456

    Date: 11/17/2009

    Time: 7:50:00 AM

    User: N/A

    Computer: SQ02

    Description:

    Login failed for user 'domain\intra_svc'.

    the SQL logs have these two errors repeated over and over, which correspond to the eventlog error:

    Date 11/17/2009 8:00:00 AM

    Log SQL Server (Current - 11/17/2009 8:00:00 AM)

    Source Logon

    Message

    Login failed for user 'domain\intra_svc'. [CLIENT: 10.1.30.33]

    Date 11/17/2009 8:00:00 AM

    Log SQL Server (Current - 11/17/2009 8:00:00 AM)

    Source Logon

    Message

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

    these errors happen ever 10 minutes and correspond to the errors on our sharepoint server:

    Event Type: Error

    Event Source: Windows SharePoint Services 3

    Event Category: Timer

    Event ID: 6398

    Date: 11/17/2009

    Time: 8:30:00 AM

    User: N/A

    Computer: WSS01

    Description:

    The Execute method of job definition Microsoft.SharePoint.Search.Administration.SPSearchJobDefinition (ID 070e9fbc-b7f1-4132-99e8-f8775f31027c) threw an exception. More information is included below.

    Login failed for user 'domain\intra_svc'.

    Event Type: Error

    Event Source: Windows SharePoint Services 3

    Event Category: Database

    Event ID: 3351

    Date: 11/17/2009

    Time: 8:30:00 AM

    User: N/A

    Computer: WSS01

    Description:

    SQL database login failed. Additional error information from SQL Server is included below.

    Login failed for user 'domain\intra_svc'.

    I know it has something to do with SQL/Windows Authentication, but not really sure where.  hopefully someone can help.

  • This error can be also solved with:

    - go to sql server

    - right click on server, choose properties

    - click on security

    - on server authentication, enable SQL Server authentication

    also enable and configure password sa user for node security on serve tree.

  • I get Severity: 14, State: 8 with a sprinkling of State: 5's against SQL2005 in a single domain. There are days where I don't get them at all but also days of plenty. I can see ONE error resulting from a mistyped password, but why do they keep going?

    Looking over some recent comments, I already have NamedPipes, TCP, and Shared Memory enabled for SQL, as well as SQL Server authentication.

    If "also enable and configure password sa user for node security on serve tree" means that the sa account should be enabled, ours isn't, but the errors never mention the sa account just a couple other accounts that are enabled and used.

  • Finally figured out that it was the DSN file we use for linking Access to SQL tables. You want to use the Windows authorization flavor, so "Trusted_Connection=Yes" should be in it along with a UID pointing to a valid username, which will happen automatically when you go into Access and recreate the DSN choosing Windows authorization this time. Yes, you'll have to relink all your tables with this new file, but it's a small price to pay. Otherwise a plague of State 5's and 8's may be upon you when people do queries.

  • This error can be also solved with:

    - go to sql server

    - right click on server, choose properties

    - click on security

    - on server authentication, enable SQL Server authentication

    also enable and configure password sa user for node security on serve tree.

    Goto SQL Server as in?

  • I was getting error state 11 for a windows login. I looked at the sql server permissions and noticed that the Effective Permissions were missing ‘CONNECT SQL’ It was because the individual I was trying to add was part of a Active Directory Group that I wasn’t aware of, and that AD Group had “DENY CONNECT” The Deny took precedence over the individual login’s right to connect. I just dropped the login that had the Deny Connect. Problem solved. I also made the Domain Admins take him out of that group since the guy changed jobs.

  • I just downloaded sql server express 2005,  I cannot connect to a server.  I have used the same login on two other computers with the same sql server express 2005, and it worked.  

    I even tried turning off the firewall and antivirus program.  Nothing works.

    Any help would be greatly appreciated.

  • Actually I am tryint to install the billing software and connecting it with SQL Server whenever I try I get this below message. Unless I have done all the necessary steps including the Security tips but unable to connect. Please provide me support.

    (Microsoft SQL 2005 Server Express Edition)

    Unable to connect to server.

    Please check the SQL Server Authentication policy should be "SQL Server and Windows".

    To check please open SQL Enterprise Manager and look under Properties-->Security-->Authentication

  • from security tab of the sa properties, clear "enforce password policy" and type the pasword of the sa twice again. now you should be able to connect.

  • Error: 18456, Severity: 14, State: 1

    would anyone know how to correct this error?

    What should I do?

  • Thank you for the helpful post, Il-Sung Lee.

    I was able to determine, by looking at the SQL Server log, that the State was 8. Password mismatch.

    However, I have about 5 services running that access the local SQL Server instance, and there doesn't not seem to be any information in the log about which application/service attempted to login with an incorrect password.

    Is there a way, for example by using Process Monitor or somesuch tool, to know which Process ID tried to logon?

  • I am trying to connect my client machine with sql server 2000 but the error thrown each time is

    Connection :failed

    SQL State : 28000

    Sql Server Error : 18452

    [microsoft][sql server driver][sql server][login failed for user null.

    Reason : not associated with the trusted sql server connection.

    This connection is functioning properly in 3 client machine but throwing error for two client machine.

    I have tried to change the above process described (Security-Login-----]

    and the second problem is its showing the name of server two times in the same list .. so is it conflicting with the server detection.

    Your valuable feedback is highly appreciated.

    thank you

  • On a cluster, could connect to an instance on one node with the Surface Area Configuration tool, but not the other node--I was getting state 11 error.  All security was identical, and I was logged in with Admin and SYSADMIN privs (a different account than the SQL service account).

    The error from SAC was:

    Computer VSERVER does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again.

    Since this is a cluster, remote connections is always enabled.

    Even though I was logged in with an Administrative account, I did a "Run As" on the SAC menu item and UNCHECKED the "Run this program with restricted access" to work around this issue.

    I did not see any differences in WMI security between the cluster nodes.  This instance is running SP3 build 4266.

Page 20 of 27 (396 items) «1819202122»