locked
SQL 2k8 R2 Mirroring : Unable to access Mirror after succesful initial test and failover with error failed to open the explicitly specified database RRS feed

  • Question

  • Hello there,

       I would need your advice, if possible.

       I have used an application setup for creating 2 database on SQL 2k8 R2 Ent sp2. One of them uses TDE, DB_A.

       Also, this part of the setup creates a local group GROUP1 on 2k8 server that has as members Network Service and the computer where will be setup the application which will access the database, COMP1. So, member DOM\COMP1. This group is used to access and modify the database as per read/write.

       The second part of the setup is ran on another server (COMP1) and will install the application (IIS) that will write keys to tables on DB_A.  

       I have mirrored it with witness (Express 2k8 R2 sp3 advanced) and mirroring was fine (exporting certificate, keys etc.)

       Then, to be able to generate the same logins, I have manually created a local group, GROUP2 on 2k8 and put the members same Network Service and COMP1, even PRINCIPLE. The rest of the logins were the same.

       To keep the same logins and because the mirrored database doesn't allow to be accessed, I had failover the mirror and create the logins as per initially on Principle. I have also used the export logins procedure described by MS.

       As MS site says, if the first logon to the principle will succeed, the application cache will be updated with the mirror address and after that, if the failover will occur, application will be capable to switch automatically and the client will be able to use the name of failover partner(database).

       So, I run the client successfully and the databases were updated. At this point, I supposed the internal cache was ok, updated with mirror name.

       Then, I failover the databases. When the application tried to connect again I had the error:

    "Login failed for user DOM\COMP1: Reason Failed to open the explicitly specified database (CLIENT 10.1...)" on the initial principle.

    Error 18456 Severity 14 State 38. 

       What are your opinions?

       Servers are running under the same dedicated domain account, also valid for witness.

    Thank you very much in advance.


    • Edited by totaia Monday, December 17, 2012 10:14 AM
    Monday, December 17, 2012 9:43 AM

