Introduction

Transparent Data Encryption (TDE) is one of the key security features available in SQL Server from SQL Server 2008 onwards. Using this feature, the ‘data at rest’ in the physical files for the database, are protected from unauthorized access if the files are copied, or the physical media is stolen. TDE is available with SQL Server, Azure SQL Database, and Azure Synapse Analytics (SQL DW) data files.

How TDE Works

When data is read from disk, SQL Server decrypts the entire block, making the data visible to the database engine. When data is inserted or updated, the SQL Server database encrypts the entire block written to disk. TDE performs this real-time I/O encryption and decryption of both the data and log files to protect data at rest. The encryption is transparent to any user or application querying the database.

Once TDE is enabled for a user database, then the tempdb database in the instance will also be encrypted, as the database may contain the temporary user objects, internal objects, and row versions which can expose the sensitive data.

Fig 1 – From the SQL Server documentation

er database, we create a Database Master Key (DMK), a Certificate, then a Database Encryption Key and turn on the encryption in the user database. The steps below outlines the steps.

Create a  Database Master Key (DMK)

The first step is to create a DMK in the master database. The below DDL command (CREATE MASTER KEY) is used for the DMK creation. The password used in the script should be strong as per your password policy and kept safe . It is required to recover the user database in another server with the same master database restored.

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd';  
GO
               

Fig 2 – Master Key creation

Create the certificate

A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. The certificates can be a self signed certificate or externally signed certificate (by a third party Certificate Authority like Verisign, Symantec etc). We cannot create an externally signed certificate with SQL Server but is possible to import an existing externally signed certificate.

The CREATE CERTIFICATE statement can load a certificate from a file, a binary constant, or an assembly. This statement can also generate a key pair and create a self-signed certificate. In this scenario , we are using self signed certificate as an external validation is not quite often used while working with SQL Server.

The below script will create the certificate ‘TDETest_Cert’ in the master database.

USE master;  
GO
CREATE CERTIFICATE TDETest_Certificate WITH SUBJECT = 'TDETest_Cert',
START_DATE = '2020-02-02',
EXPIRY_DATE = '2020-12-12';
GO

Arguments :

SUBJECT =  Field in the metadata of the certificate as defined in the X.509 standard.

START_DATE = Date on which the certificate becomes valid.

EXPIRY_DATE = Date on which the certificate expires.

Once the script is executed, a certifcate named ‘TDETest_Certificate’ will be created under the Certificates folder under Security folder in the master database.

Fig 3 – Certificate creation

However, it should be noted that the certificate created above should be backed up to a secure location. If the server ever goes down and if you need to restore the database in another server , you will have to import the certificate to the new server.

Create the Database Encryption Key (DEK)

The DEK is required to be created in the user database before the database can be encrypted by using Transparent Database Encryption (TDE). When a database is transparently encrypted, the whole database is encrypted at the file level, without any special code modifications.

The Certificate created above encrypts the DEK in the user database.

USE [TDETest]
GO
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDETest_Certificate;
GO

The arguments used in this code are as follows:

• ALGORITHM : This is the encryption algorithm that is used for the encryption key. Beginning with SQL Server 2016, all algorithms other than AES_128, AES_192, and AES_256 are deprecated. Using older algorithms is not recommended, and to use one, you must set the database to database compatibility level 120 or lower.
• ENCRYPTION BY SERVER CERTIFICATE : This is the object name of the encryptor, which encrypts and protects.

Once the script is executed, we will receive the below warning message in Fig 4 stating that the certificate created for encrypting database has to be backed up in a secure location as it is required to restore or recover the TDE enabled database on another server.

Fig 4 – Database Encryption Key creation

Enable Encryption

The final step is to turn on the encryption database option in the database properties. The code shown below will set the encryption state to ON for the user database.

-- Alter the database to enable transparent data encryption.  
ALTER DATABASE AdventureWorks2016
SET ENCRYPTION ON;  
GO  

The encryption progress of the database can be checked by querying the DMV sys.dm_database_encryption_keys  with the below query:

select db_name(database_id) as DBName,encryption_state,percent_complete from  sys.dm_database_encryption_keys

The encryption_state value from the above query will be an integer value that can be interpreted with these values:

• 0 = No database encryption key present, no encryption
• 1 = Unencrypted
• 2 = Encryption in progress
• 3 = Encrypted
• 4 = Key change in progress
• 5 = Decryption in progress
• 6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

Fig 5 – Turn ON encryption in user database

Once the script execution is complete, the Encryption Enabled property will be changed to True, as shown in the figure below.

Restore a Backup of a TDE Database to Another Server: Level 2 of the Stairway to TDE

In Level 1 of this Stairway, we discussed how to configure TDE in a user database using a Database Master Key and Certificate. In this level, we explain the steps for backing up and securing this certificate, as well as how to restore the database on another server instance.

Backup the Certificate and Private Key

We need to use the BACKUP CERTIFICATE statement to take the backup of the certificate. We got a message after creating the DEK in Level 1 that we needed a certificate backup. This is how we perform the backup.

