31 thoughts on “Identifying the source of SQL Server login failures (18456 errors)

  1. Pingback: Daily Blog List - Kev's Life

  2. Hi.

    Thank you VERY much for making the effort of leaving a well-written and easy to follow article.

    It helped me isolated a login failure on our MS-SQL 2000 server that was occurring every second.

    Kind Regards,

    Jason

  3. Pingback: The SQL Server Default Trace

  4. Hi,

    This is the best description I’ve found so far on how to identify failures. It’s helped me to find out quite a bit after I got done with a SQL server migration where we trimmed databases which supposedly were not in use.

    One question: Is there a way to tell what non-existent database a particular login is requesting?

    I have an unknown web-based app that is triggerred by an unknown individual which I can’t predict when it’ll happen next, so continually using SSP would seem to be taxing if run for a number of days waiting for that to happen. If I could just find out what database the user or process is trying to request, that might clue me in to what application is requesting the non-existent database to either obsolete the app or bring back online the database.

    Currently, I’m using alerts which tell me when the event happened, what the login and client IP is and the error, “Failed to open the explicitly specified database”. The

    Thanks in advance for any tips or advice!

    -Chris

  5. Hi Chris,

    This is a bit of a flaw in the current setup as the database engine knows everything (the client process id, hostname, db name the login is trying to use etc etc) but the error messages do not include all the pertinent information (for what it’s worth, it’s being addressed in the next version: http://connect.microsoft.com/SQLServer/feedback/details/590004/add-database-name-to-error-18456-message).
    The good news is that the login is still there, otherwise you’d get a different error, so I’d suggest that the you check the properties for that login in SSMS and view the default database that login is associated with – this should identify the db in question.
    If the default database is master, or some other valid database then the process in question must be specifying an alternate name in the connection string it is using to connect, so you’ll have to identify where this proc is storing that info – normally the trace identifies the hostname and process pid which should help.
    You can use SSP to save the trace template as a file and run it as a server side trace which will have minimal impact on the server (File > Export > Script Trace Definition…) and control it via sp_trace_setstatus.

    Good luck!

  6. Hi Chris, Hi Ajmer,

    for me works following to get the databasename:
    You can trace requested databasename with profiler, you must select “User Error Message” under “Errors and Warnings”! So add this Event to your trace template beside “Audit Login Failed”

    If your trace is active and the login error occurs you will see a message like:
    Then you will se textdata like “Cannot open database “XYZ” requested by the login. The login failed

  7. I was getting those mysterious Error: 18456, Severity: 14, State: 38. messages every three minutes on one of my test servers, and this helped me pinpoint the db name and the client process that was trying to access it. I had suspected it was a database we recently migrated to a production server, and the application support person was arguing it couldn’t be, he had changed all the pointers. This solved the argument quickly and finally.

    Thanks very much for posting this info.

  8. Pingback: Crystal Reports Generating SQL Server Error 18456 Severity 8 Error Logs « Justin Cooney – Senior Programmer / Analyst

  9. Thank you for the information. This has been somewhat helpful, at least more so than other articles I have found. I am having one issue though. The trace is capturing the information but it goes by so fast I can’t catch the PID on task manager when it’s caught in profiler. Is there anything I can do to see this? I don’t know what process is running to cause failure and that is what I am trying to catch. Please advise.

    Much thanks!!!

    • The trace can be stopped or paused and saved so you can go back over it in your own time.
      You can capture a similar trace of the processes and their PIDs by running an e.g. perfmon trace, or you can just open a DOS prompt (command window) on the host sending the invalid logins and typing “tasklist” and hitting return.
      This will list all the processes running on that host along with their PIDs which you can then reconcile with the trace output. You can also redirect the tasklist output to file via “tasklist > c:temptasks.txt”. If you use the following syntax with “tasklist >> c:temptasks.txt” you can append the output to a text file, which means you can repeatedly run the tasklist command on a scheduled basis, for example, and this will allow you to capture all the processes and their PIDs over the time interval you schedule it for. With the trace and tasklist data saved you can go over it at your lesiure to reconcile the logins with their PIDs.

  10. awsome article, it allowed me to find the process and the service but i’m still getting the lock outs. they are happening on the master database of our sql1/sql1 instance. the process is sqlagent90.exe and the service is SQL Server(sql1) i have verified that the account running the service is correct, i have verified that the service account is in the security folder of the master database.

    our sql enviorment is in a MS cluster. this failure aduit is only happening on this node of the cluster. i’m lost as to what to do now.

    • This is the sql server agent account and it sounds like the account is locked out or disabled in some way.
      From a dos prompt run “net user /domain” where is the full service account name (including domain name) and check the Account Active status and there is no password expiry for the account.
      If that doesn’t help then send me the full fragment from the error log showing the login failure.

  11. Hello,

    When attempting to implement kerberos authentication between BizTalk 2006 servers and SQL 2005 servers, SQL will not restart after SPN’s are created. SQL is clustered over 2 nodes. SQL runs under one service account and BizTalk is running under a different account. After restarting SQL, we get failed logins, Severity 14, State 11.

    We changed the password on the account that is running SQL, changed the password entry for SQL in the Services console and in the Cluster Admin console, but continued getting same errors. Eventually, after resetting password several times, it finally started successfully. The only errors seen between SQL Agent, Server and Event logs are the 18456, 14,11.

    The error state indicates “Login valid but server access failed”. What exactly does this mean? Access to what has failed? How do I troubleshoot this?

    • SPN’s for SQL Server can be a nightmare to configure, especially on a cluster. I guess you’ve already stepped through this doc: http://msdn.microsoft.com/en-us/library/ms189585(v=sql.90).aspx but it’s worth double-checking and listing out the SPNs (via SetSpn -L SQLServiceAccount).

      I would also check via management studio:
      – the account is not disabled
      – the database user account associated with that login is not orphaned or somehow disabled in the default database associated with that login (displayed on the login’s properties)

      Worth also ensuring that that these accounts haven’t become locked in AD.

      Let me know if that helps at all.

  12. THANK YOU. This is concise, clear, and extremely helpful. I’m a SharePoint person, not a SQL person, but this enabled me to find and solve my issues. Moreover, it adds another tool to my very limited SQL bag of tricks.

    Well Done, Sir.

  13. Pingback: Trace SQL login error | Dio Phung

  14. Pingback: login dql

Leave a Reply