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.

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:




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








About the author
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