SQL Server Transparent Data Encryption, also known as TDE, is a “data at rest” encryption mechanism that is introduced in SQL Server 2008 as an Enterprise Edition feature. TDE is used to perform a real-time I/O encryption for the SQL Server database data, log, backup and snapshot physical files, rather than encrypting the data itself, using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption.
Transparent Data Encryption encrypts the database data files using a Database Encryption Key. The Database Encryption Key is a symmetric key that is secured by a Certificate and a Master Key stored in the master database, preventing these data files from being viewed outside the current SQL instance. However, if the database data files or the backup media are stolen, the data thief will not be able to attach the database data files or restore the backup files to another SQL instance without the encryption keys. TDE also prevents the data and backup files from being opened in a text editor to view the database file’s contents.
The TDE encryption process, as the name indicates, is transparent from the users and requires no change from the application side. Transparent Data Encryption encrypts the physical files at the page level before writing the pages to the disk, and decrypts the files when the pages are read into the memory.
In this article, we will see how we could add a TDE encrypted database to Always On Availability Group site that is already configured. Assume that we have configured the Always On Availability Group AG40VS that contains two replicas: DB41VS and DB42VS. The DB41VS SQL Server is configured as the primary replica and the DB42 SQL server is configured as a Secondary replica. Both DB41VS and DB42VS are configured for Automatic Failover:
TDE_Test is a user database that contains a critical financial data, and on a monthly basis, the backup of that database is sent to the company headquarter office to verify the database backup. So that, we need to make sure that if the backup is stolen or missed, no one can browse its data. To achieve that, the database physical files should be encrypted using the SQL Server Transparent Data Encryption.
To configure the TDE on the TDE_Test database, we should first create a master key in the master database. Only one master key can be created per each SQL Server instance. All user databases that are hosted in the same SQL instance, and have the TDE enabled on it, will share the dependency upon the same master key. The below CREATE MASTER KEY T-SQL statement is used to create the master key under the master database, and this master key will be encrypted by a complex password. It is better to keep a backup of that password in a secure place:
|
1 2 3 4 5 6 7 |
USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD='Az_Y@89|87'; GO |
After creating the master key successfully, the next step now is to create the Certificate that will be used to encrypt the Database Encryption Key. The Certificate will be protected by the previously created master key. The Certificate can be created using the CREATE CERTIFICATE T-SQL statement below:
|
1 2 3 4 5 |
CREATE CERTIFICATE TDE_AVG WITH SUBJECT='TDE_on_AVG_test'; GO |
To make sure that the certificate is created successfully, you can query the sys.certificate system object for all certificates that are created under the current SQL Server instance as below:
|
1 2 3 4 |
SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date FROM sys.certificates; |
The result will show us that the TDE_AVG certificate is created successfully, encrypted by the master key and valid for one year only:
The Certificate is ready now, so we will start working at the database side. We will use the CREATE DATABASE ENCRYPTION KEY T-SQL command to create the TDE_Test database encryption key that is encrypted by the TDE_AVG certificate created previously, specifying the encryption algorithm, which is the Advanced Encryption Standard (AES) in our case with a 128 bit key length as follows:
|
1 2 3 4 5 6 7 8 |
USE TDE_Test GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TDE_AVG; GO |
The last step in configuring the TDE is to turn on the TDE encryption on the TDE_Test database using ALTER DATABASE … SET ENCRYPTION ON T-SQL command below:
|
1 2 3 4 5 |
ALTER DATABASE TDE_Test SET ENCRYPTION ON; GO |
That is it! The Transparent Data Encryption is configured completely on the TDE_Test database. The sys.dm_database_encryption_keys DMV can be used to list all databases with TDE enabled on the databases:
|
1 2 3 4 5 6 7 8 9 |
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 |
The result shows that the TDE is enabled on the TDE_Test database, and the value 3 for the database encryption state means that the database is completely Encrypted. But what about the tempdb? We have not configured the TDE on that database!
If you encrypt any user database using the Transparent Data Encryption, the physical files of the tempdb will be encrypted automatically, as it may contain data from the TDE enabled user database temporarily, while using temporary tables or cursors. In this way, we will maintain full protection for our data using the TDE feature. The operative point is that, the TDE encryption on the tempdb is transparent from that database users and will have a minimal performance impact on the current SQL instance.
As a best practice, it is better to back up the TDE certificate and the private key associated with that certificate after enabling the TDE on a database. In this way, you will be able to restore the database backup files or attach the database data files on another SQL Server instance. The backup can be performed by running the below BACKUP CERTIFICATE T-SQL command, which will backup both the certificate itself and its private key, providing a complex password to encrypt that private key as below:
|
1 2 3 4 5 6 7 8 |
USE master GO BACKUP CERTIFICATE TDE_AVG TO FILE = 'C:\TDE_Cert\TDE_AVG' WITH PRIVATE KEY (file='C:\TDE_Cert\TDE_AVG_Pirvate', ENCRYPTION BY PASSWORD='Lm_s$$15_12') |
The generated backup files can be viewed by browsing the path provided in the script or the SQL instance DATA path by default if you do not provide the path as follows:
At this point, the TDE_Test database is encrypted at the DB41VS SQL Server that acts as the primary replica in the AG40VS Always On Availability Group.
Now we need to add it to the Availability Group in order to replicate it to the DB42VS SQL Server. The Add Database Wizard and New Availability Group Wizard for AlwaysOn Availability Groups do not support adding databases that are already encrypted using the TDE encryption to an Availability Group. If you try to add the TDE_Test database, which is encrypted using TDE, by the Add Database Wizard, the wizard will show you that this is not applicable as the database contains an encryption key as below:
This means that we need to add the database to the Availability group manually. We need to perform full backup and transaction log backup for the TDE_Test database, copy it and restore it with NORECOVERY at the DB42VS secondary replica. But recall again that the TDE_Test database is encrypted using Transparent Data Encryption. If we try to restore the TDE_Test database backup to the DB42VS SQL Server, the restore process will fail getting the below error:
The error indicates that the TDE_Test database is encrypted using a database encryption key that is protected by a certificate, and this certificate is not available on that SQL Server instance. So that, in order to restore this encrypted database backup on a SQL Server instance, the certificate that encrypts the database encryption key should be created on that server. This is why we create a backup for the certificate and its private key when we enable the TDE on our database.
To do that, we should first create a master key on the secondary server that is encrypted by a complex password, using the CREATE MASTER KEY T-SQL command below:
|
1 2 3 4 5 6 |
USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Az_Y@89|87' |
You have the choice to use the same password for the previous master key or use a new password, as the master key is used to encrypt the certificate not the database itself in order to keep it protected. In all cases, the key itself will be different due to how the key generation process is performed.
The next step is to copy the certificate and the private key backup files generated previously from the DB41VS primary replica to the DB42VS secondary server. You can keep it in the default data folder on the secondary serve, or in a custom folder where you should provide that bath in the CREATE CERTIFICATE T-SQL statement. The private key of the certificate should be decrypted by the same password that was used to encrypt it when the backup was created:
|
1 2 3 4 5 6 7 |
CREATE CERTIFICATE TDE_AVG FROM FILE='F:\TDE_Cert\TDE_AVG' WITH PRIVATE KEY ( FILE = 'F:\TDE_Cert\TDE_AVG_Pirvate', DECRYPTION BY PASSWORD='Lm_s$$15_12') |
Now the certificate that encrypts the TDE_Test database is available in the secondary replica. We will create a full back and transaction log backup from the TDE_Test database in the primary replica and copy it to the secondary replica:
|
1 2 3 4 5 6 7 8 9 10 |
BACKUP DATABASE [TDE_Test] TO DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.bak' WITH NOFORMAT, NOINIT, NAME = N'TDE_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [TDE_Test] TO DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.trn' WITH NOFORMAT, NOINIT, NAME = N'TDE_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
On the primary replica we will add the database to the Availability Group using the ALTER AVAILABILITY GROUP… ADD DATABASE T-SQL command as follows:
|
1 2 3 4 5 |
USE master GO ALTER AVAILABILITY GROUP AG40VS ADD DATABASE [TDE_Test] |
You can make sure that the database is added successfully to the Availability Group by expanding the AG40VS Availability Groups node in the DB41VS primary server and you will find the database under the Availability Databases node:
Once the backup files are copied to the secondary server, we will restore the full backup and the transaction log backup on that server using WITH NORECOVERY option:
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] RESTORE DATABASE [TDE_Test] FROM DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 GO RESTORE LOG [TDE_Test] FROM DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 GO |
The restore process will complete successfully this time as the same certificate that encrypt the database encryption key is created on the secondary server. The database is available now on the secondary replica and waiting in RESTORING state.
At the secondary replica side, we need to join the database to the availability group using the ALTER AVAILABILITY GROUP T-SQL command below:
|
1 2 3 4 5 |
USE master GO ALTER DATABASE TDE_Test SET HADR AVAILABILITY GROUP = AG40VS; |
To make sure that the TDE_Test database is replicated to the DB42VS SQL Server, expand the Databases node at the DB42VS SQL Server and you will see the TDE_Test database in synchronized state:
A manual failover test can be performed to make sure that our database, with TDE enabled on it, is added completely to the Availability Group and working fine. Right-click on the AG40VS Availability Group and choose Failover option:
Go through this simple wizard and failover the AG40VS Availability Group to the DB42VS SQL server. The DB42VS is the primary replica now with no issue. You can browse the database and run few SELECT statements to make sure that it is working fine:
Conclusion
Securing and encrypting sensitive data stored in our databases is very important, especially the databases that store the organization’s financial data and customers’ confidential information. SQL Server Transparent Data Encryption is an encryption feature that provides encryption on the database file level, as it encrypts the database data, logs, backup and snapshot files. Also, the database should be added to a high availability and disaster recovery site in order to be available and online for the users all the time.
Always On Availability Groups is the best high availability and disaster recovery choice for us. Adding a database that is encrypted by the TDE feature to the Availability Group by the wizard is not supported. In this article, we described how to do that manually step by step.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021