Failed to open the explicitly specified database error

Question

Hi,
I've just installed a new SQL Server 2008 on Windows Server 2008 x64 (and SQL 2008 SP1), while it seems to be running well, I keep getting the same error appearing in the event log:

Login failed for user 'domain\username'. Reason: Failed to open the explicitly specified database. [CLIENT: x.x.x.x]

The Admin user name comes up once every 20 seconds or so, and the NT AUTHORITY\SYSTEM account appears twice every 5-6 seconds, obviously this is filling the event log pretty quickly...

The SQL Server event log has:

Error: 18456, Severity: 14, State: 38.

Then the above login failed message, the Windows event log has:

- System 

  - Provider 

   [ Name]  MSSQLSERVER 
 
  - EventID 18456 

   [ Qualifiers]  49152 
 
   Level 0 
 
   Task 4 
 
   Keywords 0x90000000000000 
 
  - TimeCreated 

   [ SystemTime]  2009-05-07T15:53:26.000Z 
 
   EventRecordID 20656 
 
   Channel Application 
 
   Computer servername 
 
  - Security 

   [ UserID]  S-1-5-18 
 

- EventData 

   NT AUTHORITY\SYSTEM 
   Reason: Failed to open the explicitly specified database. 
   [CLIENT: x.x.x.x] 
   184800000E000000080000004B004100420030003100530050000000070000006D00610073007400650072000000 


--------------------------------------------------------------------------------

Binary data:


In Words

0000: 00004818 0000000E 00000008 0041004B 
0008: 00300042 00530031 00000050 00000007 
0010: 0061006D 00740073 00720065 0000 


In Bytes

0000: 18 48 00 00 0E 00 00 00   .H......
0008: 08 00 00 00 4B 00 41 00   ....S.V.
0010: 42 00 30 00 31 00 53 00   R.N.A.M.
0018: 50 00 00 00 07 00 00 00   E.......
0020: 6D 00 61 00 73 00 74 00   m.a.s.t.
0028: 65 00 72 00 00 00         e.r...

I'm fairly sure that NT AUTHORITY\SYSTEM needs some sort of access to the master database, but I've tried adding that already and it didn't work.

Thanks,
Matt

Answer 1

Not sure if I would ever add that user as a login with access to master, so I'd advise reversing that.

Find out what application/process is triggering these failures first - I've got a blog entry which describes how to trace login failures which should help you do this.

Once you've isolated the cause you can establish the access required, or if it's even necessary.

---

ajmer dhariwal || eraofdata.com

Answer 2

Is default database has been set to the logins above? If yes did the login has necessary privilege on the databases?
I think either you are calling a database explicitly through connection string or default database is not set for the login, which causes the login to access some other database where it wont have privilege. Check the default db and provide necessary privilege to the user in the db

---

Vidhya Sagar. Mark as Answer if it helps!

Answer 3

Thanks to your great blog, I was able to trace the problem down to the backup software installed on the server (in this case the software was misconfigured and was using an invalid login).

Thanks very much!
Matt

Answer 4

Hey,

i am using servvice account its default database is master.
master database also has that user.
this service account has "sysadmin" rights.
If you can give me suggestion that would be great.

Thanks,
mihir

Answer 5

Thanks Ajmer,

I was stumped on this once for a while.  I put the trace on the machine and found that there was an application trying to connect to user databases that no longer existed.  

The error log and the alert did not identify which database(s) that could not be connected to.  The trace User Error Message had that info right there in plain sight.

Thanks for the post...

Dave