SQL Server TDE

TDE stands for Transparent data encryption. TDE allows you to encrypt SQL Server data files. This encryption is called encrypting data at rest.

In this tutorial, we’ll create a sample database, encrypt it using TDE, and restore the database to another server.

Create a test database

First, create a test database called test_db:

create database test_db;

Next, switch to the test_db:

use test_db;Code language: PHP (php)

Then, create the customers table:

create table customers(
	id int identity primary key,
	name varchar(200) not null,
	email varchar(200) not null
);Code language: JavaScript (javascript)

After that, insert some rows into the customers table:

insert into customers(name, email)
values('John Doe','[email protected]'),
      ('Jane Doe','[email protected]');Code language: JavaScript (javascript)

Finally, select the data from the customers table:

select * from customers;Code language: JavaScript (javascript)

Encrypt the database

First, switch to the master database:

USE master;Code language: PHP (php)

Second, create a master key:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='kKyDQouFJKLB7ymBGmlq';Code language: JavaScript (javascript)

For the password, you should use a very strong one.

Third, create a certificate protected by the master key:

CREATE CERTIFICATE TDE_Cert
WITH SUBJECT='Database_Encryption';Code language: JavaScript (javascript)

Fourth, switch to the test_db:

USE test_db;
GOCode language: PHP (php)

Fifth, create the database encryption key:

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

Sixth, enable encryption for the test_db database:

ALTER DATABASE test_db
SET ENCRYPTION ON;

Seventh, check the encryption progress and status:

SELECT 
	d.name,
	d.is_encrypted,
	dek.encryption_state,
	dek.percent_complete,
	dek.key_algorithm,
	dek.key_length
FROM sys.databases as d
INNER JOIN sys.dm_database_encryption_keys AS dek
	ON d.database_id = dek.database_idCode language: PHP (php)

Output:

The is_ecrypted 1 means the database is encrypted.

The encryption state has one of three values:

0 – not encrypted
1 – the encryption is in progress
3 – the encryption was completed

Eighth, back up the certificate. It’s important to note that you’ll need this certificate to restore the database to another database server:

BACKUP CERTIFICATE TDE_Cert
TO FILE = 'c:\cert\TDE_Cert'
WITH PRIVATE KEY (file='c:\cert\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='kKyDQouFJKLB7ymBGmlq');Code language: PHP (php)

Note that the path c:\tde_cert must exist on the database server. It’s not the path on your local computer.

Finally, backup the database. This step is optional.

BACKUP DATABASE test_db
TO  DISK = N'c:\backup\test_db.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Test DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GOCode language: JavaScript (javascript)

Restore the database to another server

First, attempt to restore the database without the certificate:

RESTORE DATABASE test_db 
FROM  DISK = N'C:\backup\test_db.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5;Code language: JavaScript (javascript)

It failed as expected. Here’s the error message:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xA1456C76093E2E1E1AE0F1E1A57C29D9755C32C3'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Code language: JavaScript (javascript)

Second, create a certificate from the file and password that we generated in the encryption step:

CREATE CERTIFICATE TDE_Certificate   
FROM FILE = 'C:\cert\TDE_Cert'
WITH PRIVATE KEY   
(  
    FILE = 'C:\cert\TDE_CertKey.pvk',  
    DECRYPTION BY PASSWORD = 'kKyDQouFJKLB7ymBGmlq'  
);Code language: PHP (php)

Third, restore the database:

RESTORE DATABASE test_db 
FROM  DISK = N'C:\backup\test_db.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5Code language: JavaScript (javascript)

Now, it should work as expected.

Was this tutorial helpful?