DBA - How To Enable Transparent Data Encryption (TDE) On a SQL Server Database Step by Step

Here are the steps to enable Transparent Data Encryption or TDE on SQL Server Database.

Step 1: 

The very first step is to Create Database Master Key if it does not exits.The below query can be used to find out if key already exists.

USE MASTER

GO

SELECT * FROM   sys.symmetric_keys

WHERE  name LIKE '%DatabaseMasterKey%'

If above statement does not return you any record, then you need to create Master Key by using below statement.

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='P@ssword1' 

Provide strong password.
Once you have created the Database Master Key, you can backup by using below script

BACKUP MASTER KEY TO FILE='FilePath' 

ENCRYPTION BY password='MyStrongPassword'

Instead of creating new Database Master Key, If you have already one backed up and you want to restore, you can use below script to Restore.

RESTORE MASTER KEY FROM FILE='FilePathforBackupKey' 

Decryption BY Password='MyStrongPassword' 

ENCRYPTION BY password='ProvideNeworSamePassword'

Step 2:

Next step is to Create a Certificate in Master Database protected by Master Key by using below script.

USE [master]
GO

CREATE CERTIFICATE DBName_TDECert 

WITH SUBJECT = 'MyDB_TDECert 09/12/2014',

EXPIRY_DATE = '08/19/2015';

Provide the name of certificate as you wish and other information according to your requirements. Once the certificate is created, you can take the backup and master key by using below script.

USE [master]
GO

BACKUP CERTIFICATE DBName_TDECert  TO FILE = 'FilePath\FileName.cer'

 WITH PRIVATE KEY (FILE = 'FilePath\FileName.key',

 ENCRYPTION BY PASSWORD = 'MyStrongPassword');

GO

Step 3:

The third step is to Create Encryption Key on Database by using Certificate that we created in Step 2. Use the below Script to create encryption Key.

USE [DataBaseName]
GO

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 

ENCRYPTION BY SERVER CERTIFICATE DBName_TDECert

Step 4:

This is last step in which you will Enable Transparent Data Encryption on Database. Use the below statement to Enable TDE on Database 

USE [DataBaseName]
GO

ALTER DATABASE [DatabaseName]SET ENCRYPTION ON

GO

One you are done with all steps, let's verify if the TDE is enabled on Database. There are two ways you can do to. 

Right click on Database and go to properties and you will see it under options as shown in below fig.

Fig 1: How to Check if TDE is enabled on a Database in SQL Server

As we can see that the Encryption Enabled=False that means that is not enabled. If It would be True that means the TDE is enable on a Database.

Or you can use below script to find out if TDE is on database/s.

SELECT DB_NAME(database_id),
       encryption_state

FROM   sys.dm_database_encryption_keys