Securing and protecting SQL Server data, log and backup files with TDE

By: Brady Upton   |   Updated: 2011-11-03   |   Comments (4)   |   Related: > Security

Problem

I need to keep some old SQL data and log files in a backup folder for DR purposes. What is the best way to protect these files?  In this tip we look at using TDE to safeguard your files and the steps necessary to move a TDE database to another SQL Server instance.

Solution

In this tip I'll show you how to setup Transparent Data Encryption (TDE). TDE is new in SQL Server 2008 and serves as an encryption method that uses a database encryption key (DEK) to protect SQL Server's data and log files. The DEK is a key secured by a certificate stored in the master database.

To setup TDE we'll need to run a few scripts: (My test database is named TDE)

The following script will create the master key with a specified password ElephantRhin0:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ElephantRhin0';
GO

Next, we'll create a certificate named TDECert that will be protected by the master key:

USE master;
GO
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
GO

After creating the certificate we'll backup the certificate to a specified source:

USE master;
GO
BACKUP CERTIFICATE TDECert TO FILE = 'C:\TDECert_backup' WITH 
PRIVATE KEY ( FILE = 'C:\TDECert_key' ,ENCRYPTION BY PASSWORD = 'ElephantRhin0' )
GO

Once the certificate is backed up we will create the DEK using the AES algorithm and protect it by the certificate:

USE TDE;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

The final step is to set our database to use encryption:

ALTER DATABASE TDE
SET ENCRYPTION ON;
GO

If everything completed successfully then we have officially encrypted our database with TDE, but don't take my word for it, run the following query to confirm:

SELECT name, is_encrypted
FROM sys.databases
WHERE name = 'TDE'

Once, we have the database encrypted let's try to move data from one instance to another.

Move Database Using a Database Restore

Backup the encrypted database:

BACKUP DATABASE [TDE] TO  DISK = N'C:\TDEBackup' WITH NOFORMAT, NOINIT,  NAME = 
N'TDE-Full Database Backup',  SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Restore the encrypted database on a different instance:

RESTORE DATABASE TDE FROM DISK = N'\\Principal\C\TDEBackup' WITH FILE = 1, NOUNLOAD,  
STATS = 10

When we try to restore the database we get the following error indicating that the instance can't find the certificate:

Move Database Using Copy Database

Right click on the database, choose Tasks > Copy Database...

Complete the wizard and on the last step we get a job failed message.

Move Database Using Detach and Attach Method

Right click on the database and select Tasks > Detach...

Move the database files to new server and attach the database to the new server.  When we try to attach the database we get the following error:

Successfully Moving The Database to a New Instance

To move, copy, or restore a database that is encrypted with TDE we will need to restore the certificate to the destination server first. To do this we will need to be connected to the destination server and run this statement to restore the certificate that we backed up while enabling TDE.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ElephantRhin0'
GO
CREATE CERTIFICATE TDECert    
FROM FILE = '\\Principal\C\TDECert_backup'     
WITH PRIVATE KEY (FILE = '\\Principal\C\TDECert_key',    
DECRYPTION BY PASSWORD = 'ElephantRhin0');
GO

To confirm the certificate was created, you can drill down in SSMS in the master database to Security > Certificates and find our certificate named TDECert.

Once this certificate is created on our destination server, we can use any of the above methods and in our case we will do a RESTORE to show that this works now.

RESTORE DATABASE TDE FROM  DISK = N'\\Principal\C\TDEBackup' WITH  FILE = 1, NOUNLOAD,  
STATS = 10

Next Steps

• If you need to protect your physical database files such as data files, log files or database backup files consider using TDE
• Make sure you safeguard the corticated and master key password as these are the keys to enable successful access to these files.
• While TDE works well for encrypting your data and log files, this encryption method does not encrypt your data inside SQL Server.  So look at other methods such as Symmetric and Asymmetric Keys.

About the author

Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips