Being a part of the database engineering team, I was given a task to setup disaster recovery program for some of our SQL Servers hosting critical applications. After a few discussions, I planned to make use of log shipping to sync data between the two data centers like most DR sites work. This requires us to copy the backup files to the Disaster Recovery (DR) site for the initial setup. But in a little while, I realized moving 30 TB of data over network is a Herculean task considering our extra large databases at an approximate 12-15 mbps over network. This might not be a feasible option with 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 just a few hundreds miles away.
Everything was good until one of the IT Directors who overheard this while he was passing by DBAs was curious and asked if it isn’t a security risk if we lose our disks during the transit?
I jumped in and replied him with a smile, “Not when we encrypt our data with Transparent Data Encryption”
Lets see what Transparent Data Encryption is?
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