3

We are planning out a move from SQL Server 2008 to SQL Server 2016. One of the key requirements for us is to have encryption at rest at a minimum. We have been considering SQL Server Enterprise as it includes TDE though now with SQL Server 2016 Standard including the "Always Encrypted" feature set we are looking into this as an alternative to Enterprise (saves us some money). Though we are having a very difficult time locating a source which clarifies what exactly AE encompasses in SQL Server. Thus far in our research it appears that AE is specific to column level encryption from the client to the DB utilizing certs though we've found nothing that mentions AE can be used in a similar fashion to TDE and encrypt the entire DB.

Can someone please shed some light on this for us?

Improve this question
5
  • 1
    Related & possible dupe of : In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption? - By @aaronbertrand
    – Kin Shah
    Dec 8, 2016 at 15:53
  • Yes AE is majorly designed for column level encryption. But what you can do is copy the set of tables to new database why creating encrypted table in new database their by encrypting whole database.See this blog for scenario specific reading see BOL
    – Shanky
    Dec 8, 2016 at 15:59
  • Must add their is performance implication when you use AE at client side, because data is downloaded ta client's end encrypted and then sent back to database.
    – Shanky
    Dec 8, 2016 at 16:00
  • I've read the other post regarding the difference between AE and TDE though it didn't explicitly state whether or not Always Encrypted can be utilized for the entire DB much like TDE. The links are helpful and it appears that TDE will need to be the way we go moving forward for the time being. AE seems to require quite a lot in terms of the the client application not to mention the possible overhead in using AE on every column in an entire DB.
    – PartnersMan
    Dec 8, 2016 at 16:15
  • 1
    I believe @aaronbertrand should be posted as the answer. However, PoolMan if the requirement is for the entire database to be excrypted at rest it will be much less administrative overhead to use TDE. Additionally as you stated and as Aaron alludes to in his answer the application may have to be re-written for it to use AE.
    – Sean Gallardy
    Dec 8, 2016 at 20:50

1 Answer 1

Reset to default
4

AE is intended to protect sensitive data end-to-end. That's why keys are held by the client, not server. In SQL Server 2016 granularity is at the column level.

If you want to AE the entire database, you'd have to do it one column at a time. That doesn't really make sense and doesn't fit the AE scenario unless all data in your database is sensitive. Theoretically possible but pretty rare in reality. You've also made your database extremely limited in query capabilities and perf is pretty much out the window.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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