Encrypting Your SQL Server 2012 AlwaysOn Availability Databases

It is likely that you'll want to add a database with TDS Encryption to your AlwaysOn Availability Group. If you do so you'll find that you can't use the SSMS wizard to do so. So, how do you achieve it? Read on...

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

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Tags: , , , , , , , , ,

  • 21919 views

  • Rate
    [Total: 8    Average: 4.3/5]
  • timothyawiseman@gmail.com

    Backup
    Excellent article, thank you.

    I would emphasize something you already said: Backup all relevant encryption keys and certificates. Not having a backup of the encryption keys and certificates would create complications in a disaster recovery scenario. Of course, the nature of a HA system can mitigate the possibility of loss of these backups a little bit, but that is no reason to neglect having backups of these items.

  • ksmani99

    Step-7 Errors Out /Unable to bring Replica to Synchronized State
    Hi Warwick,

    Following the steps mentioned, I get an error on step 7, as the database goes to Restoring mode (in my Sec Replica) after I execute Step 6 (Restore Log to Secondary Replica)

    Executing Step 7 throws
    "Database TestTDE_WithAlwaysOn does not exist" Make sure that the name is entered correctly. As the DB is in Restore mode, SQL is failing to read.

    Can you help me here?

    Thanks
    Subbu

  • SQLZEN

    Not the right way
    Hi Warwick,

    after several trials in my side, it seems to not working in this way.

    You must add the database in the Always On Availability Group before creating a Database Encryption Key and Activating the encryption.

    Elsewhere you will always receive an error message with insufficient transaction log data to preserve the log backup chain of the principal database.

  • jnrt74

    Step-7 Errors out
    ksmani99 so the error you are receiving is when you are trying to join your DB to the AG. If you are able to send through to my your scripts for me to have a look at I can get back to you.
    Thanks

  • jnrt74

    Not The Right Way
    SQLZen, you do not need to add the DB to the AG prior to enabling TDE. This can be done in either approach. I can see your issue though. In my script above I have the backup database statement using the With Copy_Only option. With this option being used it will not maintain the chain as you have mentioned. If you just perform the backup and restore process this is fine.

    Thanks

  • CDeaton

    Copy Only.
    I am in the process of adding a DR site to a 2 node group and the primary and secondary replica are encrypted. I was planning on adding the keys and certs as mentioned above and then just adding the DR server to the group. However I did just notice the copy only backup which will not work. The only method I can think of is to drop the availability group and start over with three nodes.

  • jnrt74

    With Copy Only
    CDeaton Yes you are correct. I will get that removed. That was left over from some test scripts. Thanks