28 thoughts on “Troubleshooting Login failed Error 18456”

  1. 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.

  2. 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.

  3. 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.

  4. 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. Basheer – What is the error message you are getting? Can you elaborate your problem, I couldnt get more info from your comment

    1. 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

  5. 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. @rashmi — This is not related to login failure. The ID which you used doesnt have permisison in database. Grant necessary permisison

  6. 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

  7. 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.

  8. 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. 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…

  9. Pingback: login error 18456

Leave a Reply

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


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>