Rajendra Gupta
Transparent Data Encryption (TDE)

Transparent Data Encryption for SQL Server Always On Availability Groups

August 28, 2020 by

In this 15th article of SQL Server Always On Availability Groups series, we will cover Transparent Data Encryption (TDE) for AG databases.

Introduction

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.

Transparent Data Encryption (TDE)

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.

Prerequisites

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

AG dashboard

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.

Overall steps to enable TDE

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.

Database Master Key (DMK) on the primary replica

  • 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.

Create the Certificate

We can query the sys.certificates system object to verify the certificate exists for our SQL instance.

Check the Certificate

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 3: Create a database encryption key and use the certificate to protect it

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.

Backup the certificate

It creates two files in the specified directory, as shown below.

Check certificates

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.

Create a database master key

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.

Create a certificate on the secondary replicas

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.

Enable TDE

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.

Monitor the transparent data encryption

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.

Monitor the transparent data encryption using case statement

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

SQL Server error logs

168 Views