Being part of the database engineering team, we were given a task to setup disaster recovery program for some of our SQL Servers hosting critical applications. After a few discussions, we have planned to make use of log shipping to sync data between the two data centers. This requires us to copy the backup files to the Disaster Recovery (DR) site for the initial setup. But in a little while, we realized that moving 30 TB of data over network at approximately 12 mbps might not be a workable option considering 45 days of wait time along with something that chews up all our bandwidth. So we rather decided to ship the hard drives in a truck to the DR site which is hundreds of miles away.
Everything was good until one of the IT Directors who was passing by our desk expressed uncertainty and asked, “Isn’t that like a huge security risk if we lose our disks to a competitor? “
I jumped in and replied him with a smile, “Sir, not when we encrypt our data with Transparent Data Encryption”
And the Boss was happy :)
What is Transparent Data Encryption?
Transparent Data Encryption is a new feature in SQL Server 2008 which allows the encryption of the entire database while providing real time encryption of data files (.mdf) and log files (.ldf). Apart from this, TDE also encrypts any backups taken while the encryption is enabled. Data is encrypted before it is written to disk and data is decrypted when it is read from disk as it is read into the memory. This will help in preventing unauthorized access to the data and backup files.
I will show you an example of how anyone can read the backup files even without restoring the backup into another database.
I have created a database called TDE_Test that contains address details as shown below.(This database basically contains a few tables imported from the AdventureWorks2012 sample database. For downloading and installing AdventureWorks2012, refer HERE)
Figure 1: Result set showing Address data
Let’s backup the ‘TDE_Test’ database
BACKUP DATABASE [TDE_Test] TO DISK = 'E:\Backup\TDE_Test__Backup.bak' WITH INIT, STATS = 5
Open the backup file using Notepad and search for address ‘‘5 4 4 M a g d a W a y’, as shown in Figure 2 (note the spaces between the letters and three spaces between each word as this is Unicode text)
We can easily see that the data in backup file is readable. This means anyone with the backup file can actually read your data with little skill, even without restoring it.
Figure 2: Backup file opened in Notepad – Un-encrypted format
Implementing Transparent Data Encryption
TDE setup includes the below four basic steps.
- Creating a Master Key
- Creating a Certificate Protected by the Master Key
- Creating a Database Encryption Key
- Turning on the TDE
1. Creating a master key
It is a good practice to backup the database before proceeding with the encryption. So in case we want to reverse the implementation of TDE we have a latest copy of database to bring back.
The master key must be created within the master database
USE MASTER; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password12#' GO
2. Creating a Certificate Protected by the Master Key
The certificate is used to protect the database encryption key that we will create next. In addition, this certificate is protected by the master key created in the previous step.
USE MASTER; CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate' GO
3. Creating a Database Encryption Key
Create Database Encryption Key inside the database to be encrypted, and protect it with the certificate. You can choose from one of the following encryption algorithms: AES_128, AES_192, AES_256, or TRIPLE_DES_3KEY. Details on the encryption algorithms is beyond the scope of this article. Check out this Microsoft Technet article for guidance on choosing the encryption algorithm
GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert ; GO
I have also added an additional script below to backup the private key and certificate to disk drive. Since TDE also encrypts the backup files, it is always required to maintain a backup of private key and certificate along with the database backup to use at the time of restoring.
USE master; GO BACKUP CERTIFICATE TDECert1 TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer' WITH PRIVATE KEY (FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk', ENCRYPTION BY PASSWORD = 'Password12#')
4. Turning TDE ON
Since encryption is a resource intensive process it is always a best practice to turn on TDE during off-business hours or when the server is least busy.
USE TDE_Test ALTER DATABASE TDE_Test SET ENCRYPTION ON ; GO
We can check the progress of Encryption using the below script from utilizing ‘sys.dm_database_encryption_keys’ DMV. A value of ‘2’ for ‘encryption_state’ indicates that encryption has begun and ‘3’ for completed
SELECT db.name, db.is_encrypted, dm.encryption_state, dm.percent_complete, dm.key_algorithm FROM sys.databases db JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id WHERE db.name = 'TDE_TEST'
Let’s take a backup of our encrypted database now and search for address as mentioned earlier.
BACKUP DATABASE [TDE_Test] TO DISK = 'E:\Backup\TDE_Test_withtde.bak' WITH INIT, STATS= 5
Whoopee! Our data is now encrypted and secured :)
This way, we can make sure the data is secured as well protected from any unauthorized access.
As a continuation to this topic, I have explained how to restore a TDE Encrypted database backup HERE
Technical Reviewer(s): Venkata Suresh Raavi; Jaipal Vajrala