Sql-server – Mirroring after restoring the database on a new machine

mirroringsql serversql-server-2005

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.

Best Answer

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.

Related Solutions

Sql-server – Database Mirroring with TDE

Found a website with a comment on it.

I added the code to just after where I restore the key and cert

--Mumbojumbo to get mirroring to work
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

It works like a charm, it makes a little sense that I had to encrypt the master key that I restored with the service master key of the new server. I guess.

shrug

Sql-server – Mirroring – Server network address cannot be reached

Try some basic connectivity tests.

Verify that 5022, 5023 and 5024 are listening.
Verify that the server name you are using is correct.

From the command line :

netstat -an

enter image description here

On my server, you can see that 5022 is listening.

Next make sure that you can connect to those ports via telnet

telnet fully-qualified-server-name 5022

As it mentions in the Note section of the Mirroring Properties GUI, just below the witness field, the server names have to be fully qualified tcp addresses.

enter image description here

You should just see a black screen. In this example I chose a name that would cause a connection failure. If you see "Could not open connection", then the server(s) defined as mirror, principal and witness aren't reachable or you are not using the right name.

The telnet client can be added under Features in Windows 2008.

In Windows 2008, when you right click on Computer, you can see the full computer name. You should be able to ping it as well from the command line. ex: ping myservername

Update

Please run the following queries on each SQL Server instance and put the results in your question. Many of these troubleshooting tips come from: http://msdn.microsoft.com/en-us/library/ms189127.aspx

Show tcp endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;

Display status of mirroring endpoints

SELECT state_desc FROM sys.database_mirroring_endpoints;

Check that the ROLE is correct

SELECT role FROM sys.database_mirroring_endpoints;

Display permissions to endpoints

SELECT EP.name, SP.STATE, 
   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) 
      AS GRANTOR, 
   SP.TYPE AS PERMISSION,
   CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) 
      AS GRANTEE 
   FROM sys.server_permissions SP , sys.endpoints EP
   WHERE SP.major_id = EP.endpoint_id
   ORDER BY Permission,grantor, grantee; 
GO

The login for the service account from the other server instance requires CONNECT permission. Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQL statement.

Example output:

name    STATE   GRANTOR PERMISSION  GRANTEE
TSQL Local Machine  G   sqladmin    CO      public
TSQL Named Pipes    G   sqladmin    CO      public
TSQL Default TCP    G   sqladmin    CO      public
TSQL Default VIA    G   sqladmin    CO      public
Mirroring   G   SERVERNAME\Grantor  CO      SERVERNAME\Grantee

Grantor is the account that assigned (CO) connect permission, Grantee is the account that has connect permission

From the command line run ipconfig /all and note what Host Name returns.

Best Answer

Related Solutions

Sql-server – Database Mirroring with TDE

Sql-server – Mirroring – Server network address cannot be reached

Related Question