Enable TDE for Databases in a SQL Server AlwaysOn Availability Group

By: Jan Potgieter | Updated: 2022-09-09 | Comments (1) | Related: > Availability Groups

Problem

A customer has a database that is already set up in a SQL Server Availability Group. Since this database hosts sensitive data, there is a need to encrypt the primary and all secondary replicas of the data. In this article, we will walk through how this can be done.

Solution

In this article we will cover the following:

Enabling TDE for databases in an Availability Group
Removing TDE for a database
Removing TDE from SQL Server
Backing up and Restoring a TDE enabled database.

I will be using the AdventureWorks2014 database to show all the examples. The AdventureWorks2014 database has already been added to the High Availability group and is in a Synchronized state on the Primary replica.

In this article, I will explain the process of encrypting a database while the database is already in the HA Group.

Setting the scenario:

High Availability environment with a Primary SQL Server and two Replica servers
SQL Server version 2014
AdventureWorks2014 database is configured to be in the High Availability Group

Setting Up Transparent Data Encryption for Database in an Availability Group

Step 1: Create a database master key on the primary replica.

/****************************************************************************************/
-- Check: Is the database in the HA Group?
-- If not, put the database in the HA Group first before continuing
/**************************************************************************
   Using below SQL to generate a password
SELECT cast(NEWID() as varchar(64))
You will use this password below when creating the master key, as well in Steps 4, 6 and 7
*/
 
use master;
 
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = '571093E5-9265-493F-AE2C-83453ED8D4CF'
GO
 
-- Check the master key created above
-- Look for name: ##MS_DatabaseMasterKey##
select * from sys.symmetric_keys;

Step 2: Create a certificate protected by the database master key.

If you do not set the expiry date, the default expiry date will be one year. It might be a good idea to set the expiry date to sometime in the future instead of just one year out, but it depends on your needs.

/***************************************************************************/
-- Decide on an appropriate name and subject for your environment
-- In this case the certificate is: TDECertificate and Subject: ‘TDE Certificate'
 
CREATE CERTIFICATE TDECertificate
WITH SUBJECT = 'TDE Certificate',
EXPIRY_DATE = '2100-12-31';
 
-- Check that the certificate was created above
select name
   , pvt_key_encryption_type_desc
   , issuer_name
   , subject
   , expiry_date
   , start_date
from sys.certificates
where name = 'TDECertificate';

Step 3: Create a database encryption key and use the certificate to protect it.

The database encryption key (DEK) is a symmetric key to encrypt the actual database content using AES algorithms.

/***************************************************************************/
-- You will get a warning when creating the database encryption key
-- The warning is to make sure you backup the certificate and the private key
 
use [AdventureWorks2014];
 
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECertificate
GO
 
--Warning: The certificate used for encrypting the database encryption key has not been backed up. 
--You should immediately back up the certificate and the private key associated with the certificate. 
--If the certificate ever becomes unavailable or if you must restore or attach the database on another server, 
--you must have backups of both the certificate and the private key or you will not be able to open the database.

Step 4: Backup the certificate to a file.

It is a good idea to backup the certificate and the private key to enable you to restore the database backup to another SQL Server.

/**************************************************************************
   Backup the certificate to a file
*/
 
use master;
 
BACKUP CERTIFICATE TDECertificate
TO FILE = 'C:\Temp\TDECertificate.cer'
WITH PRIVATE KEY (FILE = 'C:\Temp\TDECertificate.pvk',
ENCRYPTION BY PASSWORD = '571093E5-9265-493F-AE2C-83453ED8D4CF')
GO

After the above step, you should find the certificate and private key files in the location you specified above, in this case: C:\Temp.

Perform the following on the secondary replicas

Step 5: Copy the certificate and the private key to the secondary replicas. You should copy the above two files to the location on the replicas that will be specified in Step 7 below.

Step 6: Create a database master key on the secondary replicas. Now you will create a certificate on the secondary replicas by using the primary replica's certificate that you copied in Step 5 above.

/**************************************************************************
   Change connection to Secondary Replica
*/
 
use master;
 
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = '571093E5-9265-493F-AE2C-83453ED8D4CF'
GO
 
-- Check if the master key was created
select * from sys.symmetric_keys;

Step 7: Create a certificate on all the secondary replicas. Remember to use the password previously used in Step 4 above.

