Pros and Cons of Transparent Data Encryption (TDE) Part 1 of 3

Category : Articles Published : May 20, 2016 User Rating : 4.5 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

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.
Link back to this article : https://www.sqlmatters.com/Articles/Pros and Cons of Transparent Data Encryption (TDE).aspx

Keywords

SQL 2012, 2008R2, 2014, Security, Encryption, TDE


Comments
Post by Anand on Mon 04 Mar 2019 11:46. Report Inappropriate Post

Whilst the TDE helps protect data stored on media in the event that the storage media or data file gets stolen, it does not prevent exporting or downloading dump of sensitive data like Card numbers, PAN details of cards by an authorized user who is provided access to the database (Internal Threat).
can this be prevented or restricted?
Post by Tarek on Wed 19 Jan 2022 13:54. Report Inappropriate Post

"Requires the more expensive Enterprise Edition (or Developer or DataCenter Edition) of SQL Server."

TDE is now included for Standard and Web editions with SQL server 2019+
Post by sqldba21 on Sat 15 Oct 2022 18:29. Report Inappropriate Post

what are the recommendations to store the private key and certificate backup

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered