MSSQL Mirroring with Witness and TDE Encryption

MSSQL, TDE, Microsoft, T-SQL


This post will be about setting up a MSSQL Database Mirroring with Transparent Data Encryption enabled.

Before you begin do the preparation steps.

drop endpoint [name]
drop certificate [name]

use [database-name]
drop database encryption key

use [master]
drop master key

Also drop all test databases which you won't use.

The diagram below shows the DB infrastructure.

MSSQM Mirroring with TDE Encryption

In the Mirroring setup we use: NFSEC-DB-PROD - PRINCIPAL server NFSEC-DB-DR - MIRROR server NFSEC-AD-01 - WITNESS server

List of prerequisites: You need to have AD domain - nfsec.local
Your SQL instances in all location must be run as domain user - [NFSEC\sqlservice]

Follow these steps to establish mirroring on encrypted database with AES256 encryption and certificates

MSSQL - Preparing for TDE Encryption with Certificates

Step.1 - PRINCIPAL - NFSEC-DB-PROD

Below are the step to setup TDE encryption with certificates starting on the primary DB server.

Start a new SQL QUERY and run the following portion of code in sequence:

Create a MASTER KEY

USE MASTER  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke';  
GO

Create a CERTIFICATE

USE Master;  
GO  
CREATE CERTIFICATE principal_cert WITH SUBJECT = 'SQL TDE CERT'  
GO

Backup a MASTER KEY

USE master;  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke';  
BACKUP MASTER KEY TO FILE = 'C:\CERTS\principal_key.key'  
    ENCRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke';
GO

Backup a CERTIFICATE

USE master;  
BACKUP CERTIFICATE principal_cert TO FILE = 'C:\CERTS\principal_cert.cer'  
    WITH PRIVATE KEY ( FILE = 'C:\CERTS\principal_cert.key' ,
    ENCRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke');
GO

*Upload exported MASTER key and CERT and CERT KEY to Mirror Server NFSEC-DB-DR C:\CERTS*

Step.2 - MIRROR - NFSEC-DB-DR

Prepare the mirror server to use TDE with certificate.

Start a new SQL QUERY and run the following portion of code in sequence.

Restore a Master Key on the by-db-dr-01 server

USE MASTER;  
RESTORE MASTER KEY  
    FROM FILE = 'C:\CERTS\principal_key.key'
    DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'
    ENCRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke';
GO

Restore/Create a certificate on a mirror server using the principal certificate and its key

USE MASTER  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'  
CREATE CERTIFICATE mirror_cert  
  FROM FILE = 'C:\CERTS\principal_cert.cer'
  WITH PRIVATE KEY (FILE = 'C:\CERTS\principal_cert.key',
  DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke');
GO

Encrypt the master key that I restored with the service master key of the mirror server.

USE MASTER  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'  
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY  
GO

Step.3 - CREATE ENDPOINTS on ALL THREE SERVERS

PRINCIPAL

Create an endpoint on NFSEC-DB-PROD

CREATE ENDPOINT enc_endpoint  
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (
        ROLE = PARTNER,
        AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES
        );
GO  
GRANT CONNECT ON ENDPOINT::enc_endpoint TO [NFSEC\sqlservice];  
GO

MIRROR

Create an endpoint on NFSEC-DB-DR

CREATE ENDPOINT enc_endpoint  
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (
        ROLE = PARTNER,
        AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES
        );
GO  
GRANT CONNECT ON ENDPOINT::enc_endpoint TO [NFSEC\sqlservice];  
GO

WITNESS

Create an endpoint NFSEC-AD-01

CREATE ENDPOINT enc_endpoint  
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (
        ROLE = WITNESS,
        AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES
        );
GO  
GRANT CONNECT ON ENDPOINT::enc_endpoint TO [NFSEC\sqlservice];  
GO


MSSQL - Enabling DB Encryption and preparing for Mirroring

Step.1 - Enable DB encryption run SQL QUERY on the Principal server

Create DB Encryption Key using Certificate

USE [database-name]  
GO

CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256  
ENCRYPTION BY SERVER CERTIFICATE principal_cert  
GO

Turn Encryption On the database

USE master;  
GO  
ALTER DATABASE [database-name] SET ENCRYPTION ON  
GO

Step.2 - BACKUP encrypted DB on NFSEC-DB-PROD

BACKUP encrypted DB on NFSEC-DB-PROD

USE [master]  
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'  
BACKUP DATABASE [database-name] TO  DISK = N'C:\ProjectData\Backups\Migration\database-name.bak'  
WITH NOFORMAT, INIT, NAME = N'database-name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
CLOSE MASTER KEY  
GO

Step.3 - Copy the *.BAK file and restore on NFSEC-DB-DR

Restore an encrypted DB on NFSEC-DB-DR

--helper--
RESTORE FILELISTONLY FROM DISK = 'C:\ProjectData\Backups\Migration\database-name.bak' WITH FILE = 1  
--helper--

USE MASTER  
OPEN  
MASTER KEY DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'

RESTORE DATABASE [database-name]  
    FROM disk = 'C:\ProjectData\Backups\Migration\database-name.bak'
    WITH NORECOVERY, REPLACE, MOVE 'demo_dat' TO
    'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\database-name.mdf',
    REPLACE, MOVE 'demo_log' TO 
    'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\database-name.ldf'
CLOSE MASTER KEY  
GO


MSSQL - Setup Encrypted Mirroring for Encrypted DB’s

Step.1 - Firstly run the T-SQL script on the Mirror server.

Set PARTNER on the MIRROR

USE MASTER  
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'  
ALTER DATABASE [database-name] SET PARTNER = 'TCP://NFSEC-DB-PROD.nfsec.local:7022'  
ALTER DATABASE [database-name] SET PARTNER TIMEOUT 50  
CLOSE MASTER KEY  
GO

Step.2 - Secondly run the T-SQL on the Principal server

Set PARTNER and WITNESS on the PRINCIPAL

USE Master;  
GO  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'

-- add the Mirror as PARTNER for Principal
ALTER DATABASE [database-name] SET PARTNER = 'TCP://NFSEC-DB-DR.nfsec.local:7022'  
ALTER DATABASE [database-name] SET PARTNER TIMEOUT 50

-- add The Witness as WITNESS for Principal
ALTER DATABASE [database-name] SET WITNESS = 'TCP://NFSEC-AD-01.nfsec.local:7022'  
ALTER DATABASE [database-name] SET PARTNER TIMEOUT 50

CLOSE MASTER KEY  
GO

NOTE: You need to run the Step.1 first on Mirror and then Step.2 on Principal - otherwise mirroring won't start.

More about TDE can be found here: https://technet.microsoft.com/en-us/library/bb934049%28v=sql.110%29.aspx

Share this post:

by lo3k