Availability Groups are fantastic for High Availability/Disaster Recovery solutions, and I’m sure that fellow DBAs will agree with me. However, there will be times when we must consider certain precautions and extra steps carefully to avoid unwanted surprises. For instance, any Secondary Replica becomes the current Primary Replica for whatever reason, and our goal is to not let it happen.
There are two encryption options provided by SQL: sql tde vs always encrypted. In this article, I’m going to showcase one scenario that requires the DBA to pay extra attention to detail. Just like the title says, it will guide you through the proper way to deal with the data encryption within databases that are a part of the AlwaysOn Availability Group setup.
Initial Considerations
I will be using Transparent Data Encryption (TDE) as the technology to build my case around. It applies to all the supported versions of SQL Server. It is important to mention that this feature is available within the following SQL Server Editions only:
- SQL Server 2019 Evaluation, Standard, Developer, Enterprise
- SQL Server 2017 Evaluation, Developer, Enterprise
- SQL Server 2016 Evaluation, Developer, Enterprise
- SQL Server 2014 Evaluation, Developer, Enterprise
- SQL Server 2012 Evaluation, Developer, Enterprise
- SQL Server 2008R2 Datacenter, Evaluation, Developer, Enterprise
- SQL Server 2008 Evaluation, Developer, Enterprise
Let’s see how we can use TDE (Transparent Data Encryption) in SQL Server Standard Edition. First of all, we need to create a DMK (Database Master Key) to encrypt the data. Then, we create a certificate and a key to be able to decrypt the data while accessing it. Don’t forget to backup the certificate and, finally, enable the encryption.
Note: The TDE feature is not supported in SQL Server Express Edition.
This post will not cover the steps to build an Availability Group, and I am relying on the one already built for testing purposes. You can read more about how to deploy SQL Server AlwaysOn availability groups on Linux.
The environment is Windows-based, but the principles will be very similar if you use different platforms (e.g. SQL Server on Linux or Azure SQL Managed Instances).
What is Temporary Data Encryption
The main reason why we use TDE is data and log files security for your SQL database. To prevent your personal data from being stolen, it is a good idea to defend it, plus this encryption process is very easy. Before the page is written in the disk, files are encrypted at the page level. Every time you want to access your data, it gets decrypted. After TDE implementation, you’ll need a specific certificate and a key to restore or attach the database. That’s what an encryption algorithm is.
Microsoft SQL Server Availability Group Example
My test Availability Group consists of 2 replicas, each in its own VM. Here are the basic properties:
As you can see, there is nothing fancy, just a couple of replicas using synchronous commit mode and under manual failover mode.
The following screenshot demonstrates a database called “test.” It is added to the SQL Server AlwaysOn Availability Group and it is in the synchronized state.
How to Enable TDE in SQL Server
Here are the steps to enable SQL Server TDE for the “test” database. Note: we’ll execute the following steps in the current Primary Replica.
Step 1
Create a master key in the master database.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
Step 2
Create a certificate protected by the master key.
CREATE CERTIFICATE TestCertificate WITH SUBJECT = 'My test Certificate';Step 3
Create the Database Encryption Key (DEK) and protect it with the certificate created in Step 2.
USE test;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TestCertificate;
Step 4
Set the “test” database to use encryption.
ALTER DATABASE test
SET ENCRYPTION ON;
How to Check if TDE is Enabled?
After you’re done, you need to confirm that Transparent Data Encryption in SQL Server is enabled for the “test” database.
In the Database Properties section, go to the Options page. There, pay attention to the State area at the bottom of the window. The Encryption Enabled value must be True.
You can also run the following TSQL code to confirm it:
SELECT name,is_encrypted
FROM sys.databases
WHERE name = 'test'
Key Management and Certification Problem
Note: Don’t be surprised if you find out that tempdb is encrypted as well. It is because tempdb is where all kinds of operations take place (e.g. sorting, hash joins, etc.), using the data from all databases. Therefore, if at least one user database is encrypted, operations from that particular database might go into tempdb which will need to return that data to the user database. Therefore, sending unencrypted data back would represent the issue.
You can read more about backup encryption in SQL Server to enhance the security of your database.
You can use the following TSQL Code to confirm that there’s a Database Master Key present for the “test” database that is encrypted by the certificate (as executed in Step 3):
SELECT
DB_NAME(database_id) AS DB,
create_date,
key_algorithm,
key_length,
encryptor_thumbprint,
encryptor_type
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = 'test'
So far so good, at least for the Primary Replica. But what happens if we query the sys.databases system view to confirm the encryption status of the “test” database in the Secondary Replica?
The Availability Group replicates everything related to the database from one replica to another. However, the Secondary Replica clearly states that it’s not encrypted.
Let’s check our Secondary Replica for any clues about that:
The state of the database is “Not Synchronizing / Suspect” – not looking good at all. However, after inspecting the Error Log of the Secondary Replica, I can see the following:
2021-04-10 00:40:55.940 spid39s Error: 33111, Severity: 16, State: 3.
2021-04-10 00:40:55.940 spid39s Cannot find server certificate with thumbprint '0xDF36E3D052086AA05BBB1C64A72A2CAB5A98F240'.
2021-04-10 00:40:55.950 spid39s Error: 33111, Severity: 16, State: 3.
2021-04-10 00:40:55.950 spid39s Cannot find server certificate with thumbprint '0xDF36E3D052086AA05BBB1C64A72A2CAB5A98F240'.
2021-04-10 00:40:55.950 spid39s Always On Availability Groups data movement for database 'test' has been suspended for the following reason: "system" (Source ID 2; Source string: 'SUSPEND_FROM_REDO'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
2021-04-10 00:40:55.950 spid39s Error: 3313, Severity: 21, State: 2.
2021-04-10 00:40:55.950 spid39s During redoing of a logged operation in database 'test', an error occurred at log record ID (34:743:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
The main problem is that the certificate used to encrypt the Database Encryption Key of the “test” database (Step 3) is not present in the Secondary Replica.
Why?
Because Availability Groups do not replicate data from system databases. The server certificate missing resides in the Primary Replica master database.
How to Check and Set up TDE Certificate in SQL Server
Let’s generate a backup of the server certificate in the Primary Replica master database. Then let’s restore it in the master database of the Secondary Replica.
Use the following TSQL code to generate the backup from the current Primary Replica that has the “test” database with TDE enabled:
USE master;
GO
BACKUP CERTIFICATE TestCertificate
TO FILE = 'C:\temp\TestCertificate.cer'
WITH PRIVATE KEY (file='C:\temp\TestCertificate.pvk',
ENCRYPTION BY PASSWORD='<YourStrongPasswordHere>');
Before attempting to restore the certificate in the Secondary Replica, first check if the Database Master Key exists within the master database. If not, create it exactly as in Step 1.
Use the following TSQL code to restore the certificate in the Secondary Replica. Note: Make sure to copy the .cer and .pvk files to the target directory first.
USE master;
GO
CREATE CERTIFICATE TestCertificate
FROM FILE = 'C:\temp\TestCertificate.cer'
WITH PRIVATE KEY (
FILE = N'C:\temp\TestCertificate.pvk',
DECRYPTION BY PASSWORD = '<YourStrongPasswordHere>'
);
Thus, even after restoring the certificate in the Secondary Replica, the state of the “test” database remains the same:
Since the data movement for the “test” database is paused, let’s manually resume it to see if we’re lucky this time:
Yes! The operation was successful. The “test” database is not only fully synchronized and healthy but also encrypted with TDE.
Besides, after the performance of the manual failover to swap the roles of the replicas, everything continues to work perfectly fine.
Conclusion
The showcased solution worked perfectly fine. However, it might not be ideal in all cases, especially if you are a DBA who likes to plan and do things in the “correct” way. I see “correct” as follows:
- Remove the database from the Availability Group
- Execute all the steps to enable Transparent Data Encryption in the replica where the database is read/written.
- Backup the server certificate from the Primary Replica.
- Copy the backup file to the location(s) of the Secondary Replica(s).
- Restore the certificate in the master database.
- Add the database back to the Availability Group.
- Make sure that the operational state of the database is the correct and intended one (depending on your particular setup).
I’m throwing double-quotes for “correct” because in the way I presented the solution I got the database in the Secondary Replica marked as Suspect. This alone would probably raise many unwanted flags/alerts/finger-pointing. It is unnecessary noise that you can easily avoid by taking into account all the considerations to plan and properly implement TDE in an Always On Availability Group setup.
To wrap this post, I’m leaving you with the official Encryption Hierarchy used for TDE, that Microsoft has posted on their website. What I’d like you to keep in mind is where each element is created (either in the master or user database), so that you can overcome any potential issues/surprises with Availability Groups.
In case you are not aware, SQL Complete can greatly assist you to configure Always Encrypted, which is another useful technology to protect sensitive data.
Keep in mind that you would need to consider the same that is discussed within this article if you’re planning to deal with Always Encrypted in an Always On Availability Group scenario. However, features that SQL Complete v6.7 introduces are designed to make sure that you succeed right off the bat.
Tags: data encryption, sql always on, sql server availability groups Last modified: October 07, 2022
