Microsoft SQL Server and Microsoft Azure SQL Database offer two complementary encryption options: Transparent Data Encryption (TDE) and Always Encrypted. This blog post will help you decide when to use TDE versus Always Encrypted, and when to combine them for a “defense in depth” security and compliance strategy.

When to use Transparent Data Encryption

Transparent Data Encryption (TDE) protects data at rest, such as backups on physical media. It prevents access to data in scenarios like improper disposal of disk drives or attempts to restore databases from snapshots or copies.

TDE helps companies comply with regulations that mandate encryption of data at rest, such as HIPAA and GDPR. As a general rule, it’s appropriate to enable TDE for any SQL database, unless its data has no protection requirement at all. 

TDE encrypts the full SQL Server database in a manner that doesn’t require changes to the application. Encryption and decryption of the data and log files are performed in real-time. 

 However, TDE offers no protection for the data once it resides in memory. This leaves it vulnerable to “insider threats” and credential theft-related access from administrator (DBA) accounts, such as sysadmin, or other roles/applications that are authorized to access the database.

When to use Always Encrypted

To protect data in memory from identity/credential-based attacks, businesses can use Always Encrypted, which encrypts sensitive data in specific database columns in memory or “in use” during computations. The data remains protected even if the entire system is compromised, e.g., by ransomware. Attacks that involve scanning the memory of the SQL Server process or attempting to extract data from a memory dump are also ineffective against Always Encrypted.

 Always Encrypted allows SQL Server users to reduce the risk of storing data in the cloud, or to leverage third-party vendors for DBA services without violating compliance requirements.

 However, Always Encrypted relies on a client-side database driver within an application to encrypt the requested data before sending it to the database and to decrypt encrypted data in query results. Reliance on a client-sideWindows driver means that applications may require changes to work with Always Encrypted requirements and restrictions. For example, Always Encrypted supports only a few simple operations on encrypted database columns. This tends to limit its use to only higher-risk sensitive data, such as:

• Personal data like customer names and credit card numbers, especially in regulated industries
• To improve security when outsourcing DBA services
• To improve security of data in transit and in use beyond what SSL alone can offer

A good rule of thumb for Always Encrypted is it works best to protect sensitive data that you need to store but don’t need to search on or display to application users. Beginning with SQL Server 2019 (15.x), Always Encrypted supports secure enclaves, which removes some of the limitations on operations you can perform on encrypted data. 

Using Transparent Data Encryption and Always Encrypted together

To create a “defense in depth” or layered encryption protocol for your data, TDE and Always Encrypted can be used together alongside Transport Layer Security (TLS). 

In this scenario, TDE acts as the defensive front line by encrypting the full database at risk, and may suffice to meet compliance requirements. TLS then encrypts data as it is transferred over a network. Finally, Always Encrypted protects the most sensitive data from privileged user attacks, malware that has compromised the database environments, and other threats against the data while it is in use. 

 TDE works with SQL Server 2008 and above as well as Azure SQL Database, but requires SQL Server Enterprise Edition. Always Encrypted works with all editions of SQL Server 2016 (13.x) SP1 and above, plus Azure SQL Database. Both TDE and Always Encrypted are free in Azure SQL Database. 

Next steps

Want to talk with a database security expert before you implement TDE versus Always Encrypted? Contact Buda Consulting to schedule a free consultation.