Transparent data encryption for SQL server in an availability group

Posted in: Microsoft SQL Server, Technical Track

With the all new features in SQL Server 2016 always on, which you can read up on here, it’s easy to forget about Transparent Data Encryption (TDE). This blog post will focus on TDE.

TDE encrypts database files at rest. What this means is your .MDF and .NDF Files, and consequently your backups, will be encrypted, meaning you will not be able to detach the database files and restore them on another server unless that server has the same certificate that was used to encrypt the database.

In this blog post I am using SQL Server 2014 and will explain how to enable TDE on an existing AG Group database

  1. The first thing we need to check is if the server has a master encryption key on all replica in the AG Group
USE MASTER
GO
SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'

The Screenshot below shows I don’t have a key so I need to create one

No Master Encryption Key

  1. Create a Database Master Encryption Key on each of the replicas in the AG Group. It is important to use a complex password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'C&mpl£xP@$$Wrd'
GO
  1. Run the code in step 1 and this time you should see the below

Master Encryption Key

  1. Now we need to create a certificate to use for the encryption of the database on the primary replica. This can be accomplished by using the below
CREATE CERTIFICATE BackupEncryptionCert
WITH SUBJECT = 'SQL Server 2014 AdventureWorks2012 Encryption Certificate';
GO
  1. Validate the Certificate
SELECT name, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates

Validate Encryption Key

The thumbprint will be useful because when a database is encrypted, it will indicate the thumbprint of the certificate used to encrypt the Database Encryption Key.  A single certificate can be used to encrypt more than one Database Encryption Key, but there can also be many certificates on a server, so the thumbprint will identify which server certificate is needed

  1. Next We need to backup the certificate on the Primary Replica
BACKUP CERTIFICATE BackupEncryptionCert
TO FILE = 'C:\BackupCertificates\BackupEncryptionCert.bak'
WITH PRIVATE KEY ( FILE = 'C:\BackupCertificates\BackupEncryptionCertKey.bak' ,
ENCRYPTION BY PASSWORD = 'Certi%yC&mpl£xP@$$Wrd')

Encryption Files

The BACKUP CERTIFICATE command will create two files.  The first file is the server certificate itself.  The second file is a “private key” file, protected by a password. Both files and the password will be used to restore the certificate onto other instances.

  1. The Files created in step 6 needs to be copied to each of the other replicas and created in SQL Server. After the files are copied the below command can be used to create the certificates
CREATE CERTIFICATE BackupEncryptionCert
FROM FILE = 'C:\BackupCertificates\BackupEncryptionCert.bak'
WITH PRIVATE KEY (FILE = 'C:\BackupCertificates\BackupEncryptionCertKey.bak',
DECRYPTION BY PASSWORD = 'Certi%yC&mpl£xP@$$Wrd');
  1. That’s all the configuration needed for each instance now we are ready to start encrypting the database. We now need to tell SQL Server which Encryption Type we want to use and which certificate to use. This can be done using the following code on the Primary Replica
Use Adventureworks2012
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE BackupEncryptionCert
  1. Finally, the last step is to enable TDE by executing the below command on the Primary Replica
ALTER DATABASE AdventureWorks2012 SET ENCRYPTION ON

 

And that’s it, I hope you enjoyed this tutorial and found it informative. If you have any questions, please comment below.

email

Author

Interested in working with Daniel? Schedule a tech call.

About the Author

Project Architect
Highly experienced and skilled Solution Architect with extensive systems expertise. My recent career has allowed me to develop an outstanding track record in assisting with the implementation of new business solutions and demonstrate a strong trouble shooting ability meaning fewer and less frequent issues for a large-scale network. I have excellent experience in end to end solutions from setting up infrastructure to designing Relational Databases and Datawarehouse Databases with the ability to report write across a wide range of software/technologies.

1 Comment. Leave new

Valerie Parham-Thompson
May 4, 2016 2:19 pm

I liked seeing how data encryption at rest is implemented differently in SQL Server than it is in MySQL. Thank you for sharing the walkthrough!

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *