SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 SQL 2k8 R2 Mirroring : Unable to access Mirror...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

claumarius
Starting Member

1 Posts

Posted - 12/17/2012 :  04:52:00  Show Profile  Reply with Quote
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 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.

Completion, could below be useful?

After failover, on the mirror that became principle:

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

and the logs related to SPN on principle, after failover:

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.

Edited by - claumarius on 12/19/2012 04:33:08
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000