Transparent data encryption or always encrypted?

How to choose the right encryption technology for Azure SQL Database or SQL Server

Transparent Data Encryption (TDE) and Always Encrypted are two different encryption technologies offered by SQL Server and Azure SQL Database. Generally, encryption protects data from unauthorized access in different scenarios. They are complementary features, and this blog post will show a side-by-side comparison to help decide which technology to choose and how to combine them to provide a layered security approach.

Transparent Data Encryption

TDE is intended to add a layer of security to protect data at rest from offline access to raw files or backups, common scenarios include datacenter theft or unsecured disposal of hardware or media such as disk drives and backup tapes. For a deeper look into how TDE protects against the risk of malicious parties trying to recover stolen databases: data, log files, snapshots, copies or backups and to review TDE best practices see Feature Spotlight: Transparent Data Encryption (TDE). 

Enabling TDE on databases provides the ability to comply with many laws, regulations, and security guidelines established across various industries that require data to be encrypted at rest. Unless data stored in a SQL database has no protection requirements at all, there should be no reason to disable TDE.  It is, however, important to recognize that TDE only adds one layer of protection for data at rest and remaining risks must be addressed at the OS file system and hardware layer, see Bitlocker documentation to learn more.

TDE encrypts the entire database using an AES encryption algorithm which doesn’t require application developers to make any changes to existing applications. TDE performs real-time I/O encryption and decryption of the data and log files. Once the database is loaded into memory, the data is accessible to administrators (DBAs) of SQL Server (sysadmin, db_owner), and Azure SQL Database (cloud) administrators and can be used by all roles and applications that have access to the database. If a database contains sensitive data in specific columns that needs to be protected from administrator roles and remain encrypted in memory, using Always Encrypted should be evaluated in addition to TDE.

TDE offers two options for encryption key management, service managed keys and customer managed keys. For service managed keys, the TDE protector is a certificate stored in the master database of the server. For customer managed keys, the TDE protector is an asymmetric key protected by an EKM module or Azure Key Vault. When using  Azure SQL Database TDE with Bring Your Own Keys (BYOK) the TDE Protector never leaves the key vault, but Azure SQL Database needs to access the key vault to decrypt and encrypt the Database Encryption Key (DEK) used to decrypt and encrypt the data. After the encrypted DEK is sent to key vault for decryption, the unencrypted DEK is stored in memory until the TDE Protector in key vault is deleted or access to SQL Database has been revoked. In this case, the database will go offline within 24 hours and the DEK will be removed from memory.

Always Encrypted

Always Encrypted is a feature designed to protect sensitive data, stored in Azure SQL Database or SQL Server databases from access by database administrators (e.g. the members of the SQL Server sysadmin or db_owner roles), administrators of machines hosting SQL Server instances,), and Azure SQL Database (cloud) administrators. Data stored in the database is protected even if the entire machine is compromised, for example by malware. Always Encrypted leverages client-side encryption: a database driver inside an application transparently encrypts data, before sending the data to the database. Similarly, the driver decrypts encrypted data retrieved in query results.

With Always Encrypted, cryptographic operations on the client-side use keys that are never revealed to the Database Engine (SQL Database or SQL Server). There are two types of keys in Always Encrypted:

• Column encryption keys are used to encrypt data in the database. These keys are stored in the database in the encrypted form (never in plaintext).
• Column master keys are used to encrypt column encryption keys. These keys are stored in an external key store, such as Windows Certificate Store, Azure Key Vault or hardware security modules.  For keys stored in Azure Key Vault, only the client application has access to the keys, but not the database, unlike TDE.

The unique security benefit of Always Encrypted is the protection of data “in use” – i.e., the data used in computations, in memory of the SQL Server process remains encrypted. As a result, Always Encrypted protects the data from attacks that involve scanning the memory of the SQL Server process or extracting the data from a memory dump file.

By protecting data from high-privilege users who have no “need-to-know,” Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access). This enables customers to confidently store sensitive data in the cloud, delegate on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

Unlike TDE, this is only partially transparent to applications. Although the client driver transparently encrypts and decrypts data, the application may need to be changed to adhere to requirements/limitations of Always Encrypted. For example, Always Encrypted only supports very limited operations on encrypted database columns. This is one of the reasons why we recommend you use Always Encrypted to protect truly sensitive data in selected database columns.

One thing to call out is the fact that by encrypting data on the client-side, Always Encrypted also protects the data, stored in encrypted columns, at rest and in transit. However, unless your goal is to protect sensitive data in use, TDE is the recommended choice for encryption at rest, and we recommend TLS for protecting data in-transit. In fact, it is often advised to use Always Encrypted, TDE, and TLS together:

• TDE as the first line of defense (and to meet common compliance requirements) to encrypt the entire database at rest.
• TLS to protect all traffic to the database.
• Always Encrypted to protect highly sensitive data from high-privilege users and malware in the database environment.

Following is a side-by-side comparison of the capabilities discussed above:

More Information:

• Editions and supported features of SQL Server 2016
• For a Channel 9 presentation that includes Always Encrypted, see Keeping Sensitive Data Secure with Always Encrypted
• SQLSweet16!, Episode 1: Backup Compression for TDE-enabled Databases

Source: Azure Blog Feed