In this 15th article of SQL Server Always On Availability Groups series, we will cover Transparent Data Encryption (TDE) for AG databases.
In the previous articles of SQL Server Always On series, we explored the following topics so far.
- Build virtual servers using Oracle VirtualBox
- We configured domain controller, active directory and domain name service (DNS)
- Deployed SQL Server 2019 Availability groups
- Domain-independent AG in Windows Server 2016
- We configured distributed availability groups for the availability group in independent clusters
Suppose, In specific requirements, you might want to protect sensitive data for compliance and security purposes. SQL Server provides Transparent Data Encryption(TDE) for encrypting the physical files. In this article, we will explore TDE for a SQL Server Always On Availability Group.
Overview of Transparent Data Encryption (TDE)
The objective of implementing TDE is to secure the data and log files for a SQL database. Suppose you have sensitive customer data in your database. Someone might copy your database files and easily access critical information. This encryption process does not require any changes from the application end. It encrypts the files at the page level before writing the page in the disk. It decrypts the files once a user tries to access a page. Once we implement TDE, we cannot restore or attach a database without an appropriate certificate and key.
The high-level steps for a TDE are as below.
TDE is not straightforward for an availability group database. In the next section, we explore the steps in a two-node SQL Server Always On Availability Group.
Prepare two-node availability group replicas in synchronous mode. In this article, I have the following servers.
- Current Primary replica: SQLAG1\INST1
- Secondary replica: SQLAG2\INST2
- Synchronization status: Synchronized
- Failover mode: Automatic
- AG database: DBARepository
Steps to enable TDE for SQL Server Always On Availability Groups
Let’s explore the steps for TDE for the availability group database. We do not have transparent data encryption enabled on any of the replicas as of now. The database [DBARepository] is part of the availability group, and we require to enable TDE on it.
The below figure highlights the steps required to enable the TDE on an availability group database.
Step 1: Database Master Key (DMK) on the primary replica
We require a database master key(DMK) on the primary replica to secure certificates and keys. We require a DML on the SQL instance. All user databases share the same DMK for encryptions.
First, you can check if your instance already has a database master key. We already created a DML for the distributed availability group in the earlier article.
- You should use complex passwords for creating a master key
- If the database master key is already available for your SQL instance, but you do not know the password of it, your SQL service account with the SA permission can decrypt the key
- You can create the DML on all AG instances with different passwords
Step 2: Create the Certificate for the AG database on the primary replica
In this step, we create a certificate to encrypt the Database Encryption Key. The master key created in step 1 protects the certificate.
We can query the sys.certificates system object to verify the certificate exists for our SQL instance.
Step 3: Create a database encryption key and use the certificate to protect it
In this step, we create a database encryption key (DEK) to enable the TDE. In our case, the DBARepository database is already part of the availability group, and we want to enable TDE on it.
The database master key(DEK) is the actual key for the encryption and decryption of the database. The server certificate protects it.
Execute the script under the AG database context and specify the certificate name in the parameter ENCRYPTION BY Server Certificate argument. In the output, you get a warning message. It asks you to back up the certificate and the private key. Don’t worry about it. We take the backup in the next step.
Step 4: Backup the certificate and private key on the primary replica
In this step, we need to take the certificate and its private key backup. This backup should be encrypted by a complex password as well.
It creates two files in the specified directory, as shown below.
The first file [TDECert] is the certificate and the second file [TDECert_private] is the private key file, and it is protected by the password specified while taking the backup.
Step 5: Create a database master key on the secondary replica
This step is similar to step 1. You should create a database master key on all secondary replicas if it does not exist. In my environment, this encryption key already exists on the secondary replica node SQLAG2\INST2 as shown below.
Step 6: Create a certificate on the secondary replicas from the primary replica certificate
You must copy the certificate from the primary replica to all secondary replicas. In this step, we create a certificate on the secondary replica from the primary replica certificate.
You must specify the password that we used earlier to encrypt the backup. If you specify a different password, it won’t decrypt the certificate.
Step 7: Enable TDE for the SQL Server Always On Availability Group database
We are ready to enable the TDE for the database participating in an availability group. Execute the following t-SQL on the primary replica.
Monitor the transparent data encryption in SQL Server Always On Availability Group
We use the dynamic management view sys.dm_database_encryption_keys to monitor the TDE status.
It gives the following column outputs:
- Is_encrypted: The value “1” denotes TDE is enabled for the corresponding database
- Encryption_state: Initially, once you enable the TDE, it shows the encryption_state 2 along with its percentage completion in the percent_complete column. It might take a few hours to enable TDE on a vast database. You should monitor the status of it after enabling it. Once encryption completes, it changes the encryption_state value to 3 and percent_complete value to zero
- Key_algorithm and key_length: It shows the algorithm and key length for encrypting the databases
As per the values of the encryption_state column, we can modify the query with the CASE statement in SQL. You can browse the article How to monitor and manage Transparent Data Encryption (TDE) in SQL Server for the monitoring query
We see that the TDE certificate encrypts our database DBARepository.
You can also monitor the TDE status in the SQL Server error logs. It has two useful entries.
- Beginning database encryption scan for the database DBARepository
- The database encryption scan for the database ‘DBARepository’ is complete
Note: If your database in the primary replica is encrypted, but this database is joined in the secondary replica,
you cannot use the availability group wizard. You must use the t-SQL for this purpose. You can refer to this
article, How to add a TDE encrypted user database to an Always On Availability Group for this purpose
- Current primary replica: SQLAG1\INST1
- New Primary replica: SQLAG2\INST2
- Failover mode: Manual failover with no data loss ( database in the Synchronized state)
- AG database: DBARepository
- Deploy Azure Data Lake Analytics database using the U-SQL scripts - February 23, 2021
- Join database tables using U-SQL scripts for Azure Data Lake Analytics - February 17, 2021
- Writing U-SQL scripts using Visual Studio for Azure Data Lake Analytics - February 11, 2021
SQL Server Always On Availability Group failover testing after TDE implementation
You must do failover testing after you implement any new functionality in the SQL Server Always On Availability Group. It helps you to troubleshoot any foreseen issues during implementations only. It is easy to fix the things compared to knowing the issue at the time of any failover.
Launch failover availability group wizard and select the new primary replica.
In my case, we have the following configurations.
AG Failover is successful now, as shown below.
Launch the AG dashboard and verify its status.
Verify TDE encryption as well after the failover using the monitoring scripts provided earlier.
In this article, we explored transparent data encryption (TDE) for the database in SQL Server Always On Availability Groups. You should implement TDE encryption to protect sensitive data files and prevent unauthorized access.