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;Code language: SQL (Structured Query Language) (sql)

Next, switch to the test_db:

use test_db;Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

Finally, select the data from the customers table:

select * from customers;Code language: SQL (Structured Query Language) (sql)

Encrypt the database #

First, switch to the master database:

USE master;Code language: SQL (Structured Query Language) (sql)

Second, create a master key:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='kKyDQouFJKLB7ymBGmlq';Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

Fourth, switch to the test_db:

USE test_db;
GOCode language: SQL (Structured Query Language) (sql)

Fifth, create the database encryption key:

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;Code language: SQL (Structured Query Language) (sql)

Sixth, enable encryption for the test_db database:

ALTER DATABASE test_db
SET ENCRYPTION ON;Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

Third, restore the database:

RESTORE DATABASE test_db 
FROM  DISK = N'C:\backup\test_db.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5Code language: SQL (Structured Query Language) (sql)

Now, it should work as expected.

Was this tutorial helpful?