All replies

  • http://msdn.microsoft.com/en-us/library/ms190913.aspx

    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
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, December 17, 2012 10:18 AM
  •    Thank you Uri. Already checked... including timeout. Plus those are production servers, no latency no problems... they are Vmwares.

    Monday, December 17, 2012 10:21 AM
  • Hallo Totaia,

    did you check whether the service account of database engine has access to sql server?


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Monday, December 17, 2012 11:45 AM
  • Hallo Uwe,

       Database engine, agent and reporting services are all running under same domain account, a dedicated one. It is also sa, SQL server was installed under this account. Also, the error shows up only after failover, so db it is acting as the client would not see the mirror, equal not being able to switch to the mirror which became principle after failover.

    Thank you.




    • Edited by totaia Monday, December 17, 2012 10:49 PM
    Monday, December 17, 2012 10:01 PM
  • What does the application Connection string look like? And what does the errorlog say on the new principal?
    Tuesday, December 18, 2012 6:37 PM
  • Hello,

       12/19/2012 11:35:55,spid22s,Unknown,Database mirroring connection error 4 'An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'.' for 'TCP://FQDN_SERVER_MIRROR:5022'.
    12/19/2012 11:35:55,spid22s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.
    12/19/2012 11:35:55,spid10s,Unknown,Database mirroring is inactive for database 'DB'. This is an informational message only. No user action is required.
    12/19/2012 11:35:54,spid27s,Unknown,Database mirroring connection error 4 'An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15100)'.' for 'TCP://FQDN_SERVER_MIRROR:5022'.
    12/19/2012 11:35:54,spid27s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.

    String:

    Data Source=SERVER_MIRROR\Instance;Initial Catalog="DB";Integrated Security=SSPI

    Note: there are 2 apps with the same string and supposedly they generate those 2 errors before with 1 second apart.

    SERVER_MIRROR is the initial principle this is why string is pointing to it.

    Thank you for trying to help.

    Wednesday, December 19, 2012 12:47 AM
  • Also, these ones might be of help...

    Question: Could SPN would influence the behaviour?...

    12/19/2012 11:36:12,spid7s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
    12/19/2012 11:36:12,Logon,Unknown,Login failed for user 'DOM\COMP1$'. Reason: Failed to open the explicitly specified database. [CLIENT: 10.1.55.54]
    12/19/2012 11:36:12,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
    12/19/2012 11:36:12,spid16s,Unknown,Bypassing recovery for database 'database' because it is marked as a mirror database<c/> which cannot be recovered. This is an informational message only. No user action is required.
    12/19/2012 11:36:12,spid13s,Unknown,Service Broker manager has started.
    12/19/2012 11:36:11,spid13s,Unknown,The Database Mirroring protocol transport is now listening for connections.
    12/19/2012 11:36:11,spid13s,Unknown,Server is listening on [ 'any' <ipv4> 5022].
    12/19/2012 11:36:11,spid13s,Unknown,Server is listening on [ 'any' <ipv6> 5022].
    12/19/2012 11:36:11,spid13s,Unknown,The Service Broker protocol transport is disabled or not configured.
    12/19/2012 11:36:11,spid10s,Unknown,Starting up database 'tempdb'.
    12/19/2012 11:36:10,spid7s,Unknown,Recovery is writing a checkpoint in database 'database2' (8). This is an informational message only. No user action is required.
    12/19/2012 11:36:10,spid14s,Unknown,Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
    12/19/2012 11:36:10,spid15s,Unknown,Starting up database 'database2'.
    12/19/2012 11:36:10,spid10s,Unknown,Clearing tempdb database.
    12/19/2012 11:36:10,spid16s,Unknown,Database mirroring has been enabled on this instance of SQL Server.
    12/19/2012 11:36:10,spid16s,Unknown,Starting up database 'database1'.
    12/19/2012 11:36:10,spid15s,Unknown,Starting up database 'ReportServer$DBTempDB'.
    12/19/2012 11:36:10,spid14s,Unknown,Starting up database 'ReportServer$DB'.
    12/19/2012 11:36:10,spid13s,Unknown,Starting up database 'msdb'.
    12/19/2012 11:36:10,spid13s,Unknown,A new instance of the full-text filter daemon host process has been successfully started.
    12/19/2012 11:36:09,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
    12/19/2012 11:36:09,Server,Unknown,The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098<c/> state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

    The problems are with database1... that one I was mirroring...

    Thank you.

    Wednesday, December 19, 2012 9:28 AM
  • You are missing an Attribute in the conneciton string: FailoverPartner=MirrorServer\MirrorInstance...

    That would explain your Problem.

    Wednesday, December 19, 2012 3:23 PM
  • Hello there,

      in BOL for SQL it says that if the connection is successful on the first attempt, on the first scenario: principle-mirror, SQL will update the string for that connection with mirror's string. So, there would not be any need for specifying a connection string for the app.

       Even so, I have tried with all 3 options to specify the connection string as:

    Failover Partner

    Failover_Partner

    FailoverPartner

    depending on the provider. It did not work.

    Thank you again for your help and still working on that... what is your opinion about SPN?

    Wednesday, December 19, 2012 10:25 PM
  • When the FailoverPartner is in the string you should at least see an error message in the Secondary Servers errorlog. (Especially if you have an SPN issue...)

    The key message I am giving you is: As long as you don't see any message in the log of the mirror server chances are high that the client doesn't even try to connect there.

    And I don't know where in BOL it says that it will update the string, but I know for a fact that it wont. If the keyword is not in the connection string you are doomed.

    Thursday, December 20, 2012 3:57 PM
  • Thank you for your answer. It is indeed informative, about the error logs.

    The update of string is here: http://msdn.microsoft.com/en-us/library/ms366348.aspx .

    I am still trying to investigate what is happening with the application... it is not a simple application, in fact a client contacts an IIS application which passes on information which in turn contacts the database and make modifications.


    • Edited by totaia Friday, December 21, 2012 12:28 PM
    Friday, December 21, 2012 12:15 PM