Introduction

Transparent Data Encryption (TDE) encrypts all the data that’s stored within the database’s physical files and also any backup files created from the database. With data security becoming more and more important there’s no doubt that encryption of data using technologies such as TDE will become increasingly relevant. However as always there’s a price to be paid for implementing TDE and this article discusses some of the pros and cons.

This is the first part of a three part series about TDE. In the other articles I’ll discuss how to set up TDE and some best practices.

Transparent Data Encryption

First of all it’s important to understand the scope of TDE, as it’s not a complete end to end encryption solution. TDE will encrypt the data files and transaction log files (.mdf, .ndf and .ldf files) and the backup files (.bak files). This means that so called “data at rest” is encrypted, however traffic between the database and application is not encrypted (at least not by TDE, but you can use SSL to achieve this), and data held within the application is also not encrypted. TDE is implemented at the database level and is an all or nothing solution – so all data within the database will be encrypted – you can’t just encrypt the sensitive columns.

Another point to watch is that even if only one database on a server has TDE enabled then TempDB will be encrypted, so the performance of other non-encrypted databases on the same server may be affected. However although there’s inevitably a performance impact when using TDE on a database, Microsoft claims this is only 2 – 4% compared to a non-encrypted database.

I thought it would be useful to summarise some of the pros and cons of TDE :

Advantages of TDE

• Fairly simple to implement.
• No changes to the application tier required.
• Is invisible to the user.
• Works with high availability features, such as mirroring, AlwaysOn and log shipping.
• Works with older versions of SQL Server, back to 2008.

Disadvantages of TDE

• Only encrypts data at rest, so data in motion or held within an application is not encrypted.
• All data in the database is encrypted – not just the sensitive data.
• Requires the more expensive Enterprise Edition (or Developer or DataCenter Edition) of SQL Server.
• The amount of compression achieved with compressed backups will be significantly reduced.
• There is a small performance impact.
• FileStream data is not encrypted.
• Some DBA tasks require extra complexity, for instance restoring a backup onto another server.
• As TempDB is encrypted, there is potentially an impact on non-encrypted databases on the same server.
• The master database, which contains various metadata, user data and server level information is not encrypted.

Hopefully the above summary is useful, if you have any other pros and cons then please let me know and I will add them to the list.

For completeness TDE isn’t the only database encryption technique available within SQL Server, some of the others are:

• The business logic within individual stored procedures can be encrypted using the ‘ENCRYPTION’ keyword.
• Individual data items (i.e. column or cell-level encryption) can be encrypted and decrypted using the ‘ENCRYPTBYPASSPHRASE’ and ‘DECRYPTBYPASSPHRASE’ statement along with a pass phrase. ENCRYPTBYKEY/DECRYPTBYKEY and ENCRYPTBYCERT/DECRYPTBYCERT are similar but use a key or certificate to encrypt the data.
• Data items can also be encrypted and decrypted using .net CLR functions.

Some third party software such as Redgate SQL Backup include backup file encryption.