Mirroring after restoring the database on a new machine

Question

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.

Answer 1

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.

Answer 2

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.