Matt Calvert's Blog

MSSQL Mirroring Using Certificate-Based Authentication

This is a step-by-step guide to setting up MS SQL Mirroring using database users and certificates for authentication, instead of the usual Windows-based authentication. This is useful for scenarios where not all the servers are (or can be) on the same domain.

This was put in place for one of our customers which used Domain-based authentication for database access from his applications, but the witness server was on a 3rd site, behind a shared firewall, and was not part of the domain. The only ports that were needed to be opened between the database servers and the witness was port 5022/TCP, which makes this setup quite secure

Principal Server: ABC-SQLSRV01
Mirroring Server: ABC-SQLSRV02
Witness Server: ABC-SQLWIT01

Step by Step

USE master
GO

SELECT * FROM sys.symmetric_keys
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='SQLSRV01-P@ssw0rd';
GO

CREATE CERTIFICATE "ABC-SQLSRV01_Cert"
WITH SUBJECT = 'DB Mirroring Certificate - ABC-SQLSRV01',
EXPIRY_DATE = '12/31/2033'
GO

SELECT * FROM sys.certificates;
GO

CREATE ENDPOINT DBM_CERT_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE "ABC-SQLSRV01_Cert",
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
)
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

BACKUP CERTIFICATE "ABC-SQLSRV01_Cert"
TO FILE = 'C:\Temp\ABC-SQLSRV01_Cert.cer'
GO
USE master
GO

SELECT * FROM sys.symmetric_keys
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='SQLSRV02-P@ssw0rd';
GO

CREATE CERTIFICATE "ABC-SQLSRV02_Cert"
WITH SUBJECT = 'DB Mirroring Certificate – ABC-SQLSRV02',
EXPIRY_DATE = '12/31/2033'
GO

SELECT * FROM sys.certificates;
GO

CREATE ENDPOINT DBM_CERT_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE "ABC-SQLSRV02_Cert",
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
)
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

BACKUP CERTIFICATE "ABC-SQLSRV02_Cert"
TO FILE = 'C:\Temp\ABC-SQLSRV02_Cert.cer'
GO
USE master
GO
SELECT * FROM sys.symmetric_keys
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='SQLWIT01-P@ssw0rd';
GO

CREATE CERTIFICATE "ABC-SQLWIT01_Cert"
WITH SUBJECT = 'DB Mirroring Certificate – ABC-SQLWIT01',
EXPIRY_DATE = '12/31/2033'
GO

SELECT * FROM sys.certificates;
GO

CREATE ENDPOINT DBM_CERT_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE "ABC-SQLWIT01_Cert",
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = WITNESS
)
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

BACKUP CERTIFICATE "ABC-SQLWIT01_Cert"
TO FILE = 'C:\Temp\ABC-SQLWIT01_Cert.cer'
GO

Copy Certificates to the other SQL Servers

CREATE LOGIN "ABC-SQLSRV02_login" WITH PASSWORD = 'SQLSRV02_login-P@ssw0rd', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO

CREATE USER "ABC-SQLSRV02_User" FOR LOGIN "ABC-SQLSRV02_login";
GO

CREATE CERTIFICATE "ABC-SQLSRV02_Cert"
AUTHORIZATION "ABC-SQLSRV02_User"
FROM FILE = 'c:\Temp\ABC-SQLSRV02_Cert.cer'
GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [ABC-SQLSRV02_login]
GO


CREATE LOGIN "ABC-SQLWIT01_login" WITH PASSWORD = 'SQLWIT01_login-P@ssw0rd', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO

CREATE USER "ABC-SQLWIT01_User" FOR LOGIN "ABC-SQLWIT01_login";
GO

CREATE CERTIFICATE "ABC-SQLWIT01_Cert"
AUTHORIZATION "ABC-SQLWIT01_User"
FROM FILE = 'c:\Temp\ABC-SQLWIT01_Cert.cer'
GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [ABC-SQLWIT01_login]
GO
CREATE LOGIN "ABC-SQLSRV01_login" WITH PASSWORD = 'SQLSRV01_login-P@ssw0rd', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO

CREATE USER "ABC-SQLSRV01_User" FOR LOGIN "ABC-SQLSRV01_login";
GO

CREATE CERTIFICATE "ABC-SQLSRV01_Cert"
AUTHORIZATION "ABC-SQLSRV01_User"
FROM FILE = 'c:\Temp\ABC-SQLSRV01_Cert.cer'
GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [ABC-SQLSRV01_login]
GO


CREATE LOGIN "ABC-SQLWIT01_login" WITH PASSWORD = 'SQLWIT01_login-P@ssw0rd', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO

CREATE USER "ABC-SQLWIT01_User" FOR LOGIN "ABC-SQLWIT01_login";
GO

CREATE CERTIFICATE "ABC-SQLWIT01_Cert"
AUTHORIZATION "ABC-SQLWIT01_User"
FROM FILE = 'c:\Temp\ABC-SQLWIT01_Cert.cer'
GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [ABC-SQLWIT01_login]
GO
CREATE LOGIN "ABC-SQLSRV01_login" WITH PASSWORD = 'SQLSRV01_login-P@ssw0rd', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO

CREATE USER "ABC-SQLSRV01_User" FOR LOGIN "ABC-SQLSRV01_login";
GO

CREATE CERTIFICATE "ABC-SQLSRV01_Cert"
AUTHORIZATION "ABC-SQLSRV01_User"
FROM FILE = 'c:\Temp\ABC-SQLSRV01_Cert.cer'
GO

CREATE LOGIN "ABC-SQLSRV02_login" WITH PASSWORD = 'SQLSRV02_login-P@ssw0rd', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO

CREATE USER "ABC-SQLSRV02_User" FOR LOGIN "ABC-SQLSRV02_login";
GO

CREATE CERTIFICATE "ABC-SQLSRV02_Cert"
AUTHORIZATION "ABC-SQLSRV02_User"
FROM FILE = 'c:\Temp\ABC-SQLSRV02_Cert.cer'
GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [ABC-SQLSRV01_login]
GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [ABC-SQLSRV02_login]
GO

Prepare Mirroring Database

USE master
GO

BACKUP DATABASE "UKFast-MirrorTest"
TO DISK = 'C:\Temp\UKFast-MirrorTest_FullBackup.bak'
GO

BACKUP LOG "UKFast-MirrorTest"
TO DISK = 'C:\Temp\UKFast-MirrorTest_LogBackup.trn'
GO
USE master
GO

RESTORE DATABASE "UKFast-MirrorTest"
FROM DISK = 'C:\Temp\UKFast-MirrorTest_FullBackup.bak'
WITH NORECOVERY
GO

RESTORE LOG "UKFast-MirrorTest"
FROM DISK = 'C:\Temp\UKFast-MirrorTest_LogBackup.trn'
WITH NORECOVERY
GO

Enable Database Mirroring

ALTER DATABASE "UKFast-MirrorTest"
SET PARTNER = 'TCP://ABC-SQLSRV01:5022'
ALTER DATABASE "UKFast-MirrorTest"
SET PARTNER = 'TCP://ABC-SQLSRV02:5022'

ALTER DATABASE "UKFast-MirrorTest"
SET WITNESS = 'TCP://ABC-SQLWIT01:5022'