/***************************************************************************/
-- Create the certificate on the Secondary Replicas
use master;
 
CREATE CERTIFICATE TDECertificate
FROM FILE = 'C:\Temp\TDECertificate.cer'
WITH PRIVATE KEY (FILE = 'C:\Temp\TDECertificate.pvk',
DECRYPTION BY PASSWORD = '571093E5-9265-493F-AE2C-83453ED8D4CF')
 
-- Resume database in HA group
-- I found sometimes that the database is on Pause in the Replicas
-- Just right-click and resume the database again

Perform the following on the primary replica

Step 8: Enable TDE on all databases in the Always On Availability Group. Here you will set the encryption to on for a database.

You can monitor the progress of the encryption process by using the SELECT statement below.

/***************************************************************************/
use master;
 
ALTER DATABASE [AdventureWorks2014]
SET ENCRYPTION ON
 
 -- Check
select db.name
   , db.is_encrypted
   , dm.encryption_state
   , dm.percent_complete
   , dm.key_algorithm
   , dm.key_length
from sys.databases db
left outer join sys.dm_database_encryption_keys dm
   on db.database_id = dm.database_id

You can run the above SELECT on any of the secondary replicas as well and you will find that the database is in the process of being encrypted (if it is a large database) or has already been encrypted.

Turn on encryption for additional databases

Step 9: When you have more databases to encrypt, these are the steps to follow. In the section below, we will activate encryption for the StackOverflow2010 database.

/***********************************************************************************************
   Enable TDE On HA Group - StackOverflow2010
*/
 
/**************************************************************************/
-- Check that the database master key was created
select * from sys.symmetric_keys;
 
/**************************************************************************/
-- Check that a certificate was created
select name
      , pvt_key_encryption_type_desc
      , issuer_name
      , subject
      , expiry_date
      , start_date
from sys.certificates
 
/**************************************************************************/
-- Create a database encryption key on primary and use the certificate to protect it
 
USE StackOverflow2010;
GO
 
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECertificate
GO
 
USE master;
go
 
/**************************************************************************
Enable TDE on the database in the Primary server
*/
USE master;
go
 
ALTER DATABASE StackOverflow2010
SET ENCRYPTION ON
 
-- Check if the database encryption was set on for the database
select db.name
      , db.is_encrypted
      , dm.encryption_state
      , dm.percent_complete
      , dm.key_algorithm
      , dm.key_length
from sys.databases db
left outer join sys.dm_database_encryption_keys dm
      on db.database_id = dm.database_id

When running the above select query, you should see that the StackOverflow2010 database is in the process of being encrypted.

Check back later and you should see that your database is encrypted and the encryption_state flag is set to 3.

Remove TDE for a database

In Step 8 above, encryption was set to on for the AdventureWorks2014 database.

In this next section, I will show how to set encryption to off in a HA environment.

/**************************************************************************/
-- On Primary - Remove encryption from database AdventureWorks2014
-- Setting encryption off, only need to be done on the Primary Replica
-- The HA will automatically set it off on the Secondary Replica's
 
use master;
 
alter database AdventureWorks2014
set encryption off;
go
 
-- Check the status as database is busy decrypting – see image below
-- You can also use the SQL below to get a more informative message
SELECT DB_NAME(database_id) AS DatabaseName
   , encryption_state
   , encryption_state_desc =
      CASE encryption_state
         WHEN '0'  THEN  'No database encryption key present, no encryption'
         WHEN '1'  THEN  'Unencrypted'
         WHEN '2'  THEN  'Encryption in progress'
         WHEN '3'  THEN  'Encrypted'
         WHEN '4'  THEN  'Key change in progress'
         WHEN '5'  THEN  'Decryption in progress'
         WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
         ELSE 'No Status'
         END
   , percent_complete
   , encryptor_thumbprint
   , encryptor_type  
FROM sys.dm_database_encryption_keys

The first time I ran the select statement it shows this is in process.

The next time I rand the select statement it shows that it is now unencrypted.

Then we can do some cleanup.

/**********************************************************/
-- On Primary Replica
-- Once database has been decrypted, run below SQL commands
 
use [AdventureWorks2014];
go
 
drop database encryption key;
go
 
use master;
go
 
-- Now encryption has been removed from the database
-- DONE!

Remove TDE from SQL Server

