In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?
Downsides of Transparent Data Encryption compared to Always Encrypted:
- Only protects data at rest - backups and data files are "safe" but data in motion or in memory is vulnerable
- Whole database only
- All data is encrypted the same way
Backup compression can take longer and be counter-productive
- Well, actually, there are some improvements here in SQL Server 2016 that defy what we've typically known about trying to compress encrypted data - it's much better than previous versions, but presumably still worse than only encrypting a handful of columns (untested)
tempdb also inherits encryption – stays even after disabling TDE- Requires Enterprise Edition
- Data always accessible to sysadmin
Always Encrypted addresses all of these issues in part or in full:
- Data is protected at rest, in motion, and in memory - much more control over certs, keys, and exactly who can decrypt data
- Can be just a single column
- Encryption type is a choice:
- Can use deterministic encryption to support indexes and point lookups (say, SSN)
- Can use random encryption for higher protection (say, credit card number)
- Since it's not database-wide, backup compression isn't necessarily affected - of course the more columns you encrypt, the worse luck you'll have
- tempdb is uninvolved
- As of SQL Server 2016 Service Pack 1, Always Encrypted now works in all editions
- Data can be protected from sysadmin (but not sysadmin AND Windows security/cert/key admins, in other words you can separate responsibility as long as those two groups don't collude)
There is a limitation, though, and that is that not all drivers and applications can deal with the encrypted data directly, so in some cases this will require updating/changing drivers and/or modifying code.
Simply put, TDE is data encrypted at rest (On disk) and AE is data encrypted on the wire in addition.