Configure a SQL Server Database using Transparent Data Encryption and Database Mirroring

Problem

I have read this tip on TDE, Configuring Transparent Data Encryption with SQL Server 2012 AlwaysOn Availability Groups and this tip on database mirroring, Configure SQL Server Database Mirroring Using SSMS. Is it possible to enable TDE for a database and then configure database mirroring?

Solution

In our example, we have two SQL Server instances: SQL2016 and SQL2016MIRROR. We will enable TDE on a database and configure database mirroring for this database using certificates.

Before we get started, if you need a refresher on TDE refer to these useful tips first:

Here are the basic steps to follow to enable TDE (Transparent Data Encryption) for a database:

Create a master key
Create or obtain a certificate protected by the master key
Create a database encryption key and protect it by the certificate
Set the database to use encryption

Here are the basic steps to follow to enable database mirroring:

Create endpoints
Backup database and log
Restore database and log
Enable database mirroring

Step 1

The first step is to create the master key on the principal instance. Run the below script on the principal instance SQL2016. This step creates a master key, a mirroring certificate and the final step creates a backup of the certificate.

--STEP 1 TO CREATE MASTER KEY ON SQL2016 INSTANCE
Use master 
GO
 
Create Master Key Encryption By Password = 'C09ml_K3Y_PW23J!ly@2vc'
GO
-- ONCE MASTER KEY IS CREATED, BACKUP THE MASTER KEY
-- CREATE APPROPRIATE FOLDER LOCATION ON THE SERVER
BACKUP MASTER KEY TO FILE = 'D:\DO_NOT_DELETE\masterkeybackup' 
   ENCRYPTION BY PASSWORD = 'C09ml_K3Y_PW23J!ly@2vc'
-- ONCE MASTER KEY IS CREATED, CREATE A CERTIFICATE
Create Certificate primary_cert
With Subject ='primary_cert'
GO
-- ONCE CERTIFICATE IS CREATED, CREATE MIRRORING END POINT
Create Endpoint Mirroring_Endpoint_SQL2016
State = Started
AS TCP (Listener_Port = 5022, Listener_IP = ALL) 
For Database_Mirroring
(
Authentication = Certificate primary_cert, 
Role = ALL
)
GO
--BACKUP THE MIRRORING CERTIFICATE ONCE MIRRORING ENDPOINT IS CREATED
Backup Certificate primary_cert
To File ='D:\DO_NOT_DELETE\primary_cert.cer' 
GO

Step 2

Run the below script on the mirror instance SQL2016MIRROR. The step sequence is similar to what was done in step 1 for the principal server.

--STEP 2 TO CREATE MASTER KEY ON SQL2016MIRROR INSTANCE
Use master 
GO
 
--Password need not be same as used in Step 1
Create Master Key Encryption By Password = 'C09ml_K3Y_PW23J!ly@2vc'
GO
-- ONCE MASTER KEY IS CREATED, BACKUP THE MASTER KEY
-- CREATE APPROPRIATE FOLDER LOCATION ON THE SERVER
BACKUP MASTER KEY TO FILE = 'D:\DO_NOT_DELETE\masterkeybackup' 
   ENCRYPTION BY PASSWORD = 'C09ml_K3Y_PW23J!ly@2vc'
-- ONCE MASTER KEY IS CREATED ON MIRROR INSTANCE, CREATE A CERTIFICATE
Create Certificate mirroring_cert
With Subject ='mirroring_cert'
GO
-- ONCE CERTIFICATE IS CREATED, CREATE MIRRORING END POINT
Create Endpoint Mirroring_Endpoint_SQL2016
State = Started
AS TCP (Listener_Port = 5023, Listener_IP = ALL) 
For Database_Mirroring
(
Authentication = Certificate mirroring_cert, 
Role = ALL
)
GO
--BACKUP THE MIRRORING CERTIFICATE ONCE MIRRORING ENDPOINT IS CREATED
Backup Certificate mirroring_cert
To File ='D:\DO_NOT_DELETE\mirroring_cert.cer' 
GO

Before going to the next step, copy the backup certificates to each server. So the certificate created on SQL2016 should be copied to a folder on SQL2016MIRROR and the certificate created on SQL2016MIRROR should be copied to SQL2016. I placed these files in folder “D:\DO_NOT_DELETE\” on each server.

Step 3

Run the below script on principal server SQL2016.

--STEP 3
Use master 
GO
-- CREATE A SQL SERVER LOGIN ON SQL2016-principal server
Create Login mirror With Password ='!T#@Lgsnbs12'
GO
-- MAP A DATABASE USER TO THE NEW LOGIN
Create User mirror_user for Login mirror
GO
--ATTACH THE CERTIFICATE CREATED ON MIRROR INSTANCE SQL2016 MIRROR TO THE DATABASE USER
Create Certificate mirroring_cert
Authorization mirror_user
From File ='D:\DO_NOT_DELETE\mirroring_cert.cer' 
GO
-- GRANT PERMISSIONS TO THE MIRRORING ENDPOINT CREATED
Grant Connect on Endpoint::Mirroring_Endpoint_SQL2016 TO [mirror]
GO

