Troubleshooting Login failed Error 18456

Input : select * from sys.sysmessages where error = 18456

Output: Login failed for user ‘%.*ls’.%.*ls%.*ls

This is one of the infamous error message (and number) that most of the DBAs and developers have come across while working on SQL server. This message simply denotes that the client call was able to reach the SQL server and then an ACCESS was denied to the particular login for a reason. To figure out the exact reason, this error number 18456 with its STATE number is logged into the SQL server error log file, if SQL server was allowed or configured to capture the failed logins.

Configuring SQL server for capturing login failures:

By default, SQL server is configured to capture only failed logins but it can be changed to any of the options as mentioned in this books online link http://technet.microsoft.com/en-us/library/ms188470.aspx

Below figure shows these options to have login failed messages written into error log

error_18456_1

Accessing Error log:

Now that, we know SQL server logs all login failed messages into its error log and windows event viewer but how do I access them?

Windows event viewer:

GO to start –> Run –> Eventvwr –> open up the application logs, and now we could see the login failed error message with computer name, instance name, date and time and finally the reason for the login failed

error_18456_2

SQL Error log:

SQL server error log can be viewed from multiple places

  • If we have gained access or can gain access to SQL server with different logon credentials then always use sp_readerrorlog, xp_readerrorlog or use the GUI option of opening up Management node –> SQL server Logs –> View –> SQL server log. Same sample shown above looks like

error_18456_3

  • In a case where we cannot gain access to SQL server, then we may use the actual error log path and open the txt file physically from the file system. Use SQL server configuration manager to find the error log path and from there you could open the file. Get the value next to –e parameter and that gives the actual error log file location

error_18456_5

Typically the error log files are available in install directory for SQL server.

SQL server 2005:

C:\MSSQL\MSSQL.1\MSSQL\LOG\Errorlog

SQL server 2008:

C:\MSSQL\MSSQL10.instanceID\MSSQL\Log\Errorlog

InstanceID – MSSQLSERVER for default instance and for named instance it’s the name of the instance

STATES of 18456

State 1:

This is a very generic error message that is sent to the client tools to deliberately hide the nature of the login failure issue. However, the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition with its state number.

error_18456_6

State 2 and 5:

This state occurs when a SQL server login logs in with the name that doesn’t exist in sql server. This error mostly comes in when users specify wrong user name or misspell the login name. I am logging in to my instance with a login name called DOESNTEXIST that really doesn’t exist and let’s see what the error state in error log is.

error_18456_7

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

Login failed for user ‘DOESNT EXIST’. Reason: Could not find a login matching the name provided. [CLIENT: ]

State 6:

This state occurs when a user tries to login with a WINDOWS account but specifying that as a sql server account. I have a windows account test (domain\test) but I am specifying it as a sql account and trying to login into SQL server, let’s see what state the error log has

error_18456_8

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

Login failed for user ‘domain\test’. Reason: Attppting to use an NT account name with SQL Server Authentication. [CLIENT: ]

State 7:

This state occurs when a wrong password is specified for a login which is disabled too. In this case, my SQL server user ‘Leks’ is disabled and I’m mentioning a wrong password for the connection

error_18456_9

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

Login failed for user ‘Leks’. Reason: An error occurred while evaluating the password. [CLIENT: ]

For the accounts (logins) that are disabled and if you specify the correct password, the error log is logged with 18470

Error: 18470, Severity: 14, State: 1.

Login failed for user ‘Leks’. Reason: The account is disabled. [CLIENT:]

State 8:

This state occurs when password is not correct in the connection string for any SQL server authenticated logins. I’m logging into SQL server using ‘sa’ account with wrong password

error_18456_10

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

Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT:]

State 9:

This state means that the password was rejected by the password policy check as an invalid one. The policy API has rejected the password with error NERR_BadPassword. See more info on this error and visit http://msdn.microsoft.com/library/default.asp?url=/library/enus/netmgmt/netmgmt/net_validate_output_arg.asp

State 10:

This is one of the rare state and is very well documented here – http://support.microsoft.com/kb/925744

State 11 & 12:

This state means the login was valid but server access failed. One such example is when a windows login in trying to access sql server that wasn’t explicitly added to sql server (at least starting from 2008). To overcome this error, you can add that domain\windows account to sql logins explicitly.

Other reasons for this to happen are when a login is denied access (revoking connect permissions to SQL) to SQL server and UAC issues.SQL server product team covered this state extensively here –http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

