Encrypt database using Transparent Data Encryption (TDE) technology in SQL Server

The Transparent Data Encryption (TDE) encryption technology protects the data in the database by encrypting the underlying files of the database, and not the data itself. So not just the sensitive data but all data in the database will be encrypted. This prevents the data from being hacked.

Assume you are having a database SampleDatabase with credit card details. If you open the backup file in Notepad and search for any card number, you should be able to see the actual data in the backup file. Hence anyone having access to the backup file can read the actual data, without restoring it.

To protect the sensitive data from users who do not have appropriate permission, encrypt the data using TDE feature as given below:

Step 1:

Create a Master Key or Encryption Key in the master database.

Use master;

Create Master Key Encryption By Password = 'C0mplexP@ssw0rd!';

Step 2:

Create a certificate for use as the database encryption key (DEK) protector and is protected by the DMK.

Create Certificate Cert4TDE

With Subject = 'TDE Certificate';

Step 3:

Create a database encryption key (DEK) encrypted with the certificate created in previous step. The supported encryption algorithms are AES with 128-bit, 192-bit, or 256-bit keys or 3 Key Triple DES. The created Master Key and the Certificate will be stored in the master database in an encrypted format.

Use SampleDatabase

Create Database Encryption Key With Algorithm = AES_256 

Encryption By Server Certificate Cert4TDE

Step 4:

Encrypt the data using the Master Key created in previous step.

Alter Database SampleDatabase

Set Encryption ON

Step 5:

We can verify the encryption using the below database query.

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

GO

From the result we can see the SampleDatabase database is encrypted.

name	is_encrypted	encryption_state	key_algorithm	key_length	percent_complete
SampleDatabase	1	3	AES	256	0

Step 6:

Repeat Step 3 to backup the database again (with encrypted data) and open the backup file in Notepad. Now search the same card number and see that the data in the backup file is encrypted and secured.