Tip

Understanding transparent data encryption in SQL Server 2008

Transparent Data Encryption (TDE) is a new feature in SQL Server 2008 designed to encrypt your database files, database backups and temporary database (tempdb). As you request data from your database, it will be decrypted in real time, and TDE will not prevent any user authorized to enter your database from accessing and reading your tabular data.

Transparent data encryption – why use it?

The PCI DSS (Payment Card Industry Data Security Standard) requires that each of your databases and backups are secured, and TDE is intended primarily to help companies running SQL Server 2008 meet the terms of those compliance guidelines.

Keep in mind that TDE won't satisfy all of your security or compliance requirements on its own. It is instead part of a suite of features provided by SQL Server 2008 to help DBAs achieve compliance. The DBA will still need to ensure that sensitive data is encrypted by the encryption algorithms, and network and system administrators must ensure that the Windows servers, network and link between the Web and application servers are secure. Developers are still responsible for making sure that communication from the client to the Web server is secure or encrypted.

Considerations before using TDE

Before you implement transparent data encryption on your SQL Server you should consider several factors.

For example, any company using

    Requires Free Membership to View

TDE in SQL Server may notice a slight performance degradation as data is encrypted while being written to disk, and decrypted when being read from the disk. This hit is mainly due to increased CPU requirements. The data file, transaction log and backups will be the same size as with a database that does not have TDE enabled.

More features and tools for SQL Server 2008

New replication features and what they mean to you

Data profiling tools in SQL Server 2008

Database compression ratios for encrypted database backups are far more cost-effective for unencrypted backups. This may require increased storage requirements for your backups, and an added fee may be incurredif you are transferring those encrypted backup files offsite.

Database compression ratios for encrypted database backups are much less when compared to those of unencrypted backups. This may require increased storage requirements for your backups, and added costs if you are transferring those encrypted backup files offsite.

While securing backups can also be done natively in SQL Server via a password, this is considered a weak option. Most tape backup solutions now include encryption on the fly while writing to tape devices. While in the past this technology was slow, there have been considerable advances in tape encryption over the past few years. Still, these developments will not prevent a hacker from accessing your SQL Server, nor will they hinder their efforts to detach your database files, copy them to another SQL Server, attach them and read your database contents. Database file encryption is required by most compliance regulations.

Below are some other important factor to take into account before implementing transparent data encryption:

  • Using TDE requires a database encryption key (DEK) and any certificate that you may have used for the DEK. You will need this key when restoring your backups.


  • If you are using TDE, instant file initialization is disabled. Instant file initialization is a feature of Windows Server 2003 that SQL Server 2005 can take advantage of where database growth times are extremely fast, as the underlying space in the file system does not need to be zeroed out.


  • If you are log shipping or database mirroring a transparent data encryption database, TDE will need to be enabled on the secondary, or mirror server.
  • FILESTREAM data will not be encrypted. FILESTREAM is a feature of SQL Server 2008 where varbinary columns can be stored in the file system and asynchronously streamed to the client.


  • Read-only file groups in your database will have to be made writable to enable TDE to encrypt the database contents. They can then be made read-only again.


  • Enabling a database for transparent data encryption may take some time, and some database operations will not be enabled during this conversion period. Consult Microsoft's page on understanding TDE for more information on what these limitations are.


  • Replication is "TDE unaware", and replicated data will not be encrypted. In other words, replication network traffic will be plain text as always, as will the replication snapshot files. The DBA will need to account for this in the compliance effort.


  • Full-text indexing will extract textual data from varbinary and image columns into the file system momentarily during the index process. This data will be plain text and not encrypted. Microsoft recommends that you do not full-text index data stored in the varbinary/image columns.

Enabling transparent data encryption in SQL Server 2008

To enable TDE you will fist need to create a Service Master Key (SMK). To do this, use the following statement in your master database:

Create Master Key Encryption By Password = 'MyPassword'

You will then need to protect the DEK with a certificate which you will be able to transfer to another server should you need to restore the TDE protected database there. You can achieve this by using the following statement:

CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate'

You will then need to backup the certificate into the file system, along with the private key. Ensure that you keep both of these files in a secure, known location. If you loose these files you will be unable to restore your database and read its contents.

BACKUP CERTIFICATE MyCertificate TO FILE = 'c:\temp\MyCertificateBackup.bck'
WITH PRIVATE KEY (
  FILE = 'c:\Temp\MyPrivateKey.key',
  ENCRYPTION BY PASSWORD = 'MyPassword');

You will now need to create a database encryption key encrypted with the above certificate.

CREATE DATABASE ENCRYPTION KEY
 WITH ALGORITHM = AES_256
 ENCRYPTION BY SERVER CERTIFICATE MyCertificate

Now you can enable transparent data encryption on your database by using the following command:

ALTER DATABASE myDatabase SET ENCRYPTION ON

Finally, you can monitor the progress or state of the encryption conversion by querying the following DMV:

Select db_name(database_id), encryption_state from sys.dm_database_encryption_keys

The important thing to remember about transparent data encryption for SQL Server is that it's not a one stop encryption solution. It also does not encrypt sensitive data in your database, but rather the data files and backups. You will still need to protect sensitive data by encrypting individual columns to only allow authorized people to view them.

ABOUT THE AUTHOR:   
Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a Web and database consultant, and has worked with SQL Server for 11 years. Cotter is Director of Text Mining at RelevantNoise, dedicated to indexing blogs for business intelligence. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is author of the book A Guide to SQL Server 2000 Transactional and Snapshot Replication and is currently working on books devoted to merge replication and Microsoft search technologies. Hilary Cotter can be contacted at hilary.cotter@gmail.com.


This was first published in June 2009

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.