How to Apply Transparent Data Encryption (TDE) in SQL Server

Applies to:

- Imagicle UC Suite (any version)
- Microsoft SQL Server 2016, 2017, 2019 Standard/Enterprise Edition

Description

Use this procedure if you need to apply Transparent Data Encryption (TDE) to MS-SQL Server. TDE secures data in:​

• Database Data Files
• Database Transactional Log Files
• Database Snapshots
• Database Backups
• TempDB

Here you can find more information about what TDE is and how it works.

Prerequisites

Before to proceed, check below points:

• Imagicle database is already created
• You have access to MS SQL Server Management Studio (or another SQL browser tool)

How to configure TDE

This is the procedure summary to apply TDE encryption to the Imagicle database:

• Create Database Master Key
• Create Certificate
• Backup Certificate (not mandatory, but strongly suggested)
• Create Database Encryption Key
• Enable Encryption

Create Database Master Key (DMK)

The first task implies Database Master Key (DMK) creation, which is a symmetric key to protect other keys. Thanks to this key, you can encrypt all the encryptable objects within the database, such as Symmetric Keys, Asymmetric Keys and Certificates.

USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<ChooseAVerySecurePassword>';

Create Certificate

Now you should create the certificate which is protected by previously generated Database Master Key. This certificate protects the the Database Encryption Key (DEK) created in the next steps.

USE master;
CREATE CERTIFICATE MyImagicleDBCertificate WITH SUBJECT= 'Imagicle DEK Certificate';

Backup Certificate

This task, even though not mandatory, is very important, because if the certificate gets lost, all the encrypted data are lost and there will be no way to recover them.

BACKUP CERTIFICATE MyImagicleDBCertificate
TO FILE = 'C:\CertBackup\ImagicleDEKCertificate.cer'
WITH PRIVATE KEY
(
FILE = 'C:\CertBackup\ImagicleCertPrivateKey.key',
ENCRYPTION BY PASSWORD = '<ChooseAVerySecurePassword>'
);

Create Database Encryption Key

Now you need to create the main key, which is the Database Encryption Key (DEK). This key is protected by the certificate generated at the steps above. The DEK is stored in the database itself and you can have only one DEK in each database. You can adopt different encryption algorithms: in below query sample, AES_256 is used and Imagicle database is named "IAS", which is the default name. If you are using another name, please remember to amend the query accordingly.

USE IAS;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyImagicleDBCertificate;

Enable Encryption

As last task, you should turn the encryption on for the Imagicle database. The following query starts a thread to encrypt the entire database using the Database Encryption Key. In below query sample, the Imagicle database is named "IAS" (default name). If you are using another name, please remember to amend the query accordingly.

ALTER DATABASE IAS SET ENCRYPTION ON;

The encryption task is scheduled on background threads by SQL Server.

How to monitor encryption task

Starting from SQL Server 2019, it is possible to monitor the encryption process and suspend/resume it as well.
To check the encryption status, please run this query (assuming Imagicle database is named "IAS"):

SELECT db_name(database_id) as db_name, percent_complete, * FROM sys.dm_database_encryption_keys WHERE db_name(database_id)='IAS'

Click here for more information about the meaning of the columns.

The following query suspends the encryption (Imagicle database name is "IAS"):

ALTER DATABASE IAS SET ENCRYPTION SUSPEND;

The following query resumes the encryption (Imagicle database name is "IAS"):

ALTER DATABASE IAS SET ENCRYPTION RESUME;