SQL SERVER – How Enable Transparent Database Encryption (TDE) for Database Mirroring?

Life can teach you interesting lessons and push you beyond the boundary of what we call as a comfort zone. Recently I had a customer who was working on SQL server 2008 R2 and was enabling Mirroring. The very first thought was to recommend them to use AlwaysOn but that was not in their current scope because the application that they were working with was supported with SQL Server 2008 R2. The next thought for me was to understand what they were looking for as part of this consulting call. They incidentally wanted to enable Transparent Database Encryption (TDE) on their server and wanted me to guide on how to enable the same because the ISV had not done this before with a Database Mirroring configuration. So they were taking my help to understand how this needs to be systematically done with minimal downtime.

This got me thinking because it has been a while I touched this subject, but was worth a revisit to my fundamentals of security related topics. After a couple of minutes, I got the drift and had suggested the following steps. When using SQL Server Database Mirroring, there are a few steps that need be implemented before enabling mirroring all the way.

The first step involves creating a Master Key on the Mirror server.

USE master;
GO;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password #1';
GO

The password doesn’t have to be the same as the one of the master key for the principal instance. This key is used to encrypt the certificate that will be created in the third step.

Second step is to backup the certificate on the principle. To backup the certificate used for encryption. The command would be:

USE master;
GO;
BACKUP CERTIFICATE MyPrinServerCert TO FILE = 'path_and_file_name.cer'
WITH PRIVATE KEY ( FILE = 'path_and_file_name.pvk' ,
ENCRYPTION BY PASSWORD = 'your strong password #2' );
GO

The third step is to copy the certificate file and the key file to the mirror server and then create a certificate using these two files. The command would be:

USE master;
GO;
CREATE CERTIFICATE MyPrinServerCert
FROM FILE = 'path_and_file_name.cer'
WITH PRIVATE KEY (FILE = 'path_and_file_name.pvk',
DECRYPTION BY PASSWORD = 'your strong password #2');
GO;

The password provided in this step has to match the password provided when backing up the certificate on the principle server.

Note: There is no direct Restore Certificate command. We have to use the Create Certificate command.

Similar steps apply in case of log-shipping / AlwaysOn as well. One important thing to keep in mind is that as and when the certificate used to encrypt the DEK of the Log-Shipped database changes, that certificate needs to be restored to the secondary server as well in order for the restore to be successful.

Since these steps are generic and can be used in other places too, I took the script and used it as part of the blog post. Do let me know if you ever used similar scripts in your environments while working with TDE inside SQL Server?

Reference: Pinal Dave (https://blog.sqlauthority.com), Twitter