Transparent Data Encryption

Transparent Data Encryption (TDE) is a powerful feature in SQL Server that helps secure sensitive data by encrypting database files. In other words, it prevents restoring your backup files on a different server if you don’t have appropriate certificate and key. It doesn’t matter if some bad actors try to steal your data or unexperienced users with enough permission are making it vulnerable for exposure… there are few others examples, but I’m not going into details, so let’s say it’s making your data safer. Please note that the scripts provided are only quick overviews. Do not use them on production instances without proper testing. It’s worth mentioning that since SQL Server 2019, TDE is available in the Standard edition as well. You can find more details about TDE on microsoft docs https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16

First step is to create our test database, let’s call it TDE. You can insert some data if you want.

USE master;
GO
CREATE DATABASE [TDE];
GO
USE [TDE];
GO
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (1), (2), (3);

The second step is to create a master key in the master database, if one does not already exist.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperSecretP@ssword123';
CREATE CERTIFICATE TDE WITH SUBJECT = 'TDE Certificate'

Third, we are creating database encryption key with previously created certificate.

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

Now we’re ready to enable encryption on our example “TDE” database.

USE master;
GO
ALTER DATABASE [TDE] SET ENCRYPTION ON;

Encrypting data may take some time depending on size of your database. You can easily check encryption progress or status using the script provided below. In our case database is small enough to encrypt it almost immediately.

SELECT
	db.name,
	db.is_encrypted,
	dm.encryption_state,
	dm.percent_complete,
	dm.key_algorithm,
	dm.key_length
FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id

The most important step now is to create a backup of the certificate and key, and store it in a secure location.

use master;
GO
BACKUP CERTIFICATE TDE 
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\TDE'
WITH PRIVATE KEY (file = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\TDE_certKey.pvk',
ENCRYPTION BY PASSWORD = '$uperSecr3tPassw0rd');

Next, let’s back up our TDE database, move it to a different SQLL instance, and attempt to restore it. This will fail because we don’t have the necessary certificate and key in place.

That’s why we need to restore our certificate and key from previously created backup.

CREATE CERTIFICATE TDE
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\TDE'
WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\TDE_certKey.pvk',
DECRYPTION BY PASSWORD = '$uperSecr3tPassw0rd');

Once the certificate and key have been successfully restored, we should be able to restore the encrypted database without any errors. hurray!

If you have any remarks or questions, feel free to contact me or leave a comment.