locked
Login failed for user 'User'. Reason: Failed to open the explicitly specified database 'DB'. RRS feed

  • Question

  • We are getting below error on mirrored server.The db is in recovery mode.

    Login failed for user 'User'. Reason: Failed to open the explicitly specified database 'DB'. [CLIENT: IP]

    How can I resolve this error?

    Tuesday, April 14, 2015 9:09 AM

Answers

  • Hi

    What is it you're trying to do?  If a database is in recovery mode then it cannot be accessed until either you have completed all of the data recovery and set the database to online; or you want to leave the database in STANDYBY/READ ONLY mode where you can restore additional transaction logs but you are still able to read the data.


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, April 14, 2015 9:12 AM
  • Why is the database in recovery mode? Till the database is in online status, you wont be able to access the database.

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

    Tuesday, April 14, 2015 9:17 AM
  • If you are taking about mirror database and not principle server, I wonder why user or application is trying to connect to mirror db? 

    If you simple want to get away with this error, you can change the default db for that login to master or some other database which is online.

    exec sp_defaultdb @loginame='someone', @defdb='dbname'

    Tuesday, April 14, 2015 9:30 AM
  • Login failed for user 'User'. Reason: Failed to open the explicitly specified database 'DB'. [CLIENT: IP]

    Go to start-->run-->cmd

    nslookup xxx.xxx.xxx.xxx(client ip)

    nslookup is a command that can take an IP address as a parameter and tell you the name of the machine. Check if this is application or web server name you will know if this request is coming from application.

    If it’s coming for application connection string then you should check why it’s configured like that.

    If you don’t care and don’t want to see this error you can turn off login audit, by default SQL server is configured to capture only failed logins. But this way you will not even know when real problem occurs. I won’t recommend that.

    Friday, April 17, 2015 5:04 PM

All replies

  • Hi

    What is it you're trying to do?  If a database is in recovery mode then it cannot be accessed until either you have completed all of the data recovery and set the database to online; or you want to leave the database in STANDYBY/READ ONLY mode where you can restore additional transaction logs but you are still able to read the data.


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, April 14, 2015 9:12 AM
  • Why is the database in recovery mode? Till the database is in online status, you wont be able to access the database.

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

    Tuesday, April 14, 2015 9:17 AM
  • If you are taking about mirror database and not principle server, I wonder why user or application is trying to connect to mirror db? 

    If you simple want to get away with this error, you can change the default db for that login to master or some other database which is online.

    exec sp_defaultdb @loginame='someone', @defdb='dbname'

    Tuesday, April 14, 2015 9:30 AM
  • 1) check database size is proper

    2) RESTORE DATABASE Dinesh_DB WITH RECOVERY or make database online

    GO

    3) check if login there or create login.

    Recovery can happen when lot of delete statement or space issue.

    Check possible reason.

    Check and confirm.


    • Proposed as answer by AV111 Tuesday, April 14, 2015 10:31 AM
    • Unproposed as answer by AV111 Thursday, April 16, 2015 3:14 PM
    • Edited by AV111 Tuesday, April 21, 2015 8:17 AM
    Tuesday, April 14, 2015 10:31 AM
  • Hi Dave_gona,

    If you are taking about mirror database and not principle server, I wonder why user or application is trying to connect to mirror db?

    Yes, i am taking about mirror database and not principle server.

    If you simple want to get away with this error, you can change the default db for that login to master or some other database which is online.

    I want to get away with this error.I changed the default db for that login to master.But still we are getting same error.

    Is there any possibility to fix this error from db end without asking application help?

    Thursday, April 16, 2015 9:49 AM
  • If the application is specifying a database name which is currently under recovery then there is no way you could stop this error from happening.

    This should be handled in the application. As dave_gona has asked why is the application trying to connect to the mirror? It should ideally only try to connect to mirror when the failover happens.


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

    Thursday, April 16, 2015 9:52 AM
  • Login failed for user 'User'. Reason: Failed to open the explicitly specified database 'DB'. [CLIENT: IP]

    Go to start-->run-->cmd

    nslookup xxx.xxx.xxx.xxx(client ip)

    nslookup is a command that can take an IP address as a parameter and tell you the name of the machine. Check if this is application or web server name you will know if this request is coming from application.

    If it’s coming for application connection string then you should check why it’s configured like that.

    If you don’t care and don’t want to see this error you can turn off login audit, by default SQL server is configured to capture only failed logins. But this way you will not even know when real problem occurs. I won’t recommend that.

    Friday, April 17, 2015 5:04 PM
  • Check whether ur database is online/Available

    Does the default database for that login exists on server ? 

    Better drop and recreate the login

    Friday, April 17, 2015 5:34 PM