Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We've had alot of problems with one of our database servers and two days ago the call was made to change a disk and reinstall the OS. Now this server functioned as part of a mirroring setup. Basically after the OS and SQL we're reinstalled i restored the Master database hoping mirroring would continue without causing my headpains.

I should state i'm not a DBA but i try to make due with the knowledge i have.

At the moment the server is giving the following errors;

Please create a master key in the database or open the master key in the session before performing this operation.

Followed by the following Information message;

Database Mirroring login attempt failed with error: 'Connection handshake failed.
Error 15581 occurred while initializing the private key corresponding to the certificate.
The SQL Server errorlog and the Windows event log may contain entries related to this error. State 88.'.  [CLIENT: XXX.XXX.XXX.92]

We've setup mirroring using Certifcates, this is because the servers do not run in the same domain. Now from googling i should have apperently made a backup of the master key (sadly i didn't even know SQL had a master key so i didn't).

What suprises me is that i can actually backup the certificate, i can login to the SQL Server using different credentials so it does seem to be able to encrypt/decrypt somethings (unless these things are not Master Key related).

Basically i'm somewhat at a deadend, how do i resolve these problems? Should i completely reinstall the mirroring? Is this at all recoverable? Any adivce and pointers in the right direction would be very much appreciated.

Answer (ill post as the answer later, need to wait 8 hours :( )

Bit fast for me answering my own question, but basically what i did was the following:

On the "restored" server

  • Remove the Endpoint
  • Remove the All Mirror related users, logins & certifactes
  • Create a new certificate and setup an endpoint
  • Create inbound access for the Witness and Principal

On the Witness and Principal;

  • Remove the user, login & certificate of "restored" server
  • Create a new user, login with the newly generated certificate from the restored server.

After this my mirroring started to Synchronize.

share|improve this question

migrated from serverfault.com Jan 12 '12 at 11:45

This question came from our site for professional system and network administrators.

add comment

2 Answers 2

up vote 2 down vote accepted

Certificate based authentication of Database Mirroring is explained in How Does Certificate based Authentication Work. After your server rebuild you should had open the master database database master key (hic!) using OPEN MASTER KEY ENCRYPTION BY PASSWORD=... and then add the new server service master key encryption to it using ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY. Then DBM would had access to the endpoint certificate private key and would successfully authenticate with the partner.

If you do not have the original master database master key password (shame!) then you need to force a new one, which may loose the DBM endpoint certificate private key encryption. If you also have encrypted the DBM endpoint certificate with a password then you can open it using that password and then add the new master key encryption to it, afterwards the DBM will successfully connect.

If you cannot recover the original DBM endpoint certificate private key then you need to replace it. You do not have to drop the endpoint, nor the mirroring related users and certificates. You can simply create a new certificate, then associate the new certificate with the existing endpoint using ALTER ENDPOINT ... FOR DATBASE_MIRRORING (CERTIFICATE = ...). On the partner(s) (including the Witness) you need to copy the public key of the certificate, create a new certificate for it in master and have it be owned by the user associated with the previous server certificate/user/login. This is the least disruptive way.

What you did is ultimately correct, but kinda of using the sledgehammer. Given the complexity of all the steps, I don't blame you for redoing everything from scratch. See also Replacing Endpoint Certificates that are near expiration.

share|improve this answer
    
Thanks for answering, i'll mark it as the answer since it's more complete then what i did. In all honesty i didn't know i could simply replace the Certificate the way you explained. Something to remember should this ever happen to me again :) –  F.B. ten Kate Jan 13 '12 at 8:10
add comment

Which part of the mirror was lost ? If it was the primary, it should have failed over to the mirror (which then becomes the Primary.)

Database mirroring, like replication, is non-destructive; you can always throw it away and start from scratch.

Make a full backup of your Primary database, and restore it to the mirror WITH NO RECOVERY. Then re-establish the mirror instance.

share|improve this answer
    
Well, the principal failed, everything switched over the the mirror fine. Problem is i cant just remove mirroring, since atm all our backoffices are connected to the Failover Partner which needs to have mirroring enabled otherwise you get some (imo silly) error stating it doesn't have a failover partner.... I've managed to solve the problem though, sadly can't answer my own question yet so i'll edit it in for the time beeing. –  F.B. ten Kate Jan 12 '12 at 9:44
    
Yeah.. you didn't really provide this information before :) –  adaptr Jan 12 '12 at 9:46
    
True, i have to be honest i was also expecting alot more trouble fixing this then i had. Gues i got lucky :) –  F.B. ten Kate Jan 12 '12 at 10:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.