41

As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature.

I would just like to know what the specific differences will be between Always Encrypted & the currently available Transparent Data Encryption in SQL Server 2016 so that we can make the correct decision for future projects.

Improve this question
1

2 Answers 2

Reset to default
49

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.

Improve this answer
3
  • Could you please explain me, how Always Encrypted encrypts data in memory? I've tried to find this information in the MSDN, but it just says about data at rest and data in motion encryption. Thank you :)
    – Victoria Malaya
    Nov 25, 2016 at 21:56
  • 1
    @Victoria the data is encrypted by the provider before SQL Server ever sees it. So SQL Server receives an encrypted value, puts that encrypted value on disk, and loads the encrypted value into memory when that page is in memory. Encryption doesn't happen in memory the way you think, and decryption only happens when a client possesses a certificate with the ability to decrypt...
    – Aaron Bertrand
    Nov 25, 2016 at 22:12
  • 2
    As far as I know, one of the biggest differences is when it comes to querying the data. With TDE you can run any query as you normally would, with AE you are very restricted when it comes to querying the encrypted columns, i.e. you can only do equality comparisons (and that requires opting for deterministic encryption). There is no checking date ranges, no LIKE queries, etc.
    – musefan
    Aug 22, 2017 at 9:12
3

Simply put, TDE is data encrypted at rest (On disk) and AE is data encrypted on the wire in addition.

Improve this answer
3
  • I think you might have that backwards, no?
    – zwerdlds
    May 4, 2016 at 22:22
  • Shouldn't it be: TDE is data encrypted at rest while AE is data encrypted on the wire, at rest & in memory?
    – RoastBeast
    May 12, 2016 at 14:04
  • AFAIK: You can combine TDE and Force Protocol Encryption on the MS SQL server to have it on the wire encrypted as well.
    – TiloBunt
    Jun 16, 2017 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.