Adding a TDE-Enabled Database to an Always On Availability Group

Introduction

Ensuring the security of your database is crucial, especially when it comes to protecting sensitive data. Transparent Data Encryption (TDE) is a feature in SQL Server that provides encryption at the file level, securing your data and preventing unauthorized access. In this blog post, we will walk you through the step-by-step process of adding a TDE-enabled database to an Always On Availability Group in SQL Server, allowing you to combine high availability with enhanced security.

Prerequisites

Before we dive into the steps, make sure you have the following prerequisites in place:

1. An existing SQL Server Always On Availability Group configuration.
2. A database that is already encrypted using TDE.
3. A Windows Server Failover Cluster (WSFC) with at least two nodes.

Create a TDE encrypted user database

To include a TDE encrypted database in an existing Always On Availability Group site. Let’s assume we have set up the Always On Availability Group AG2, which consists of two replicas: BOX1 and BOX2. The primary replica is BOX1, and the secondary replica is BOX2. Both are set up for Manual Failover.

We must first create a master key in the ‘master’ database before configuring the TDE on the ‘AdventureWorks2016_TDE‘ user database. Each SQL Server instance can only have one master key. The same master key will be used by all user databases that are hosted in the same SQL instance and have the TDE enabled.

The master key under the master database is created using the below CREATE MASTER KEY T-SQL statement, and this master key will be encrypted with a strong password. It’s preferable to store a backup of that password in a safe location also.

-- T-SQL Server script that creates a master key and 
-- encrypts it using a password. 
USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Welcome@12345';
GO

To check if the master key encryption has been created in the master database, you can use the following T-SQL script.

-- To check if the master key encryption has been created 
-- in the master database, you can use the following T-SQL script.

USE master;
GO

SELECT name, create_date, modify_date
FROM sys.symmetric_keys
WHERE name LIKE '##MS_DatabaseMasterKey##';

The next step is to create the certificate that will be used to encrypt the database encryption key. The previously created ‘master key’ on master database will be used to secure the Certificate. The CREATE CERTIFICATE T-SQL statement following can be used to create the Certificate:

-- T-SQL script that creates a certificate in SQL Server

USE master;
GO

CREATE CERTIFICATE TDE_CERT WITH SUBJECT = 'TDE_on_CERT_testDB';
GO

We may run the following query on the ‘sys.certificate’ system object to find out whether the certificate was successfully produced for the present SQL Server instance.

USE master;
GO
SELECT 
   name AS 'Certificate Name', 
   certificate_id AS 'Certificate ID', 
   principal_id AS 'Principal ID', 
   pvt_key_encryption_type_desc AS 'Private Key Encryption Type Description', 
   start_date AS 'Start Date', 
   expiry_date AS 'Expiry Date' 
FROM sys.certificates;

Now the certificate is already prepared, we can create a TDE encrypted user database.

To create the TDE enabled test database encryption key that will be encrypted by the ‘TDE_CERT‘ certificate created previously in master database.

T-SQL script creates a database encryption key in the AdventureWorks2016_TDE database using AES_128 algorithm and encrypts it with the server certificate named TDE_CERT. Here’s the script:

USE AdventureWorks2016_TDE;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_CERT;
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.

T-SQL script performs a backup of the TDE_CERT certificate, including the private key, to files stored in the specified paths. Here’s the script:

USE master
GO

BACKUP CERTIFICATE TDE_CERT
TO FILE = 'C:\Backup\TDE_CERT.bak'
WITH PRIVATE KEY (
    FILE = 'C:\Backup\TDE_CERT.pvk',
    ENCRYPTION BY PASSWORD = '12345@Welcome'
);
GO

Now enables Transparent Data Encryption (TDE) for the AdventureWorks2016_TDE database. Here’s the script:

ALTER DATABASE [AdventureWorks2016_TDE]
SET ENCRYPTION ON;
GO

T-SQL script retrieves information about the encryption state and details of the database encryption keys for each database. Here’s the script:

SELECT 
	 DB_NAME(database_id) AS DatabaseName
	,Encryption_State AS EncryptionState
	,key_algorithm AS Algorithm
	,key_length AS KeyLength
FROM sys.dm_database_encryption_keys;
GO

So, AdventureWorks2016_TDE database in is now encrypted on the primary replica is BOX1, in the AG2 Always On Availability Group.

To replicate it to the BOX2, we must now add it to the Availability Group. But Availability Group Wizard do not support adding databases to an Availability Group because the database is TDE encrypted.

So, using T-SQL script only we can add database in secondery replica.

Again, on the master database secondary server, we should first construct a master key. We have the option of using the same password as for the previous master key or creating a new password, as the master key is used to encrypt the certificate rather than the database itself to keep it secure. The key itself will be different in all circumstances due to how the key creation process is carried out.

-- T-SQL Server script that creates a master key and 
-- encrypts it using a password. 
USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Welcome@12345';
GO

To check if the master key encryption has been created in the master database, you can use the following T-SQL script.

