Error 18456 – Login Failed for User.

Views 2200

Error 18456 is the generic error message for Login Failures in SQL Server. Depending on your situation this could be a simple misspelled password or the windows account does not have permission to the Instance and/or database. However I am not looking at those simple issues around this SQL Server error message.

“Error: 18456, Severity 14, State 38. Login failed for user ‘DomainServiceAccountName’. Reason: failed to open the explicitly specified database.”

Ok we can see from the error message above which particular account is having the login failure, but failed to open which database?

If you happen to have this issue there is a simple fix for it. A colleague of mine shared this with me.

How often does this occur? If you know it happens frequently then the time to actually retrieve this information can be pretty short, but it may be at a not so nice time.

    • Start up a SQL Server Profiler Trace
    • Remove all events from your trace and then select “Errors and Warnings” Event
    • Select User Error Message
    • Set your column filter for the Login Name filter type to your login that is experiencing the issue
    • Set your error filter type in (4060 , 18456)

Windows-Live-Writer-Error-18456_C824-image_2

Once the event triggers you will see below in your trace output.

Windows-Live-Writer-Error-18456_C824-image_6

Now you can work out why your process is trying to connect to the particular database.

Good luck…..

Leave a Reply

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

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search