Sample Code For Transparent Data Encryption - TDE Sql Server 2008 and Above

1. Create a master key for the database (must be in master database).
2. Create a certificate that's protected by the master key. 
3. Create a special key that's used to protect the database. This key is called the database encryption key (DEK) and you secure it using the certificate.
4. Enable encryption.

 Sample Code given below demonstrates how to enable TDE on a sample database named “Raptor_Cloud.

Listing 1: Code to Enable TDE on a Database

USEMASTER

GO

CREATEMASTERKEYENCRYPTIONBYPASSWORD='R@pt0r';

--DROP MASTER KEY

CREATECERTIFICATE RaptorCloudCertificate WITHSUBJECT='Raptor Cloud Certificate';

SELECT*FROM [sys].[symmetric_keys]

SELECT*FROMsys.certificates

GO

BACKUPCERTIFICATE RaptorCloudCertificate

TOFILE='\\ws-cgomes\Shared\RaptorCloudCertificate.bak'

WITHPRIVATEKEY (

FILE='\\ws-cgomes\Shared\RaptorCloudCertificate.pvk',

ENCRYPTIONBYPASSWORD='R@pt0r');

GO

USE Raptor_Cloud

GO

--DROP DATABASE ENCRYPTION KEY

CREATEDATABASEENCRYPTIONKEY

WITHALGORITHM=AES_256

ENCRYPTIONBYSERVERCERTIFICATE RaptorCloudCertificate

SELECT name,database_id

FROMsys.databases;

ALTERDATABASE Raptor_Cloud

SETENCRYPTIONON;

-- Command to know status of database encryption  

SELECT*FROMsys.dm_database_encryption_keys

-- Explaination of different encryption_state  

/*

0 = No database encryption key present, no encryption

1 = Unencrypted

2 = Encryption in progress

3 = Encrypted

4 = Key change in progress

5 = Decryption in progress

6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.

*/

GO

ALTERDATABASE Raptor_Cloud

SETENCRYPTIONOFF;

SELECT*FROMsys.dm_database_encryption_keys

GO

DROPCERTIFICATE RaptorCloudCertificate

GO

CREATECERTIFICATE RaptorCloudCertificate

FROMFILE=N'\\ws-cgomes\Shared\RaptorCloudCertificate.bak'

WITHPRIVATEKEY

(FILE=N'\\ws-cgomes\Shared\RaptorCloudCertificate.pvk'

,DECRYPTIONBYPASSWORD=N'R@pt0r'

);

GO

To Know status of encryption and certificates

SELECT*FROMsys.certificates

SELECT* FROMsys.dm_database_encryption_keys