Executive Summary

Transparent Data Encryption (TDE) encrypts SQL Server database files at rest – including data files, log files, and backups – using a database encryption key (DEK) protected by a certificate stored in the master database. Adding TDE to a database that is part of an AlwaysOn Availability Group requires an additional step: the TDE certificate must be backed up and restored to every secondary replica’s master database before the secondary can decrypt and apply the encrypted log stream. This article walks through the complete process for SQL Server 2012: creating the master key, creating the TDE certificate, encrypting the database, backing up the certificate and private key, and restoring them to each secondary replica.

Previously, in my article SQL Server 2012 AlwaysOn, I discussed the components that make up SQL Server 2012’s AlwaysOn and how you can configure your SQL Server environment using AlwaysOn Availability Groups (AAG) to meet the ever-increasing need for ‘High Availability’ (HA) and ‘Disaster Recovery’ (DR). One of the benefits that I outlined was the ability to use the Enterprise Edition feature ‘Transparent Data Encryption’ (TDE) to secure your databases.

Transparent Data Encryption

TDE allows you to protect your databases by performing real-time I/O encryption utilising keys. This prevents anyone who does not have these keys from accessing the data. I am not going to dive into the details of TDE in this article; there are links in the References & Further Reading section later in the article for you to get a better understanding of TDE.

Configuring your AlwaysOn environment

If you have not yet set up and configured a SQL Server 2012 AlwaysOn environment, then please have a read of my article SQL Server AlwaysOn. In this article I shall assume that you already have an environment set up that is already using AlwaysOn Availability Groups (AAG). Normally, if you are going to create a new AAG or need to add a database into an existing AAG, you can simply use the wizards provided in SSMS. However, if you are going to add a database that has been configured to be encrypted using TDE then you will not be able to use these wizards.

Whether you are creating a new AAG, or you are needing to add a database that has been encrypted using TDE, there are steps that you need to complete on your SQL Server 2012 AlwaysOn Primary Replica. These are:

• Create a Master Key on the Primary Replica
• Backup the Master Key
• Create a Certificate protected by the Master Key
• Backup the Certificate
• Create a Database Encryption Key
• Enable a TDE on a Database

Here is some example code that can be used to perform these tasks:

We need to undertake some configuration steps on the Secondary Replicas in order to allow us to be able to replicate the Availability Databases. We need to:

• Create a Master Key on the Secondary Replica
• Backup the Master Key
• Create Certificate from the Primary Replica

Here is some example code that can be used to perform these tasks:

The above will need to be undertaken on every Secondary Replica in the SQL Server 2012 AlwaysOn environment. After the Replicas are all configured with the Certificate, the encrypted database(s) can then be made available on all of the Replicas.

When we are using the ‘New Availability Group Wizard’, we have to select those database(s) which will participate in the AlwaysOn Availability Group. If one of the databases has been enabled for encryption utilising TDE, we will not be able to use the wizard to create the AlwaysOn Availability Group. To achieve this, we need to use T-SQL or PowerShell to create the Availability Group.

This T-SQL Code (to run in SQLCMD mode in SSMS) will manually create an Availability Group:

— The following code needs to be run using SQLCMD Mode

Alternatively if you prefer to use PowerShell here is some code to manually create an Availability Group:

If we already have an AAG, and we need to add a database that has been configured for encryption, then a slight modification to the T-SQL code above will allow us to achieve this. Swap Steps 1 & 2 in the T-SQL Code above for the code below:

We have now configured the environment to have an Encrypted database participate in a SQL Server 2012 AlwaysOn Availability Group.

References & Further Reading

• Transparent Data Encryption – http://msdn.microsoft.com/en-us/library/bb934049.aspx
• Create Master Key – http://technet.microsoft.com/en-us/library/ms174382.aspx
• Create Certificates – http://msdn.microsoft.com/en-us/library/ms187798.aspx
• Create Database Master Key – http://technet.microsoft.com/en-us/library/aa337551.aspx
• Move a TDE protected Database – http://technet.microsoft.com/en-us/library/ff773063.aspx
• Backup Master Keys – http://technet.microsoft.com/en-us/library/ms174387.aspx
• Backup Certificates – http://msdn.microsoft.com/en-us/library/ms178578.aspx
• Manually Prepare a DB for for AAG’s – http://msdn.microsoft.com/en-us/library/ff878349.aspx
• Encrypted Databases with AAG’s – http://msdn.microsoft.com/en-us/library/hh510178.aspx
• Create an AAG with PowerShell – http://msdn.microsoft.com/en-us/library/gg492181.aspx