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.