Windows account which do not have access

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

Login failed for user ‘domain\user’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT:]

SQL account that was denied access

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

Login failed for user ‘Leks’. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT:]

State 13:

This state occurs when any login tries to access to sql server with services paused on it. But for this reason, there will also be other error number 17142 logged along with 18456.

error_18456_11

Error: 17142, Severity: 14, State: 0.

SQL Server service has been paused. No new connections will be allowed. To resume the service, use SQL Computer Manager or the Services application in Control Panel.

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

Login failed for user ”. Reason: SQL Server service is paused. No new connections can be accepted at this time. [CLIENT:]

State 16:

This state occurs for logins that do not have access to the target database or the database doesn’t exist anymore with the same name or offline. We always specify the database in the connection string as initial catalog or using -d parameter.

This can be fixed by granting access to the database and sometimes orphan users existing in the database. Orphan users can be fixed by sp_change_users_login

error_18456_12

This state occurs in SQL server 2005 and same is changed to 40 in SQL server 2008 and above

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

Login failed for user ‘Leks’. [CLIENT: <local machine>]

State 18:

This state occurs when a sql login is added with USER MUST CHANGE THEIR PASSORD ON FIRST LOGON or a login that needs to have its password changed as per the domain or windows password policy ; this state gets in to error log when the new password box prompts up.

error_18456_14

This error is logged with state 18488

Error: 18488, Severity: 14, State: 1.

Login failed for user ‘Tester’. Reason: The password of the account must be changed. [CLIENT: ]

State 23:

This state can happen due to couple reasons; first being simultaneous action of shutting down SQL SERVER and any incoming logins to sql server takes place. The other one is documented here as an issue http://support.microsoft.com/kb/937745

State 38:

This is similar to state 16 but this was introduced from SQL server 2008 and above.

Database doesn’t exist or login doesn’t have access to the database.

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

Login failed for user ‘Leks’. Reason: Failed to open the explicitly specified database. [CLIENT: ]

State 40:

This state occurs when the login’s default database doesn’t exist in SQL server or offline or the login doesn’t have access to the default database. This state is always logged alongside with error 4064

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

Login failed for user ‘Leks’. Reason: Failed to open the database specified in the login properties. [CLIENT: ]

State 58:

This state occurs when a SQL server login is used for accessing SQL server when SQL server is installed with windows authentication mode.

You may have to use only windows login or change the authentication to mixed authentication for this to work – http://msdn.microsoft.com/en-us/library/ms188670.aspx. The authentication mode change always requires a SQL restart to come into effect.

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

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

I have tried and covered most of the states that I know of, and if you find states that I haven’t discussed happy to hear and add to this list. Apart from the error 18456, there are other login failure errors that you might have to keep an eye on

18451

Login failed for user ‘%.*ls‘. Only administrators may connect at this time.%.*ls

18452

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.%.*ls

18458

Login failed. The number of simultaneous users already equals the %d registered licenses for this server. To increase the maximum number of simultaneous users, obtain additional licenses and then register them through the Licensing item in Control Panel.%

18459

Login failed. The workstation licensing limit for SQL Server access has already been reached.%.*ls

18460

Login failed. The number of simultaneous users has already reached the limit of %d licenses for this ‘%ls’ server. Additional licenses should be obtained and installed or you should upgrade to a full version.%.*ls

18461

Login failed for user ‘%.*ls‘. Reason: Server is in single user mode. Only one administrator can connect at this time.%.*ls

18462

The login failed for user “%.*ls“. The password change failed. The password for the user is too recent to change. %.*ls

18463

The login failed for user “%.*ls“. The password change failed. The password cannot be used at this time. %.*ls

18464

Login failed for user ‘%.*ls‘. Reason: Password change failed. The password does not meet Windows policy requirements because it is too short.%.*ls

18465

Login failed for user ‘%.*ls‘. Reason: Password change failed. The password does not meet Windows policy requirements because it is too long.%.*ls

18466

Login failed for user ‘%.*ls‘. Reason: Password change failed. The password does not meet Windows policy requirements because it is not complex enough.%.*ls

18467

The login failed for user “%.*ls“. The password change failed. The password does not meet the requirements of the password filter DLL. %.*ls

18468

The login failed for user “%.*ls“. The password change failed. An unexpected error occurred during password validation. %.*ls

18470

Login failed for user ‘%.*ls‘. Reason: The account is disabled.%.*ls

18471

The login failed for user “%.*ls“. The password change failed. The user does not have permission to change the password. %.*ls

