SQL Server 2012, Transparent Data Encryption, and Availability Groups

We are looking into using Microsoft Bitlocker Administration and Monitoring (MBAM) 2.0 to manage BitLocker in our environment. One requirement for MBAM is a SQL Server database instance that supports Transparent Database Encryption (TDE).  (Update 2013-06-04:  Microsoft now claims that TDE is “optional” with MBAM 2.0, which is nice to know.  If only they had told me this before I went to the trouble of setting up SQL 2012 Enterprise just for this project!)  Currently we also are in the process of investigating the creation of a consolidation SQL 2012 Enterprise Edition “Always On” Availability Group. I wanted to see if I could create the MBAM Recovery Database in a SQL 2012 Availability Group. This proved slightly tricky… fortunately I was able to find a decent reference here:

https://www.simple-talk.com/sql/database-administration/encrypting-your-sql-server-2012-alwayson-availability-databases/

The trick is, you need to create SQL Certificates that on each member server of the Availability Group that have the same name and are generated from the same private key. The procedure follows…

On the first server in the group, create a SQL Master Key and Certificate by running the following code. The script will create a backup file in your SQL Server data directory. Move this file to an archival location. If you lose the file and password, you will not be able to recover encrypted databases in a disaster event:

USE MASTER
GO

-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'Password1';

-- Backup the Master Key
BACKUP MASTER KEY
   TO FILE = 'Server_MasterKey'
   ENCRYPTION BY Password = 'Password2';

-- Create Certificate Protected by Master Key
CREATE Certificate SQLCertTDEMaster
   WITH Subject = 'Certificate to protect TDE key';

-- Backup the Certificate
BACKUP Certificate SQLCertTDEMaster
   TO FILE = 'SQLCertTDEMaster_cer'
   WITH Private KEY (
       FILE = 'SQLCertTDEMaster_key',
       ENCRYPTION BY Password = 'Password3'
   );

Now create a master key on any secondary servers in the availability group, and create the same cert by using the backup file from the first step, above. You will need to copy the certificate backup files to the local server data directory, or use a network share that is accessible to the account running the script:

-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'Password1';
-- Backup the Master Key
BACKUP MASTER KEY
   TO FILE = 'Server_MasterKey'
   ENCRYPTION BY Password = 'Password2';

-- Create Certificate Protected by Master Key
CREATE Certificate SQLCertTDEMaster
   FROM FILE = 'SQLCertTDEMaster_cer'
   WITH Private KEY (
       FILE = 'SQLCertTDEMaster_key',
       Decryption BY Password = 'Password3'
   );

To avoid needless trouble, create your new database and add it to your availability group before encrypting the database. Once the database is created, you can initiate encryption by opening SQl Management Studio, right-clicking your database, select tasks, then select “Manage Database Encryption”. Select the option to generate the database encryption key using a server certificate. Select the certificate created above, and select the option to “set database encryption on”.

Once the database is encrypted, be sure to test availability group failover to make sure the secondary servers are able to work with the encrypted database.

Comments are closed.