Azure SQL Database Transparent Data Encryption(TDE) + Always Encrypted safe?

Question

I was curious if anyone knows if you can combine Transparent Data Encryption(TDE) and Always Encrypted column-level encryption simultaneously without causing problems?

TDE encrypts the entire database but the table data can still be viewed by database administrators. Whereas Always Encrypted protects data from our staff by encrypting the values stored within the table. But we only need to protect a few columns in this way.

I read that it is risky to perform actions such as compression on an Always Encrypted column. So I wonder if adding TDE to a database using Always Encrypted will be ok? I don't want to risk corrupting the data.

Thanks

Answer 1

I don't see why that wouldn't work. TDE and Always Encrypted are basically different approaches:

• TDE encodes data files, and (IMHO) the only reason to use that is to prevent data compromising when physical files are stolen
• Always Encrypted encrypts data in columns using public-private keys, so even if you know login/pass to database - you still need private key to decode data; it is used for sensitive data like credit cards etc

When you use both - you have encoded data in columns which is kept in encoded data files.

I read that it is risky to perform actions such as compression on an Always Encrypted column

I cannot find any references to that. You could be misguided by idea that there's no sense in compressing encrypted data.

Microsoft about TDE backup compression:

Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.

Microsoft about Always Encrypted compression:

Encrypted data cannot be compressed, but compressed data can be encrypted. If you use compression, you should compress data before encrypting it