-- To check if the master key encryption has been created 
-- in the master database, you can use the following T-SQL script.

USE master;
GO

SELECT name, create_date, modify_date
FROM sys.symmetric_keys
WHERE name LIKE '##MS_DatabaseMasterKey##';

The next step is to copy the previously generated certificate and private key backup files from the BOX1 primary replica to the BOX2 secondary server.

The certificate’s private key should be decrypted using the same password that was used to encrypt it when the backup was created:

USE master
GO

CREATE CERTIFICATE TDE_CERT
FROM FILE= 'C:\Backup\TDE_CERT.bak'
WITH PRIVATE KEY (
FILE = 'C:\Backup\TDE_CERT.pvk',
DECRYPTION BY PASSWORD='12345@Welcome')
GO

By executing the following query on the ‘sys.certificate’ system object to we may find certificate for the present SQL Server instance.

USE master;
GO
SELECT 
   name AS 'Certificate Name', 
   certificate_id AS 'Certificate ID', 
   principal_id AS 'Principal ID', 
   pvt_key_encryption_type_desc AS 'Private Key Encryption Type Description', 
   start_date AS 'Start Date', 
   expiry_date AS 'Expiry Date' 
FROM sys.certificates;

Now the secondary replica BOX2 now has access to the certificate that encrypts the AdventureWorks2016_TDE database. We will now backup and restore the AdventureWorks2016_TDE database from the primary replica to the secondary replica, create a full backup, and backup the transaction logs.

BACKUP DATABASE [AdventureWorks2016_TDE] TO  DISK = N'C:\AW_TDE.bak' 
WITH  DESCRIPTION = N'AdventureWorks2016_TDE-Full Database Backup', 
NOFORMAT, NOINIT,  NAME = N'AdventureWorks2016_TDE-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

BACKUP LOG [AdventureWorks2016_TDE] TO  DISK = N'C:\AW_TDE_T-LOG.trn' 
WITH  DESCRIPTION = N'AdventureWorks2016_TDE-T-LOG Database Backup', 
NOFORMAT, NOINIT,  NAME = N'AdventureWorks2016_TDE-T-LOG Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

Now we will use the WITH NORECOVERY option to restore the full backup and the transaction log backup on the secondary server, BOX2.

USE [master]
RESTORE DATABASE [AdventureWorks2016_TDE] 
FROM  DISK = N'C:\AW_TDE.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 5

GO

RESTORE LOG [AdventureWorks2016_TDE] 
FROM  DISK = N'C:\AW_TDE_T-LOG.trn' WITH  
FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

To add the database “AdventureWorks2016_TDE” to the availability group “AG2”, you can use the following SQL commands in primary node BOX1.

USE master;
GO

ALTER AVAILABILITY GROUP AG2 ADD DATABASE [AdventureWorks2016_TDE];

validate the health of an Always On Availability Group (AG)

To validate the health of an Always On Availability Group (AG) using a dashboard, you can follow these steps:

1. Launch the SQL Server Management Studio (SSMS) and connect to the primary replica of the AG.
2. Expand the “Always On High Availability” node in the Object Explorer and navigate to the Availability Groups folder.
3. Right-click on the AG you want to validate and select “Show Dashboard” from the context menu. This will open the Availability Group Dashboard.
4. The Availability Group Dashboard provides an overview of the health and status of the AG and its associated replicas. It displays various sections such as Cluster, Availability Databases, Replica Health, and more.
5. Review the different sections of the dashboard to ensure that everything is functioning correctly. Pay attention to any alerts, warnings, or errors that are highlighted.
6. In the “Replica Health” section, you can see the synchronization state of each replica, whether they are synchronized, synchronizing, or not synchronized. This information helps determine if the data is being replicated properly.
7. The “Availability Databases” section displays the health of individual databases within the AG. Check for any issues or discrepancies related to the databases.
8. Monitor other sections of the dashboard, such as Cluster, Log Send Queue, and Redo Queue, to get a comprehensive view of the AG’s health and performance.
9. If you encounter any errors or warnings, investigate further to determine the root cause and take appropriate actions to resolve them.

The Availability Group Dashboard provides a visual representation of the AG’s health, making it easier to monitor and identify potential issues. It is a useful tool for ensuring the availability and reliability of your AG configuration.

Failover test on an Availability Group

Performing a manual failover test on an Availability Group can help validate the functionality and readiness of your database, including one with Transparent Data Encryption (TDE) enabled.

Conclusion

By following this step-by-step guide, you have successfully added a TDE-enabled database to an Always On Availability Group in SQL Server. This configuration provides both high availability and enhanced security for your data, protecting it from unauthorized access. Remember to regularly monitor and maintain your availability group to ensure its optimal performance and security.

Disclaimer: Always perform thorough testing and follow best practices before implementing any changes in a production environment.

Thank you for joining us on this tech journey! We hope you’ve found our blog post insightful and informative. We value your feedback and would love to hear your thoughts on the topics discussed.