In this section, I will work through a scenario to remove the encryption completely from the SQL Servers in the HA environment.

Step 1: Check if any user databases are still encrypted.

/*********************************************************/
-- Encryption for all databases should be turned off
 
USE [master]
GO
 
-- Check if any Databases are still encrypted
select db.name, db.is_encrypted, dm.encryption_state
   , dm.percent_complete, dm.key_algorithm, dm.key_length
from sys.databases db
left join sys.dm_database_encryption_keys dm
   on db.database_id = dm.database_id

The is_encrypted flag is set to 0 to show that the database is not encrypted.

Step 2: On secondary replica(s):

/**********************************************************/
-- On Secondary Replica's
-- Make sure you have the correct Certificate name to drop
-- Use the select below to get the certificate name
 
use master
 
-- Drop the Certificate
drop certificate TDECertificate;
 
-- Check if certificate is dropped
select name, subject, expiry_date, start_date
from sys.certificates
 
 
-- Drop the master key and check
drop master key
 
-- Check master key
select * from sys.symmetric_keys

Step 3: On primary replica:

/**********************************************************/
-- On Primary drop the certificate and check
 
use master;
 
-- Drop the Certificate
drop certificate TDECertificate
 
-- Check Certificate
select name, subject, expiry_date, start_date
from sys.certificates
 
/***********************************************************/
-- On the Primary Replica and the Secondary Replica's
-- Drop the master key
drop master key
 
-- Check master key
select * from sys.symmetric_keys

That's it. TDE is now completely removed from the environment.

Backup Encrypted Database and Restore on Another Server

Step 1: Back up an encrypted database.

BACKUP DATABASE [AdventureWorks2014] TO DISK = 'C:\Temp\AW2014_Encrypted.bak'

Step 2: Copy the backed-up file to another SQL Server where you can test the restore of the encrypted database.

Step 3: Try to read the backup file and you will get an error.

RESTORE FILELISTONLY FROM DISK= 'C:\Temp\AW2014_Encrypted.bak'

Step 4: If you try to do a restore you will get an error.

-- Restore Database to new device / location
RESTORE DATABASE AW2014_Encrypted FROM DISK= 'C:\Temp\AW2014_Encrypted.bak' WITH 
MOVE 'AdventureWorks2014_Data' TO 'C:\Temp\AW2014_Encrypted_Data.mdf',
MOVE 'AdventureWorks2014_Log'  TO 'C:\Temp\AW2014_Encrypted_Log.ldf',
REPLACE

Step 5: Create a database master key on the primary replica.

/**************************************************************************
   Create a database master key on the primary replica
*/
 
USE master;
GO
 
-- Need password for master key
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = 'E3B75E9E-A18C-43BE-AE6D-FFCF5EF61C54'
GO
 
-- Check
select * from sys.symmetric_keys;

Step 6: Copy the .cer and .pvk files to a location on the new location server.

Step 7: Make sure you have the correct password for the certificate.

Step 8: Create a certificate protected by the database master key on the server where you want to restore the database.

/**************************************************************************
   Create a certificate protected by the database master key
   Copy the .cer and .pvk file to location on new location server: C:\Temp   Need the correct password for the certificate
*/
 
create certificate TDECertificate
from file = 'C:\Temp\TDECertificate.cer'
with private key (
   file = 'C:\Temp\TDECertificate.pvk',
   decryption by password = 'E3B75E9E-A18C-43BE-AE6D-FFCF5EF61C54'
   );
go
 
-- Check if the certificate was created
select name, subject, expiry_date, start_date
from sys.certificates

Step 9: Try to do the restore again and it should now be successful.

-- Restore Database to new device / location
RESTORE DATABASE AW2014_Encrypted FROM DISK= 'C:\Temp\AW2014_Encrypted.bak' WITH 
MOVE 'AdventureWorks2014_Data' TO 'C:\Temp\AW2014_Encrypted_Data.mdf',
MOVE 'AdventureWorks2014_Log'  TO 'C:\Temp\AW2014_Encrypted_Log.ldf',
REPLACE,  STATS = 5

Next Steps

Check out the following articles:

You can also read the following that has a slightly different approach when encrypting databases with TDE on a High Availability Group:

Configuring TDE with AlwaysOn Availability Group

About the author

Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-09-09

Setting up SQL Server Transparent Data Encryption (TDE) in a High Availability (HA) environment