Setting up TDE with Certificates in SQL Server

This article will explain in setting up Transparent Data Encryption Feature in SQL Server with a step by step illustration in a workstation . This will also show what will be the outcome if the backup of a TDE encrypted database has been compromised by some unauthorised hands and try to recover the data out of it.

As per the Books Online, following diagram gives clear cut idea on the architecture of Transparent Database Encryption.

For the demo purpose we are using a fresh

SQL Server instance named <WorkstationName>\PLAYSTATION

DB name : TDETest

The key part or the root of the TDE hierarchy in SQL Server is the Service Master Key which got generated the first time the SQL Server is started. The Service Master Key is protected and encrypted in the OS Level by Local Machine Key using the DPAPI (Data Protection API). The Local Machine Key used by the DPAPI is derived from the SQL Server Service account and Computer’s credentials.

The second step is to have a database Master Key (DMK) which is responsible for creating the required certificate in the master database. The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database. Service Master Key (SMK) encrypts the Database Master Key (DMK).

Creation of Database Master Key(DMK)

Creation of Database Master Key(DMK) 

USE master; 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd'; 
GO

Next step is to create a CERTIFICATE in the master database . Database Master Key by protecting the certificate’s private key in master database.

USE master; 
GO
CREATE CERTIFICATE TDETest_Certificate WITH SUBJECT = 'TDETest_Cert';
GO

So far, we have the Service Master Key, Database Master Key and the CERTIFICATEs with us.

Next step is to create a Database Encryption key under the user database which is  responsible for transparently encrypting a database. Certificate encrypts the Database encryption Key in the user database. Choose the Algorithm parameter based on your requirement. I use AES_256 here for this demo. (Please Note : Beginning with SQL Server 2016, all algorithms other than AES_128, AES_192, and AES_256 are deprecated. To use older algorithms (not recommended) you must set the database to database compatibility level 120 or lower. )

USE [TDETest]
GO

CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDETest_Certificate; 
GO

You must have noted that while creating the Database Encryption key, a message got displayed on the Messages tab that the certificate used for creating the Database encryption Key has not been backed up. The reason it is displayed here is nothing but if you want to restore the backup or move the data into another server , you wont be able to perform it unless you have the Certificate and the Private key backup with you.

Last part of this article will explain how to take the backup of the Certificate and the private keys and use it to restore the database into another server.

Final step to set up the TDE in the database is to Set the database to use encryption.

ALTER DATABASE [TDETest]
SET ENCRYPTION ON; 
GO