locked
Failed to open the explicitly specified database RRS feed

  • Question

  • We have an application loading data into a SQL Server 2008 R2 Express database. The application runs about half way through then terminates with an error saying that SQL login failed for the current Windows user. We are using Windows authentication and it works for the part of the data.

    The error log has this information

    Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
    - <System>
      <Provider Name="MSSQL$AURORAXMP" />
      <EventID Qualifiers="49152">18456</EventID>
      <Level>0</Level>
      <Task>4</Task>
      <Keywords>0x90000000000000</Keywords>
      <TimeCreated SystemTime="2012-06-27T02:17:41.000000000Z" />
      <EventRecordID>27743</EventRecordID>
      <Channel>Application</Channel>
      <Computer>D0101026.idacorp.local</Computer>
      <Security UserID="S-1-5-21-140328221-716536946-747242864-6054" />
      </System>
    - <EventData>
      <Data>IPCO\SLW5493</Data>
      <Data>Reason: Failed to open the explicitly specified database.</Data>
      <Data>[CLIENT: <local machine>]</Data>
      <Binary>184800000E00000013000000440030003100300031003000320036005C004100550052004F005200410058004D0050000000070000006D00610073007400650072000000</Binary>
      </EventData>
      </Event>

    Can somebody suggest where to look for the problem?

    Thanks,

    Wednesday, June 27, 2012 3:03 PM

Answers

  • This error means in most cases that the target database to connect to has been renamed.

    Arthur My Blog

    • Proposed as answer by ArthurZ Thursday, November 28, 2013 3:26 PM
    • Marked as answer by Olaf HelperMVP Sunday, January 5, 2014 9:36 AM
    Thursday, November 28, 2013 3:25 PM

All replies

  • Look at the connection string for the code that is failing - just set your IDE to break on error.  Either you are specifying the wrong database name, or not specifying a database name and you don't have permissions to the default database for your user (or your useer has an invalid default database)

    Chuck Pedretti | Magenic – North Region | magenic.com


    Wednesday, June 27, 2012 3:25 PM
  • If it works for part of the data, but not all of it, I would ask if there was a network issue between the sql instance and active directory.

    HTH.

    Wednesday, June 27, 2012 3:28 PM
  • HI Don_Don,

    >> network issue between the sql instance and active directory.

    Regarding to your description, the SQL and AD are independent.

    SQL is database engine. AD is used to store information about the network resources across a domain and also centralize the network.

    The relationship between SQL Server and AD seems to simply be the ability to publish a SQL Server and its databases in Active Directory much like file shares. That seems to be a feature with limited usage as only applications that are designed to use AD to look for resources would be able to use it.

    For more information about Active Directory Services for SQL Server, please refer to the following articles:
    1. http://msdn2.microsoft.com/en-us/lib...0(sql.80).aspx
    2. http://www.databasejournal.com/featu...le.php/2176561

    For network issue about AD and SQL Server, please refer to Publishing SQL Server in Active Directory.

    I hope those useful articles satisfy your desire to learn.

    Regards,
    Amber zhang
    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    • Edited by amber zhang Thursday, June 28, 2012 4:31 AM
    Thursday, June 28, 2012 4:30 AM
  • Check the SQL Server Error Logs to find out what is the reason for the windows login failure. 

    these articles will help 

    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx 

    http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/

    Also please check the state of the database during the time you get the error. It might be offline. 

    Check no 40 in the table from the below article

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, June 28, 2012 8:20 AM
  • HI Don_Don,

    >> network issue between the sql instance and active directory.

    Regarding to your description, the SQL and AD are independent.

    SQL is database engine. AD is used to store information about the network resources across a domain and also centralize the network.

    The relationship between SQL Server and AD seems to simply be the ability to publish a SQL Server and its databases in Active Directory much like file shares. That seems to be a feature with limited usage as only applications that are designed to use AD to look for resources would be able to use it.

    For more information about Active Directory Services for SQL Server, please refer to the following articles:
    1. http://msdn2.microsoft.com/en-us/lib...0(sql.80).aspx
    2. http://www.databasejournal.com/featu...le.php/2176561

    For network issue about AD and SQL Server, please refer to Publishing SQL Server in Active Directory.

    I hope those useful articles satisfy your desire to learn.

    Regards,
    Amber zhang
    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Hi Amber - thanks for the links and the response.  I realize that sql and ad are independent of each other and, according to best practices, should be on different servers.  I was answering the question with the understanding that the OP was using Windows Authentication to access a SQL Server.  AFAIK, in using Windows Authentication, sql looks up the login's basic credentials from ad; is that not true?  If it is, it follows then that if there is a network hiccup between sql and ad, the windows login won't be authenticated, and therefore the process will fail.

    In addition, sometimes applications are not written such that the process flows where:  login -> all the activity -> logoff.  Unfortunately, there are a lot of applications, especially web-based ones that are:  login -> some activity -> logoff :: login -> some more activity -> logoff, etc. 

    Anyway, that was the flow of my train of thought.  Please don't hesitate to correct my misunderstanding.


    • Edited by Don_Don Friday, June 29, 2012 3:32 PM
    Friday, June 29, 2012 3:31 PM
  • Michael,

    looks like the batch process which trying get access of different database within it's process, and the target database is in RESTORING/IN RECOVERY state that's why an explicit connecion request was failed for that DB, please let me know if you have any more query, thanks.


    With regards, Ram Pravesh Singh India- Delhi

    Friday, June 29, 2012 4:09 PM
  • Ram Pravesh Singh - Thank you very much for the reply. I appreciate you help on this. Can you tell me how you determined that the database was in RESTORING/RECOVERY state? Is that information present in the error log data?

    Friday, June 29, 2012 4:26 PM
  • Don_Don - You are correct, the application is logging in (connecting), loading one year's data, logging off (disconnecting), then logging in and loading the next year's data.

    The error logs don't show any network errors or security errors during the run. Do you happen to know where else I might look?

    Thanks for you help on this.

    Friday, June 29, 2012 4:30 PM
  • One thing you might consider doing - we could eliminate windows authentication.  If you can change the login method of your process and use a sql login, you'd eliminate the request to ad.  I know that is easier suggested than done.  :)

    Friday, June 29, 2012 4:58 PM
  • If you are getting 18456 then it should be printed in the SQL Server error log and also the windows event log (application)

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Monday, July 2, 2012 9:04 AM
  • This error means in most cases that the target database to connect to has been renamed.

    Arthur My Blog

    • Proposed as answer by ArthurZ Thursday, November 28, 2013 3:26 PM
    • Marked as answer by Olaf HelperMVP Sunday, January 5, 2014 9:36 AM
    Thursday, November 28, 2013 3:25 PM