Setting up database mirroring with certificates

We use certificates while setting up database mirroring for two partners that are in a workgroup or in non-trusted domains. The idea is to create a certificate on each partner, export it to the other and then setup a login to use that certificate. As explained in BOL here, this is called setting up Inbound and Outbound connections

Here is a simplified representation of how it needs to be setup

If either of these is not setup correctly you can get a variety of error messages like these

Msg 1431, Level 16, State 4, Line 1

Neither the partner nor the witness server instance for database "TEST2" is available. Reissue the command when at least one of the instances becomes available.

Error: 1438, Severity: 16, State: 1.

The server instance Partner rejected configure request; read its error log file for more information. The reason 1405, and state 2, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.

Error: 1405, Severity: 16, State: 2

Apart from the blog post above, you can refer to Bemis 2189705 for a step by step approach to setting up database mirroring with certificates. The steps consist of the following

1. Setup Outbound connections:- Consists of creating the certificate, the endpoint ( with the certificate in the AUTHENTICATION clause) and then backing up the certificate

2. Setup Inbound connections:- Consists of restoring the certificate from the partner, associating it with a login and granting that login connect on the endpoint

3. Run the ALTER DATABASE statements starting with Mirror server first and then on Principal

In order to avoid confusion, be sure to use two separate local accounts on each partner and name them with the prefix of the machine name for the other partner.

Also, we shall see these error messages while trying to use the GUI to setup database mirroring on Sql Server 2005 as outlined in https://connect.microsoft.com/SQLServer/feedback/details/343027/database-mirroring-gui-does-not-work-and-throws-fqdn-error - which means setting up mirroring using certificates through SSMS is not possible on Yukon SSMS but is possible for Katmai and above.

Example Scripts that you can use in your environment

--1.Setup Outbound connections:- Consists of creating the certificate, the endpoint ( with the certificate in the AUTHENTICATION clause)

--and then backing up the certificate

USE master

CREATE CERTIFICATE HOST_PRINCIPAL_cert

WITH SUBJECT = 'HOST_PRINCIPAL certificate',

START_DATE = '08/19/2011'

GO

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT=5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE HOST_PRINCIPAL_cert

, ROLE = ALL);

GO

BACKUP CERTIFICATE HOST_PRINCIPAL_cert TO FILE = 'C:\temp\HOST_PRINCIPAL_cert.cer';

GO

--2. Copy this ceritificate to the Mirror machine

--3.Setup Outbound connections:- Consists of creating the certificate, the endpoint ( with the certificate in the AUTHENTICATION clause)

--and then backing up the certificate

CREATE CERTIFICATE HOST_MIRROR_cert

WITH SUBJECT = 'HOST_MIRROR_certificate',

START_DATE ='08/19/2011'

GO

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE HOST_MIRROR_cert

, ROLE = ALL

);

GO

BACKUP CERTIFICATE HOST_MIRROR_cert TO FILE = 'C:\temp\HOST_MIRROR_cert.cer';

GO

--4. Copy this certificate to the Principal machine

--5.Setup Inbound connections:- Consists of restoring the certificate from the partner, associating it with a login and granting that login connect

--on the endpoint

--Create the login for the Mirror machine and associate the mirror cert with the login

USE master;

CREATE LOGIN HOST_MIRROR_login WITH PASSWORD = '1Sample_Strong_Password!@#';

GO

CREATE USER HOST_MIRROR_user FOR LOGIN HOST_MIRROR_login;

GO

--Associate the certificate with the user.

CREATE CERTIFICATE HOST_MIRROR_cert

AUTHORIZATION HOST_MIRROR_user

FROM FILE = 'C:\temp\HOST_MIRROR_cert.cer'

GO

--Grant connect on the endpoint to the login

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_MIRROR_login];

GO

--6.Setup Inbound connections:- Consists of restoring the certificate from the partner, associating it with a login and granting that login connect

--on the endpoint

--Create the login for the Principal machine and associate the principal cert with the login

USE master;

CREATE LOGIN HOST_PRINCIPAL_login WITH PASSWORD = '=Sample#2_Strong_Password2';

GO

CREATE USER HOST_PRINCIPAL_user FOR LOGIN HOST_PRINCIPAL_login;

GO

--Associate the certificate with the user.

CREATE CERTIFICATE HOST_PRINCIPAL_cert

AUTHORIZATION HOST_PRINCIPAL_user

FROM FILE = 'C:\temp\HOST_PRINCIPAL_cert.cer'

GO

--Grant connect on the endpoint to the login

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_PRINCIPAL_login];

GO

Here is a list of common troubleshooting steps to do when you are stuck with these error messages

1. Telnet to the ports works correctly from each machine to the other

2. Output of netstat –abn shows that ports are opened on both sides as expected

Principal

TCP 0.0.0.0:5022 0.0.0.0:0 LISTENING 5068

  [sqlservr.exe]

Mirror

  TCP 0.0.0.0:5023 0.0.0.0:0 LISTENING 5052

  [sqlservr.exe]

4. Check the output of sys.database_mirroring_endpoints is identical on both sides

End_Mirroring 65545 273 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 3 ALL 1 4 CERTIFICATE 258 1 RC4

End_Mirroring 65545 273 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 3 ALL 1 4 CERTIFICATE 258 1 RC4

5. Run the Metadata check query from BOL and check that the respective logins have correct CONNECT permissions on each partner

SELECT 'Metadata Check';

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

6. Try removing the ENCRYPTION clause from the endpoint definition and recreating it. This is relevant when the Sql Server is hosted on virtual machine in Vmware environment.

7. Try dropping and recreating the local account on each side that is being used for DBM. This will only be required if they are using local windows accounts and they have moved the database from one server to another with the same local account existing on both the machines or if they have moved to another domain where the same domain user exists as the previous domain. This is similar to the orphaned logins issue with SQL Authentication for Database Mirroring

Rohit Nayak

Sr. Support Engineer – Sql Server CTS