TIL: Database Mirroring, TDE, And Encryption

I recently had to build database mirroring for a database with Transparent Data Encryption enabled. Ahmad Yaseen had a very nice walkthrough which covered most of what I needed to know. There were a couple things I needed to do to get everything working, so I figured it was worth a blog post.

Set Up Certificates

The first thing I had to do was set up a master key and certificate on my primary instance:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some Master Key Password';
go
CREATE CERTIFICATE TDECertificate WITH SUBJECT = Some TDE Certificate';
go

Then we need to turn encryption on for the database, which is a two-step process:

USE [SomeDatabase]
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECertificate;
GO
ALTER DATABASE [SomeDatabase] SET ENCRYPTION ON;

At this point, TDE is on for the primary instance.

Backups

Now it’s time to take some backups. First, let’s back up the various keys and certificates:

USE [master]
GO
--Back up the service master key
--Note that the password here is the FILE password and not the KEY password!
BACKUP SERVICE MASTER KEY TO FILE = 'C:\Temp\ServiceMasterKey.key' ENCRYPTION BY PASSWORD = 'Service Master Key Password';
GO
--Back up the database master key
--Again, the password here is the FILE password and not the KEY password.
BACKUP MASTER KEY TO FILE = 'C:\Temp\DatabaseMasterKey.key' ENCRYPTION BY PASSWORD = 'Database Master Key Password';
GO
--Back up the TDE certificate we created.
--We could create a private key with password here as well.
BACKUP CERTIFICATE [TDECertificate] TO FILE = 'C:\Temp\TDECertificate.cert'
	WITH PRIVATE KEY (FILE = 'C:\Temp\TDECertificatePrivateKey.key', ENCRYPTION BY PASSWORD = 'Some Private Key Password');
GO

Then we want to take a database backup and log file backup. I’ll let you take care of that part.

Now I want to get mirroring set up.

Mirror Certificates

On the mirror instance, let’s restore the various certificates. I’m assuming that this is a true mirroring instance and that you haven’t created any keys. I also moved the keys, certificates, and backups over to the mirroring instance’s C:\Temp folder.

USE [master]
GO
--Test restoration of the keys and certificate.
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\Temp\ServiceMasterKey.key' DECRYPTION BY PASSWORD = 'Service Master Key Password';
GO
--For the master key, we need to use the file decription and then the original password used for key encryption.  Otherwise,
--your restoration attempt will fail.
RESTORE MASTER KEY FROM FILE = 'C:\Temp\DatabaseMasterKey.key'
	DECRYPTION BY PASSWORD = 'Database Master Key Password'
	ENCRYPTION BY PASSWORD = 'Some Master Key Password' FORCE;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Some Master Key Password';
GO
CREATE CERTIFICATE [TDECertificate] FROM FILE = 'C:\Temp\TDECertificate.cert'
WITH PRIVATE KEY (FILE = 'C:\Temp\TDECertificatePrivateKey.key', DECRYPTION BY PASSWORD = 'Some Private Key Password');
GO

I needed to use the FORCE directive when restoring the master key. Otherwise, this part went smoothly.

Database And Log Restoration

Before restoring the database files, I needed to open the master key file.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Some Master Key Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
RESTORE DATABASE [SomeDatabase] FROM DISK = 'C:\Temp\SomeDatabase.bak' WITH NORECOVERY, REPLACE;
RESTORE LOG [SomeDatabase] FROM DISK = 'C:\Temp\SomeDatabase.trn' WITH NORECOVERY, REPLACE;

Now we have the mirror database in Restoring mode, and it’s using our service master key, so TDE is ready to work on this database as well.

From there, I was able to finish setting up mirroring. Note that I didn’t need to do anything special to the witness server–it doesn’t need any certificates or keys to do its job.

Additional Links

In addition to Ahmad’s post, I read though this post on the topic. I also read Don Castelino’s post on mirroring configuration failure scenarios. I ran into a couple of these errors, so it was a helpful article.

Finally, I want to leave this Simon McAuliffe post on the uselessness of Transparent Data Encryption. I’m not sure I’m 100% in agreement, but it’s a thought-provoking post.