Transparent Data Encryption
Transparent Data Encryption (TDE) in Microsoft SQL Server Databases allows you to protect sensitive data in tables by encrypting them when they are stored on media. The data is transparently decrypted for authorized users or applications when they access the data.
TDE fundamentally is full database-level encryption. Any data written into the database storage is automatically encrypted. SQL Backups are also automatically encrypted. Data in use is decrypted by TDE as they are read by a user or application and stored, in clear text, in memory. Data-in-flight is decrypted and the key is stored in memory.
Cell-Level Encryption is implemented as a series of built-ins. It is a manual process “that requires a re-architecture of the application to call the encryption and decryption functions. The traditional limitations of encryption are inherent in this method as none of the automatic query optimization techniques [of TDE] can be used.
CLE vs. TDE
The advantages of CLE:
Since it is column-level encryption, it encrypts only the sensitive information in a table.
With CLE, the data is still encrypted even when it is loaded into memory.
CLE allows for “explicit key management” giving you greater control over the keys and who has access to them.
CLE is highly configurable, giving you a high degree of customization (especially when your applications require it).
Queries may be faster with CLE if the encrypted column(s) is not referenced in the query. TDE will always decrypt the entire row in the table. CLE will decrypt the column value only IF it is a part of the data that is returned. So in some cases, CLE implementations provide much better overall performance.
The disadvantages of CLE:
One of the main disadvantages of CLE is the high degree of fully manual application changes needed to use it. TDE, on the other hand, can be very simple to deploy with no changes to the database, tables, or columns required.
CLE can also have high-performance penalties if search queries cannot be optimized to avoid encrypted data. “As a rough comparison, performance for a very basic query (that selects and decrypts a single encrypted column) when using cell-level encryption tends to be around 20% worse [than TDE].”
Using Column Level Encryption with Transparent data encryption
A case could be made for using CLE in conjunction with TDE as a defense-in-depth strategy. By selectively encrypting columns with CLE, encrypting the full database with TDE, and then managing the separate keys with a centralized key manager; would ensure that crucial data was protected, even while loaded into memory.
But, in general, TDE and CLE are used for different purposes. If you are looking to encrypt a small amount of data, if your application “has custom design requirements,” or if performance is not much of a concern, CLE may have advantages over TDE. But, if performance is a concern or you would like to avoid manually implementing encryption (normally a time-consuming process) then TDE is the way to go.