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:
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:
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.
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:
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%.
Here are the 5 steps that we must perform to use TDE in SQL server.
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.
create master key encryption by password=’P@$$w0rd12’/*P@$$w0rd12= Any Complex Password Can be given */
select * from sys.symmetric_keys
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.
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
create database encryption key
with algorithm=AES_128 /* By using AES-128 algorithm the database will be encrypted */
encryption by server certificate SQLTDECert;
Alter database TestTDE
set Encryption on;
Note: The path of the source and destination should be same.
create master key encryption by password='P@$$w0rd12'/* Any Complex Password Can be given */
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 */
Repeat the steps 4 and 5 depending on how many databases are needed to implement 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.
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.
Subscribe to our monthly newsletter!