TDE encryption and decryption

 

To implement TDE you should follow the following steps:
1. Create a master key
2. Create or obtain a certificate protected by the master key
3. Create a database encryption key and protect it by the certificate
4. Set the database to use encryption

Step #1 Create a master key.

"The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the service master key and stored in both the database and in master.

To create a Master key you need to execute the followin Tsql comand.

create database test USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterkey1';

Step #2 Validate if a master key has been created.

To validate if the master key has been created you can use the folllowing TSQL statement.

USE Master;
select * from sys.symmetric_keys;

Figure1. Query result showing Master data key has been created (click image to enlarge)

Step #3. Create a Database Certificate

"A certificate is a database-level securable that follows the X.509 standard and supports X.509 V1 fields. CREATE
CERTIFICATE can load a certificate from a file or assembly. This statement can also generate a key pair and
create a self-signed certificate.

The following script should allow you to create a database certificate
Use Master CREATE CERTIFICATE mytestcert
WITH SUBJECT = 'Mytestcertificate'

Step #4. Validate that a Database Certificate has been created

If you want to check if your database has certificate you can inspect it through Management studio
or query the sys.certificates system tables as shown below.

 

 

Figure 2. A database certificate as shown in management studio (click on the image to enlarge)

Step #5. Create a Database Encryption Key

"A database encryption key is required before a 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 or asymmetric key that is used to encrypt the database encryption key must be located in the master system database.

To create a database encryption Key You can use the following codes:

use test CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE [mytestcert]

Step #6. Set the Database to use Encryption

 

The final step is to run an alter database command to turn on the encryption. To do this you will need to run the following commands

ALTER DATABASE test
SET ENCRYPTION ON;