How To Enable Transparent Data Encryption In An Existing SQL Server Availability Group

I’ve deployed several SQL Server Availability Groups in the past with high availability being the primary requirement. In some environments, there is a requirement to protect sensitive data for security and compliance reasons. Back in 2013, I had a customer who wanted to implement Availability Groups for their databases. They were moving their on-premise databases to a new hosting provider and wanted to make sure that the databases are protected at-rest. Their existing solution uses the Windows Encrypting File System feature for encrypting the disk volumes that contain the database files and TrueCrypt for the backups. But with the plan to move to a hosting provider, they need something that can do both because they don’t have the flexibility of keeping their existing solution. Besides, they already have the license for SQL Server 2012 Enterprise Edition, we might as well make the most out of it.

I also wrote an article that outlines what needs to be done to prepare their databases for the upgrade (since they were still on SQL Server 2008) and migration. And while the article has been very helpful for new deployments, not so much for existing Availability Group implementations. Business requirements change frequently and you might need to implement Transparent Data Encryption (TDE) on your databases that are already in an Availability Group configuration. If you follow the steps outlined in the article, you might end up causing issues on your Availability Group databases depending on the amount of transactions generated. I’ve outlined the steps that you need to take if you would like to enable TDE on an existing database participating in an Availability Group. I’m “stealing” the code examples in the article for reference.

  1. Create a service master key on the primary replica. The Service Master Key is the root of the SQL Server encryption hierarchy. It is responsible for encrypting the encrypting the database master key for the master database because that is where we will be storing all of our database master keys. To create a service master key, 
    USE MASTER
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mYC0mpl3XP@$$w0rd'
    GO
  2. Create a certificate protected by the service master key. Once we’ve created a service master key, we need to create a certificate protected by the service master key. This certificate is what we’ll use to protect the database encryption key (DEK) used to enable TDE. To create a certificate protected by the service master key, 
    CREATE CERTIFICATE TDECert
    WITH SUBJECT = 'My TDE Certificate for all user database in the Availability Group'
  3. Create a database encryption key and use the certificate to protect it. After the certificate has been created, we can now create a database encryption key that we can use to enable TDE. You will need to do this on all of the databases that are already on the Availability Group. To create  a database encryption key and protect it using the certificate, 
    USE [DemoSalesDB]
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH
    ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE TDECert
    GO
  4. Backup the certificate to a file. This is where the process is a bit different from the article. If you enable TDE prior to restoring the certificate on the secondary replicas, the transaction log records won’t get applied to the secondary databases because they “don’t speak the same language yet.” You want to make sure that the databases in an Availability Group configuration “speak the same language” in order for the secondary replicas to properly receive the transaction log records from the primary replica and apply them to the log file. To backup the certificate to a file, 
    USE MASTER
    GO
    BACKUP CERTIFICATE TDECert
    TO FILE = 'C:\TDECert_File.cer'
    WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk' ,
    ENCRYPTION BY PASSWORD = 'mYC0mpl3XP@$$w0rd' )
    GO
  5. Copy the certificate to the secondary replicas. Once the certificate has been generated to a file, copy it to all of the secondary replicas. We will use this certificate to protect
  6. Create a service master key on the secondary replicas. Similar to what we did on the primary replica, we need to create a service master key on all of the secondary replicas. Repeat this step and all the steps below on all of your secondary replicas.To create a service master key, 
    USE MASTER
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mYC0mpl3XP@$$w0rd'
    GO
  7. Create a certificate on all of the secondary replicas using the certificate file from the primary replica. Once we’ve created a service master key on the secondary replicas, we need to create a certificate based on the one that we generated and copied from the primary replica. To create the certificate on all of the secondary replicas, 
    USE MASTER
    GO
    CREATE CERTIFICATE TDECert
    FROM FILE = 'C:\TDECert_File.cer'
    WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk',
    DECRYPTION BY PASSWORD = 'mYC0mpl3XP@$$w0rd' );
  8. Enable TDE on all of the databases in the Availability Group. Now that we have given all of the Availability Group replicas a “common language to use to talk to each other” we can go ahead and enable TDE on all of the databases in the Availability Group. And since enabling this feature generates transaction log records, all of the databases in the secondary replicas will also have TDE turned on. Note that you still have to do this on a per-database level. To enable TDE on the database in an Availability Group, 
    ALTER DATABASE [DemoSalesDB]
    SET ENCRYPTION ON

Note that the steps are a bit different from the one described in the article because we don’t want to cause any disruption on the existing Availability Group configuration. Our goal is still maintaining high availability while meeting security and compliance requirements.

Additional Resources

Comments

comments

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

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