none
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
    Thursday, May 07, 2009 4:07 PM

Answers

All replies

  • 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
    Thursday, May 07, 2009 10:05 PM
  • 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!
    Friday, May 08, 2009 2:38 AM
  • 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
    Thursday, May 14, 2009 9:47 AM
  • 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
    Wednesday, August 05, 2009 3:31 PM
  • 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

    Wednesday, May 29, 2013 6:43 PM