A misleading SQL Error Message Error: 18456, Severity: 14, State: 38

On Friday I had to help a client out with an error that kept appearing in their event logs:

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

It took me a while to troubleshoot the error. The client’s internal system administrator (who was quite sharp) only had to call me in in the first place because the error was a little misleading. See the first thing I did when I saw that was audit login failures. In the trace, the database was listed as master. The user had full access to master. However, I later learned that the user was switching from master to a non-existent database, which was triggering this error. I figured this out thanks to Sadequl Hussain‘s article, SQL Server Error 18456: Finding the Missing Databases.

Sadequl explains in detail the how and the why. However, the take home is you need to trace for User Error Message to get the message that tells you what database you are connecting to.

This took me about an hour to solve. Honestly, it was a bit humbling of an experience. It took me an hour to figure out something a full time senior DBA would probably be able to solve in 15 minutes. However, I’ll probably be able to solve this error in 15 minutes myself go forward. Finally, the fact that it took me a while to find this one blog article that explained what the issue actually was proves how dependent I’ve become upon google.