18482

Could not connect to server ‘%.*ls’ because ‘%.*ls’ is not defined as a remote server. Verify that you have specified the correct server name. %.*ls.

18483

Could not connect to server ‘%.*ls’ because ‘%.*ls’ is not defined as a remote login at the server. Verify that you have specified the correct login name. %.*ls.

18485

Could not connect to server ‘%.*ls’ because it is not configured to accept remote logins. Use the remote access configuration option to allow remote logins.%.*ls

18486

Login failed for user ‘%.*ls’ because the account is currently locked out. The system administrator can unlock it. %.*ls

18487

Login failed for user ‘%.*ls‘.Reason: The password of the account has expired.%.*ls

18488

Login failed for user ‘%.*ls‘.Reason: The password of the account must be changed.%.*ls

18489

The dedicated administrator connection is in use by “%.*ls” on “%.*ls“.%.*ls

Happy “login failure “troubleshooting


Posted

in

by

Comments

35 responses to “Troubleshooting Login failed Error 18456”

  1. […] It was very strange, so I decided to investigate. To check that domain is not related to the issue I created local admin account and logged to system from that account. But the result was the same: error 18456. Error message is intentionally uninformative to prevent disclosing of sensitive information. So I took a look at the logs where I found additional ‘state 11′ piece of info. As google told me helpfully state 11 means ‘Valid login but server access failure’ (you can see other states here or here). […]

  2. VidhyaSagar avatar
    VidhyaSagar

    Error: 18456, Severity: 14, State: 58.
    Login failed for user ‘sagar’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]

    Lexx has missed this state. State 58 describes that SQL Server is configured for Windows authentication mode and user is trying to connect using SQL authentication. You need to change authentication mode for SQL Server. Check the KB http://msdn.microsoft.com/en-us/library/ms188670.aspx article for changing authentication mode.

  3. Bijoy Kaiprath avatar
    Bijoy Kaiprath

    This was the article that i was searching for a long time. Thank you for giving a brief and clear picture about almost all login errors.

  4. AnilV avatar
    AnilV

    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.

  5. basheer avatar
    basheer

    i did all what the solution what you mention but still not able to slove as iam using dell server rs510 installed sql server2005 enterprise edition i am getting the same error message.any alternate solution for this issues,iam as system admin i loged through admin.

    1. admin avatar
      admin

      Basheer – What is the error message you are getting? Can you elaborate your problem, I couldnt get more info from your comment

  6. andrew avatar
    andrew

    so how can i fix state 5?
    thanks mate

    1. Lekss NZ avatar
      Lekss NZ

      Hi Andrew,
      You cannot use a login that doesnt exist in SQL server.You have to connect with a login that exist in SQL server

      1. Juan avatar

        Ok, but how do I find what program is using that logon? I am not attempting to login to SQl server manager. I get this in my event log . Thanks

  7. rashmi avatar
    rashmi

    Hi,

    A user is getting below error any suggestion how to fix this
    Cannot connect to servername

    Failed to retrive data for this request(Microsoft.sqlserver.smoEnum)
    An exception occured while executing a Transcat-sql statement or batch.(Microsoft. Sqlserver.connectionInfo)
    The select permission was denied on the object ‘configurations’, database ‘mssqlsystemresource’, schema ‘sys'(microsoft Sql Server , Error:229)

    1. VidhyaSagar avatar
      VidhyaSagar

      @rashmi — This is not related to login failure. The ID which you used doesnt have permisison in database. Grant necessary permisison

  8. dominique avatar
    dominique

    Hello,

    I have the

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

    Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.
    Reason: Token-based server access validation failed with an infrastructure error.
    Check for previous errors. [CLIENT: 10.32.159.28]

    States 11 and 12 simply mean the Windows user coming in does not have login access to the server. Valid login but server access failure. SQL Server validated the Windows user’s token, figured out who it is,
    but the Windows user has not been granted access to the server.

    I am running the installation already as Administrator

    http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/
    you are saying adding the account to the sql logins but if I add NTAUTHORITY\ANONYMOUS LOGON it seems I am opening a security breach if I add this account isn’t it?
    Any other way in that case to do?
    Thanks,
    Dom

  9. Mohamed Azlan avatar

    Thank you for the very thorough troubleshooting guidelines you have provided in this website. Although my problem still remain unresolved I have picked up a tip or two from here.

    Recently to deploy my site I changed my authentication from windows to SQL authentication. Successfully added the user to ‘Security\Logins’. But having problem enabling database role membership as it returns error 15247. I believe error 15151 is also that of permission issues.

    If anyone have come across this problem before I really hope to get a few input to work around on this.

    Thank you in advance.

  10. Mohamed Azlan avatar
    Mohamed Azlan

    Referring to my comment dated January 7, 2012 at 5:59pm

    After struggling for so long i found a solution to this.

    Before you reinstall SQL server using Mixed Mode Authentication. (I know the pain of doing that. Sometimes, it wont work for this because you backup your database.mdf under the non permissible user and when you attach it after reinstalling SQL it still shows the same 15247 error)

    What worked for me is;

    Logged in under windows authentication

    1) Went to Database\Security -> Right clicked on my role -> Selected all the necessary permissions

    2) Went to \Security ->Right clicked on my role -> Selected all the necessary permissions

    3) Restart SQL

    4) Log in using SQL authentication

    5) Repeat steps (1) & (2)

    By God’s grace it should work. If not, try reinstalling SQL with a positive mind 🙂

    1. VidhyaSagar avatar
      VidhyaSagar

      Thanks for the reply Mohamed

  11. Saeed Neamati avatar

    I get this error in SMO (Server Management Objects). Do you have any idea how can I stop enforcing password policy in SMO?

    1. VidhyaSagar avatar
      VidhyaSagar

      Saeed,
      I don’t think so you can stop it in SMO, instead you need to turn off password policy for that login

  12. Matt Mason avatar

    Great help. Thanks. I never thought to look in the event logs.

  13. […] SQL-Articles » Troubleshooting Login failed Error 18456This is one of the infamous error message (and number) that most of the DBAs …. 18486. Login failed for user ‘%.*ls’ because the account is currently locked out. […]

  14. barandaf avatar
    barandaf

    i have sql error 18456 state 8 with both sql and windows user login.
    how i can solve this error.

    1. VidhyaSagar avatar

      As described check your password. Please provide correct password while logging in.

    2. Elvina avatar
      Elvina

      Hi Barndaf, did you find the solution to the ql error 18456 state 8 with both sql and windows user login. i am in the same situation.. Please assist…

  15. Paras Doshi avatar

    Thanks for this tutorial, it helped me! To be specific, The part where you mentioned how to access security proprieties of a server was helpful.

  16. […] on December 04, 2012: For more Error States, please refer to this blog here. It saved me several times, recently, in figuring out what was going […]

  17. […] After looking more carefully at the problem in the SQLServer log i can see that the error code is: 18456 severity 14 with state 38. Read about it on: http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/ […]

  18. […] Troubleshooting Login failed Error 18456 | SQL-Articles […]

  19. […] Troubleshooting Login failed Error 18456 | SQL-Articles – Input : select * from sys.sysmessages where error = 18456. Output: Login failed for user ‘%.*ls’.%.*ls%.*ls. This is one of the infamous error message (and number …… […]

  20. moti avatar
    moti

    login failed for user ‘sa’ Error 18456, Severity 14, State 8
    i can create the connection string successfully i can not connect to the application database

    1. VidhyaSagar avatar

      state 8 represents you are providing wrong password

  21. Martin avatar
    Martin

    Sorry for waking up an old thread. My problem is weird as the errorlog states:

    Login succeeded for user ‘sa’. Connection: non-trusted. [CLIENT: ]
    Login succeeded for user ‘sa’. Connection: non-trusted. [CLIENT: ]
    Fehler: 18456, Schweregrad: 14, Status: 16.
    Login failed for user ‘sa’. [CLIENT: ]

    First I have 2 successful logins and a third one is failing with state 16. How do I have to understand this? What can be the cause?

    BR
    Martin

    1. VidhyaSagar avatar

      This means the login is not from a trusted domain. In your case someone from different domain trying to access the server. For example the server is connected to a doamin called SQLArticles so any connections to this server should happen through SQLArticles domain as a trusted authentication. Instead if a user from SQLArticlesDevTest access the server as trusted authentication then this error is thrown.

  22. Vijay avatar
    Vijay

    I’m getting error pertaining to state 2 and 5.

    The user that is mentioned in the error is not available but still i’m getting this issue.

    Any suggestions please?

    Thanks in advance.

  23. Giuseppe avatar

    Thank you very much for this article. It display a pletora of strictly related error messages in one single page. Well done.

Leave a Reply

Your email address will not be published. Required fields are marked *