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

By:   |   Updated: 2022-09-09   |   Comments   |   Related: More > 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.

tde cert files

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.

tde status
tde status

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

tde status

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.

tde status

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

tde status

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
tde status

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'
restore filelistonly

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
restore database

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;
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
keys

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:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights

Related Articles




get scripts

next tip button



About the author
MSSQLTips author Jan Potgieter Jan Potgieter has years of experience in Information Technology which ranges from Unix Administration through System Administration and development, Web and Database Development and Administration and applying Agile methodologies.

View all my tips


Article Last Updated: 2022-09-09

Comments For This Article

















get free sql tips
agree to terms