BACKUP CERTIFICATE TDETest_Certificate
TO FILE = 'D:\tde\TDETest_Cert'
WITH PRIVATE KEY (file='D:\tde\TDETest_CertKey.pvk ', ENCRYPTION BY PASSWORD='StrongPasswordgoeshere')
GO

the arguments are:

• TO FILE = ‘path_to_file‘  –  complete path, including file name, of the file in which the certificate is to be saved.
• WITH PRIVATE KEY        –  specifies that the private key of the certificate is to be saved to a file.
• ENCRYPTION BY PASSWORD = ‘encryption_password‘ –  password used to encrypt the private key before writing the key to the backup file.

This script will create a TDETest_Cert file and TDETest_CertKey.pvk private key in the location ‘D:\tde\’ in source server.

Fig 1: Certificate and private key backup location

Note:  The BACKUP CERTIFICATE command does not have the option to overwrite the existing file. Hence,TDETest_Cert file should not exist in the location specified while taking the certificate backup.

If we are restoring the database without the TDE certificate and private key, we will encounter the certificate missing error as the database is secured with the certificate.

Restore TDE enabled database in another instance

This section describes the steps to backup and restore the TDE enabled database backup to another instance. The steps involved include the backup of the database, the restore of the certificate backup to the destination server, and finally the restore of the database backup. Some of these steps only need to be done once, as subsequent restores will complete without issue, as long as the certificate remains the same.

Backup the Database

We start by issuing the BACKUP DATABASE statement to take a full backup of the TDE enabled database, TDETEST.

BACKUP DATABASE [TDETEST] TO  DISK = N'D:\tde\TDETest.bak'

Fig 2: Output – Database Backup

Restore this database backup in the new instance 

We can issue the RESTORE DATABASE statement to restore the database to another server instance. While performing the restore, you will encounter the below error if the certificate that we created in the first section is not imported. The reason is the certificate that was used to encrypt the database on the source server is not available on the destination server instance, and thus the data cannot be decrypted.

We can see this when we run this command. The results in Fig 3 show the restore cannot complete.

USE [master]
RESTORE DATABASE TDETest FROM  DISK = N'D:\tde\TDETest.bak'

Fig 3: Output – Restore error due to certificate missing

The below steps will show how to restore the TDE enabled database backup to the new server instance. The steps to be followed in the destination server are shown below.

• Create a Database Master Key (DMK)
• Certificate creation
• Restore TDE enabled database

Database Master Key Creation

The first step is to create a Database Master Key (DMK) in the master database on the destination server with a password. This password doesn’t need to be the same that was used in the source server for DMK creation. If there is already a DMK present in the destination server, there is no need to create another one.

USE master; 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DifferentP@$$w0rd'; 
GO

Fig 4: DMK creation in destination server

Once this step has been completed, it doesn’t need to be repeated for future restores.

Certificate creation

The certificate and the private key that we created from the source server with the BACKUP CERTIFICATE statement have to be copied to the destination server instance. Once the files have been copied, use the CREATE CERTIFICATE statement to add the certificate to the master database in the destination server. Note the FROM FILE and FILE parameters point to the certificate and private key files. The paths should match the location to which you copied these files.

USE MASTER
GO
CREATE CERTIFICATE TDETest_Certificate
FROM FILE = 'D:\tde\TDETest_Cert'
WITH PRIVATE KEY (FILE = 'D:\tde\TDETest_CertKey.pvk',
DECRYPTION BY PASSWORD = 'StrongPasswordgoeshere' );

FROM FILE  –  complete path, including file name, of the certificate. The location can be in the destination server or a location accessible to both source and destination servers.

WITH PRIVATE KEY        –  specifies that the private key of the certificate.

DECRYPTION BY PASSWORD = ‘key_password‘ –  password used to decrypt the private key.

The below figure shows the expected result after execution.

Fig. 5: Importing the certificate

Once this step has been completed once, it does not need to be repeated for future restores.

Restore the TDE enabled database

Once the certificate and the private key get imported to the destination server, the restore process will be successful.

USE [master]
RESTORE DATABASE [TDETest] FROM DISK = N'd:\tde\TDETest.bak' WITH FILE = 1, 
MOVE N'TDETest' TO N'E:\SQL\MSSQL14.MSSQLSERVER\MSSQL\DATA\TDETest.mdf', 
MOVE N'TDETest_log' TO N'E:\SQL\MSSQL14.MSSQLSERVER\MSSQL\DATA\TDETest_log.ldf', NOUNLOAD,REPLACE, STATS = 5
GO

Fig 6: Output showing restore as successful

Query To Monitor The Status Of TDE Encryption Of A SQL Server Database

Transparent Data Encryption (TDE) is Microsoft’s solution to encrypting SQL database files. This provides an at-rest solution for securing your database and backups.

Depending on the size of your database, TDE can take quite a while to encrypt or decrypt the database and SQL Server Management Studio does not provide a great way of tracking the overall process.

Fortunately this type of solution can easily be scripted to help you better track the overall progress of applying or removing TDE from a database. The following script should provide you with the relevant information regarding TDE and the status of the encryption or decryption process.

Below is an illustration of the output that is generated by the script. In the example below you can see that I have 3 certificate encrypted databases. If I were in the process of encrypting or decrypting the database you would see a value listed within the percent_complete column.