DataGinger.com

In this article I will show you how to restore a database that is encrypted with Transparent Data Encryption (TDE) on to a different server. Let’s use the same database ‘TDE_Test’ that we have created in the previous post HERE

Restoring a database to a different SQL Instance is usually a straightforward task. However, this attempt will return an error as shown below for an encrypted database when restoring into a different instance.

USE [master]
RESTORE DATABASE [TDE_Test_restore] FROM  
DISK = N'C:\Backup\TDE_Test_withtde.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Output:

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint..
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally

To restore successfully, we will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. As a best practice, we should immediately back up the certificate and the private key when we enable TDE. However, we can still take backup the certificate and private key now in the source server as shown below if not done earlier.

USE master; 
GO 
BACKUP CERTIFICATE TDECert
TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Password12#')

Create a Master Key in destination server.

The password provided here is different from the one we used in the source server since we are creating a new master key for this server.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D1ffPa$$w0rd'

After a master key has been created, create a certificate by importing the certificate we created earlier. Here the ‘Decryption By Password’ parameter is same as that provided to export the certificate to a file.

CREATE CERTIFICATE TDECert2
FROM FILE = 'E:\cert_Backups\ certificate_TDE_Test_Certificate.cer'     
WITH PRIVATE KEY (FILE = 'E:\cert_Backups\certificate_TDE_Test_Key.pvk', 
DECRYPTION BY PASSWORD = 'Password12#')

Restore Database in destination server

We will now be able to restore the encrypted database backup successfully.

USE [master]
RESTORE DATABASE [TDE_Test] FROM  DISK = N'F:\Backup\TDE_Test_withtde.bak' 
WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5

Listed are some of the advantages and disadvantages of using TDE.

Advantages

• The performance impact of TDE is minor. Estimated to be around 3-5%
• Performs real-time I/O encryption and decryption of the data and log files
• Encrypts the Entire Database in rest
• No architectural changes needed
• No application code changes are required and the user experience is the same
• Easy to implement
• DBAs can still see the data

Disadvantages

• No protection for data in memory
• Not granular – Cannot just encrypt specific tables/columns
• Not good for high CPU bottleneck servers
• Not protected through communication/networks

Read Full Post »

Being a part of the database engineering team, I was given a task to set up disaster recovery program for some of our SQL Servers that host 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 the network is a Herculean task considering our extra large databases at an approximate 12-15 mbps over the 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 hundred miles away.

Everything was good until one of the IT Directors who was passing by got curious and questioned if it isn’t a security risk in case we lose our disks during the transit?

That made a whole lot sense, as anyone who can get hold of the disks can break-in into the stored data which is a huge threat. How do we solve this? Yes, encryption.

SQL Server has something called 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.

Demo

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.

1. Creating a Master Key
2. Creating a Certificate Protected by the Master Key
3. Creating a Database Encryption Key
4. 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 the 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 are beyond the scope of this article. Check out this Microsoft Technet article for guidance on choosing the encryption algorithm

USE TDE_Test  

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 a 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 TDECert 
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 the same address that we looked earlier

BACKUP DATABASE [TDE_Test] 
TO DISK = 'E:\Backup\TDE_Test_withtde.bak' 
WITH INIT, STATS= 5

Figure 3: Backup file opened in Notepad – Encrypted format

That’s it! 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 of this topic,  I have explained how to restore a TDE Encrypted database backup HERE

Read Full Post »