Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

TDE and SQL Server Databases

By Perry Whittle,

Transparent data encryption is a fairly new option that is available in SQL Server 2008 onwards. It has the ability to protect databases from opportunist access, this covers the following;

  • an opportunist user who takes a copy of the backup file and restores it to another server.
  • an opportunist user who takes copies of the database files and restores them to another server.

TDE protects against both these scenarios. Now, there are some very good 3rd party products such as Redgate's SQL Backup Pro and Quest's Litespeed; they both offer the ability to combine compression with full encryption up to 256bit, something that native compression and TDE don't combine very well. However, this would still leave the actual database files vulnerable.

Implementing TDE also brings with it a whole set of extra tasks to consider. You have the added overhead of managing passwords and certificates and securing backups of all your certificates. Also, what happens if you have to send the database out to your vendor and it's TDE protected?

Looking at some key points (this is by no means an exhaustive list) 

What doesn't TDE do? 

  • does not encrypt data at the object level, users can still see the data via queries, etc.
  • does not support instant file initialisation for database files.
  • does not interract well with backup compression.
  • does not encrypt read only filegroups.
  • does not encrypt databases used in replication topologies.
  • does not encrypt filestream data.

What does TDE do? 

  • does encrypt the data "At rest", the actual files themselves are encrypted via a symmetric database encryption key usually protected by an asymmetric key or a certificate.
  • does encrypt database files (data and log) and transaction log records for log shipped and mirrored databases.
  • may affect performance for non encrypted databases on the same instance due to addition of the encrypted TempDB.

Yes, that's correct, for TDE to fully protect any given database it also has to encrypt the TEMPDB too. Before implementing TDE you should carefully weigh up the options and the "pros and cons". If backup compression is paramount TDE will not be a good partner, look at the NTFS ACLs instead as these are used to secure the raw files.

Something I tend to see quite a lot, are posts from users requesting information on how to move their TDE protected database to a new server, the reply has nearly always been, "backup your master key and restore it on the new server". This is simply untrue!

The SQL Server instance has an encryption key called the Service master key and sits at the top of the SQL Server encryption hierarchy. Please see the following link for details of the SQL Server encryption hierarchy: http://technet.microsoft.com/en-us/library/ms189586.aspx

This is the principal under which all objects are secured, linked servers are one example. The key is generated the first time its required, more can be found at this link: http://technet.microsoft.com/en-us/library/ms189060

Encryption is applied using the following crptographic sequence;

  • The Service master key is used to protect the database master key (although you can change this behaviour).
  • The database master key is the key we use for Transparent Data Encryption, this key is stored in the master database.
  • This master key is then used to protect any certificates that we store in the master database.
  • A database encryption key is then created in your TDE database and is bound by the server certificate.

It's important to understand that the certificate used to secure the database encryption key used for TDE has no direct dependency on the service or database master keys, you do not need to backup and restore either of these keys when moving your TDE protected database to a new server. You do, however, require a back of the certificate to create a matching cert on the new server.

Implementing TDE involves the following steps; 

  1. If you haven't already, create the database master key and store the password securely.
  2. Create a server certificate in the master database and take a backup of this certificate.
  3. Create a database encryption key in your database you wish to enable for TDE
  4. Set the encryption option on.
  5. Check the encryption state using the following query
select DB_NAME(database_id), encryption_state, percent_complete from sys.dm_database_encryption_keys

Moving your TDE protected database to a new server involves the following steps; 

  1. You've already completed the steps above
  2. If you haven't already, create the database master key on your target server and store the password securely.
  3. Create a server certificate from the backup of the certificate on the source server.
  4. Restore the TDE protected database to the target server.

TDE can provide great benefit when protecting database files\backups, there are restrictions so choose carefully whether to implement this technology. There are also possible performance ramifications for encrypted databases due to the encrypted TEMPDB and the realtime I/O encryption applied to the protected database(s). Like most options, they're not for everyone and require careful thought and planning, just be sure the effort provides sufficient benefit to the business.

Total article views: 5636 | Views in the last 30 days: 13
 
Related Articles
ARTICLE

Administering Database Master Keys in SQL Server

A short piece that gives you the basics of how to administer the database master keys that form the ...

FORUM

Sharing MASTER KEY ENCRYPTION DB?

MASTER KEY ENCRYPTION, CERTIFICATE or SYMMETRIC KEY

BLOG

SQL Server Encryption - Protecting Files at Rest

You have a few options for protecting your SQL Server data at rest. So far I haven't seen anything t...

FORUM

Encrypt the whole database

Encrypt the whole database

FORUM

Encrypting the entire database.

Encrypting the entire database.

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones