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)
Once the event triggers you will see below in your trace output.
Now you can work out why your process is trying to connect to the particular database.