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.