Implement Transparent Data Encryption(TDE) in SQL Server

Transparent Data Encryption(TDE) is used in SQL Server to protect data by encrypting physical files of the database.In TDE only files (ldf, mdf and bak) will be encrypted, but not the data. In this TDE, database will be encrypted using a Database Encryption Key. This database encryption key will be protected by a certificate and master key which are present in Master Database. To get the database information decrypted on the fly, a user connecting to the database should be accompanied by the Instance level Service Master key, this preserves the security in tact. As Service master key is at instance level, physical data files cannot be taken to other instances and get decrypted, that way data is protected.

Please follow below steps to encrypt a database –

IMP: Make sure your Database edition is one of – Enterprise, Developer, Evaluation editions. Express Edition doesn’t support TDE.

/**************************************** 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.
*************************************************************************************************/
USE [master];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';


/**************************************** Step 2 ************************************************
Create a Certificate. CREATE CERTIFICATE can load a certificate from a file or assembly. It can
also be used to create a self signed certificate.
*************************************************************************************************/
CREATE CERTIFICATE AdventureWorksTDECert
WITH SUBJECT = 'ADWorksTDE'


/**************************************** Step 3 ************************************************
Create a Database Encryption Key.A database encryption key is required before a database can be 
encrypted by using Transparent Database Encryption (TDE).
*************************************************************************************************/
USE [AdventureWorks2012]
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE [AdventureWorksTDECert]


/**************************************** Step 4 ************************************************
Encrypt database.
*************************************************************************************************/
ALTER DATABASE [AdventureWorks2012]
SET ENCRYPTION ON;

You may also like...