Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

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


By:   |   Read Comments (4)   |   Related Tips: More > 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'

2


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:

3


Move Database Using Copy Database

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

 

4

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

5


Move Database Using Detach and Attach Method

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

6

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:

7


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.

8

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

9

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.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 19, 2013 - 4:39:04 AM - hoannh Back To Top

Thanks Maria!

I have a question: I deployment my application (include window form & sql server database). How to protect structures(Tables,SP,Views and Functions) of a SQL Server Database?


Sunday, November 06, 2011 - 2:34:32 AM - Mohammed Moinudheen Back To Top

Brady, This is a cool tip. I used this tip as reference just yesterday for setting up TDE on one of my servers. This tip is neatly described with nice screenshots. Good job.


Thursday, November 03, 2011 - 11:00:09 AM - Brady Back To Top

Thanks Maria! Glad we could help!


Thursday, November 03, 2011 - 10:44:03 AM - MariaSoft Back To Top

Great tip.  I picked up a few new things.


Learn more about SQL Server tools