locked
SQL 2008 mirroring Login failed for user 'xxxx'. Reason: Failed to open the explicitly specified database. RRS feed

  • Question

  • I have SQL 2008 SR2.  I have a mirrored database in synchronous mode with no witness (I've tried with witness and asynch mode with same error).  My company has a .net 2.0 app that connects to the server.  Once the mirror has been established the error log on my mirror server fills with this message -> Login failed for user 'xxxxx'. Reason: Failed to open the explicitly specified database.  Where the xxxxx is the user/login name.

    All logins are scripted via sp_revlogin each night and rebuilt on the mirror machine.  I have verified that the passwords match and all users and logins exist on the mirror with same SID.  My .net connection string does not (yet) even specify the mirror server as "failover partner".  My question is:

    How do these errors keep appearing the the mirror's error log?  I have not told the client that the mirror even exists.  Is this .net grabbing the mirror name from the principle on its own?  My theory is the as the mirror replays the log entries it does so with the same user context in which the log entries were created and this is why is see so many login failed messages (just a wild guess).

    They don't appear harmful but I would at least like to know what is going on.

     

    Thanks,

     

    Glenn

    Monday, September 20, 2010 8:07 PM

All replies

  • Is the login XXXX that you are talking about in this case is the application login or the windows \ domain account that was used for configuring mirroring?


    Thanks, Leks
    Monday, September 20, 2010 8:54 PM
    Answerer
  • No.  These are standard logins that the .net application uses to login to the sql server.

     

    Glenn

    Tuesday, September 21, 2010 6:02 PM
  • Hi,

    Pls run profiler at Mirror server and get the more detials like application Name, host name etc.

    The error is clear that user 'XXXX" is trying to access Mirror DB (In resrore state) and hence Failed to open the explicitly specified database.

    Wednesday, September 22, 2010 2:52 AM
  • Login failed on mirroring and  you say that  your app did not connect to? Have you verified that all modified data has been transefered, means principal and mirror are syncronized?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 22, 2010 9:58 AM
  • Hi,

    Good idea.  I ran a "failed login" profiler on the mirror.  Within seconds I got

     

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

    I replaced the real username with "username" and the IP address with "0.0.0.0" for the example.  The trace shows IIS as the application name and the host IP address is the IIS server.

    I will add that the same trace gets nothing if the mirroring is turned off and that is with no changes to the application or connect string.

    Wednesday, September 22, 2010 12:33 PM
  • Hi Uri,

     

    Yes.  I get login failed errors on the mirror server but the app should never be connecting there.  In terms of verifying that all modifed data has been transferred -- I restore full backup and all logs to the mirror and leave in a restoring state.  Then I turn on mirroring (I've tried both kinds with and without witeness) and the databases show to be in a synchronized state.  Is that all I have to do? 

    Wednesday, September 22, 2010 12:35 PM
  • Has anyone found a solution to this?  We are experiencing the same issue with three different mirroring set ups that we have.  The weird thing is that we have connections that are being sent to the failover partner and there is no evidence in sql server logs that a failover had taken please.  thank you.
    Monday, October 1, 2012 7:42 PM
  • Hi Team,

    as Per URI transer logins form principal to mirror server after that user was not mappped at that time

    you have to map that perticular user to the same login. even in our environment also happend the same i have folllow the above

    USE AdventureWorks2012;
    GO
    EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';

    Regards

    Subu


    subu

    Tuesday, October 2, 2012 3:28 AM
  • Hmm, if you did not specify failover partner in the connection string  then you need to turn on SQL Profiler to see who is trying to login the mirroring database ( actually you cannot access that db as it is in recovery mode).

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Tuesday, October 2, 2012 10:47 AM