Wednesday, March 14, 2012

Continual "Error 18456" problem solved - the quick and dirty version

Looking at the log today for my local SQL Server Express 2008 R2, I noticed quite a lot of these:
Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>] 
Error: 18456, Severity: 14, State: 38.
This was happening once a minute, and made it difficult for me to look through the log for what I really needed to see.   None of the databases were offline or otherwise disabled.  So I asked myself, "what database is it trying to open?"

Enter SQL Profiler and the event called "User Error Message" - under "Errors and Warnings".  That provided me with this info:
Cannot open database "SQLdmRepository" requested by the login. The login failed.
Hmmm...  To be completely and embarrassingly honest, I didn't know why that was happening.  And since it was only a local server that I used only occasionally and is not critical, I simply created a new database called "SQLdmRepository", which stopped all those log entries.  

Obviously, it doesn't fix the real problem.  I could dig into this more and solve it for good, but the immediate need was to read my log without all that extraneous stuff.

Later that day...

OK, so I couldn't help it.  I had to find out.  It turns out that there is a service running on my machine for Idera SQL Diagnostic Manager.  When I uninstalled and reinstalled SQL Server Express last month, I inadvertently overlooked its database.  Now, however, my version of SQL Server is not compatible with the version of Idera SQL Diagnostic Manager on my machine.  Live and learn...

No comments:

Post a Comment