0

Bit of a confusing one here.

We are getting repeated messages in the SQL Server error log as below for a SQL Server instance:

NT AUTHORITY\SYSTEM
Reason: Failed to open the explicitly specified database XXX [CLIENT: xx.xx.xxx.xx]

SQL Server Profiler shows nothing nor does sp_who2 or sys.dm_exec_requests.

The client IP Is actually the same SQL Server instance, with the source in the Windows Application Log being MSSQLSERVER - so its trying to connect to itself but failing.

However I cannot find what exactly is occurring. The start of these messages is recorded in the SQL Log. As you can see the database was started up due to a CHECKDB. The CHECKDB completed then the login errors have been there since.

2015-07-13 22:11:27.790 spid30s Starting up database XXX.
2015-07-13 22:11:28.480 spid30s CHECKDB for database XXX finished without errors on 2015-07-11 03:36:12.720 (local time). This is an informational message only; no user action is required.
2015-07-13 22:11:33.780 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database XXX. [CLIENT: XX.XX.XXX.XX]
2015-07-13 22:11:38.750 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database XXX. [CLIENT: XX.XX.XXX.XX]

Any info on this would be appreciated!

| improve this question | |
  • Profiler shows nothing if you are not watching the right events. You should be able to get an application name from watching failed logins in profiler. – user507 Jul 14 '15 at 13:17
  • The application is SQL Server though. Which is why I think profiler is not picking up on it. – hpk89 Jul 14 '15 at 13:22
  • What service account do the services for SQL Server run under? – user507 Jul 14 '15 at 13:24
  • Who is the database owner ? I normally set the db owner to sa. – Kin Shah Jul 14 '15 at 13:25
1

I've seen this before. I believe that it was due to autoclose. But I could not recall from memory. Credit here goes to Aaron Bertrand.

Usually this means the login's default database is offline (perhaps due to AutoClose) or no longer exists. Resolve by fixing the missing database, or changing the login's default database using ALTER LOGIN (for older versions, use sp_defaultdb, which is now deprecated). This is reported as state 16 prior to SQL

Server 2008. Error: 18456, Severity: 14, State: 40. Login failed for user ''. Reason: Failed to open the explicitly specified database.

Blog post regarding SQL login errors

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.