Step 4

Run the below script on the mirror instance SQL2016MIRROR.

--STEP 4
Use master 
GO
-- CREATE A SQL SERVER LOGIN ON SQL2016MIRROR-mirror server
Create Login primary With Password ='!T#@Lgsnbs12'
GO
-- MAP A DATABASE USER TO THE NEW LOGIN
Create User primary_user for Login primary
GO
--ATTACH THE CERTIFICATE CREATED ON PRINCIPAL INSTANCE SQL2016 TO THE DATABASE USER
Create Certificate primary_cert
Authorization primary_user
From File ='D:\DO_NOT_DELETE\primary_cert.cer' 
GO
-- GRANT PERMISSIONS TO THE DATABASE MIRRORING ENDPOINT CREATED
Grant Connect on Endpoint::Mirroring_Endpoint_SQL2016 TO [primary]
GO

With the completion of steps 3 and 4, you are ready to configure database mirroring using certificates.

Step 5

Create a test database named TDE on the principal server SQL2016 and enable TDE for the database. Run the below steps on the primary server once the database is created.

--STEP 5
CREATE DATABASE TDE
GO
USE TDE
GO
--IN ORDER TO TEST TDE, CREATE CERTIFICATES
CREATE CERTIFICATE TDE_CERT WITH SUBJECT = 'TDE CERT'
GO
-- ONCE CERTIFICATE IS CREATED, BACKUP THE CERTIFICATE
BACKUP CERTIFICATE TDE_CERT 
TO FILE = 'D:\DO_NOT_DELETE\TDE_CERT_BAK.CER'
WITH PRIVATE KEY 
(
FILE = 'D:\DO_NOT_DELETE\TDE_CERT_PVT_KEY.PVK',
ENCRYPTION BY PASSWORD = 'T!i4asP2345W'
)
GO
-- CREATE A DATABASE ENCRYPTION KEY ON THE TEST DATABASE
USE TDE
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_CERT
GO
--ENABLE THE TEST DATABASE FOR TDE
ALTER DATABASE TDE
SET ENCRYPTION ON
GO 
-- ONCE TDE IS ENABLED ON THE DATABASE, PERFORM FULL BACKUP, FOLLOWED BY LOG FILE BACKUP
BACKUP DATABASE TDE TO DISK = 'D:\DO_NOT_DELETE\TDE.bak'
GO
BACKUP LOG TDE TO DISK='D:\DO_NOT_DELETE\TDE_LOG1.trn'
GO

Before going to next step, copy the certificate backup (TDE_CERT_BAK.CER) and private key (TDE_CERT_PVT_KEY.PVK) created in step 5 from the principal server SQL2016 to the mirror server SQL2016MIRROR and place it a folder (I used “D:\DO_NOT_DELETE”). Also, copy the full backup and the log file backup from the principal server to the mirror server.

Step 6

Run the below script on the mirror server: SQL2016MIRROR.

--STEP 6
--CREATE THE CERTIFICATE ON THE MIRROR SERVER, USE THE CERTIFICATES COPIED FROM PRINCIPAL
CREATE CERTIFICATE TDE_CERT
FROM FILE = 'D:\DO_NOT_DELETE\TDE_CERT_BAK.CER' 
WITH PRIVATE KEY (FILE = 'D:\DO_NOT_DELETE\TDE_CERT_PVT_KEY.PVK', 
DECRYPTION BY PASSWORD = 'T!i4asP2345W');
GO
-- RESTORE DATABASE USING THE FILES COPIED FROM PRINCIPAL SERVER
restore database TDE
from disk='D:\DO_NOT_DELETE\TDE.bak'
with move 'TDE' to 'C:\mirror2\tde.mdf', -- Provide appropriate drive location
move 'TDE_log' to 'C:\mirror2\tde_log.ldf',-- Provide appropriate drive location
replace, norecovery,stats
-- ONCE DATABASE IS SUCCESSFULLY RESTORE, RESTORE THE LOG BACKUP
restore log TDE from DISK='D:\DO_NOT_DELETE\TDE_LOG1.trn' with norecovery

The final step is to set up mirroring between the principal and mirror databases.

Step 7

Run the below script on the mirror database.

--STEP 7
--Run below step on the SQL2016MIRROR instance
alter database TDE set partner ='TCP://SQL2016:5022' --primary server endpoint
GO

Step 8

Run the below script on the principal database.

--STEP 8
--Run below step on the SQL2016 instance- Principal instance
alter database TDE set partner ='TCP://SQL2016MIRROR:5023' --mirror server endpoint
GO

With these steps, you have successfully configured TDE and enabled mirroring for the database using certificates.

Next Steps

Try testing this tip on your own lab environment
To learn more about encryption, refer to these useful tips: SQL Server Encryption Tips
To learn more about mirroring, refer to these useful tips: SQL Server Database Mirroring Tips

Resources

Community

Subscribe