In 2006, British mathematician and data science entrepreneur Clive Humby famously coined the phrase “Data is the new oil” which simply translates to data surpassing oil as the world’s most valuable commodity.This obviously brought with it the very real threat posed by cyber criminals who stole valuable information for financial gains.

Data encryption has since then proved to be a robust solution for this problem. Encryption protects confidential data from attackers and when thinking about it, you should consider three cases:

• Encrypting your data at rest
• Encrypting your data in transit
• Encrypting your data in use

In this blog, I will show you how you can implement Transparent Data Encryption (TDE) for Data at rest. We will cover data in transit and data at rest in my subsequent blogs. But for now, let’s start with understanding TDE.

Transparent Data Encryption (TDE) is a tool that is primarily used to protect data by encrypting the physical files or ‘data at rest’, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database) in SQL Server 2008 enterprise edition using an Encryption key management (EKM) system.

But, before we move any further, I urge you to make note of the eligible editions with which you can deploy this encryption. Here’s a list:

• SQL 2016 Evaluation, Developer, Enterprise
• SQL 2014 Evaluation, Developer, Enterprise
• SQL Server 2012 Evaluation, Developer, Enterprise
• SQL Server 2008 R2 Datacenter, Evaluation, Developer, Enterprise, Datacenter
• SQL Server 2008 Evaluation, Developer, Enterprise

Choosing a correct version of SQL server that allows TDE is key. Some may say that this is an expensive feature since it needs enterprise editions. However, for the sake of testing and development, a developer edition works just as well.

Hierarchy of Encryption

Here is what the hierarchy of encryption looks like:

So, based on that, the tool that we will be using today is an Advanced Encryption Standard provided by SQL Server TDE.

With the help of this, we will take on a solution approach as follows:

For TDE Implementation:

1. SQL server provides Transparent Data encryption (TDE) feature with the Enterprise edition of SQL server 2008 version & above.
2. This is used to ensure data security by encrypting all physical files including backup, mdf and ldf
3. The list of keys & certificates to be created as a part of TDE implementation on the SQL server database are:
   1. Service Master Key: Protects the database master key created at SQL server instance setup
   2. Database Master Key: Database Master key certificate’s private key in Master Database
   3. Master Certificate: Guards the database encryption key in the User database
   4. Database Encryption Key: Connects the certificate with the actual database

Based on this approach, the performance impact can be observed as follows:

Encryption is CPU intensive so applications with high CPU usage will suffer a performance loss of approximately 28%.

Implementing TDE on database

Here are the 5 steps that we must perform to use TDE in SQL server.

1. Create a Master Key
2. Create a certificate protected by the master key
3. Backup the certificate
4. Create a database encryption key and protect it by the certificate
5. Enable the encryption on the database

Now that we have our approach in place, let’s see an example that will help us understand better. We will look at TDE on Source Server (Server1) as follows.

1. Create a Master Key [Set the complex password]

create master key encryption by password=’P@$$w0rd12’/*P@$$w0rd12= Any Complex Password Can be given */

select * from sys.symmetric_keys

2. Create a certificate protected by the master key.

create certificate SQLTDECert /*SQLTDECert is the name of the Cerficate */
with subject='Certificate to protect TDE key'

As soon as the certificate is created, it is always recommended to take the backup of the certificate.

3. Backup the Certificate

BACKUP CERTIFICATE SQLTDECert
TO FILE = 'D:\TestTDE\MSSQLCert'/* Creates Cerficate File at specified path */
WITH PRIVATE KEY (file='D:\TestTDE\MSSQLCertKey', /*Creates Private Key File at the specified Path */
ENCRYPTION BY PASSWORD='P@$$w0rd122') /* Password is important to remember */
select * from sys.certificates

4. Create Database Encryption Key (DEK)

use TestTDE
Go
create database encryption key
with algorithm=AES_128    /* By using AES-128 algorithm the database will be encrypted */
encryption by server certificate SQLTDECert;
Go

5. Enable the encryption on the database

Alter database TestTDE
set Encryption on;
Go

Steps to Perform on the Destination Server (Server2)

1. Copy the Certificate and Private key backup files to the destination server.

Note: The path of the source and destination should be same.

2. Create a master key

create master key encryption by password='P@$$w0rd12'/* Any Complex Password Can be given */

3. Restore/Create certificate from the certificate backup file

CREATE CERTIFICATE SQLCertificateTDE
from FILE = 'D:\TestTDE\MySQLCert'
WITH PRIVATE KEY (file='D:\TestTDE\MySQLCertKey',
DECRYPTION BY PASSWORD='P@$$w0rd122'); /*Password Should be same as the Source */
Go

4. Take the backup of the database
5. Restore on the destination

Enable TDE for Multiple Databases

Repeat the steps 4 and 5 depending on how many databases are needed to implement TDE.

Key Rotation in TDE

When we create a certificate, by default the expiry date of the certificate will be one year.

To rotate the certificate, the first thing we need to do is create a new certificate as in step 2 of the source.

USE TDE_New
GO
ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE TDE_New

Next, backup the certificate and recreate it on the destination server.

Here, handling restores is a challenging task.

e.g.: If we rotate the certificates on Tuesday afternoon, and then we restore Monday night’s backup, we will need the older certificate on the system. If we need a restore on Wednesday, we should use the new certificate.

So before rotating the Keys, it is recommended to stop the backup jobs.

In conclusion, we have successfully seen how we can use SQL server to implement Transparent Data Encryption (TDE) for physical data to gain an added layer of protection for data at rest.

To learn more about deriving insights from data, peruse through our Big Data services and reach out to us at Nitor Infotech to know how we leverage Blockchain to ensure secure and affordable transaction tracking.