1

I have been tasked with encrypting all data on an SQL database (SQL 2016 standard).

However this doesnt support TDE (Transparent Data Encryption) when I try I get the below error.

Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions.

  • Product Version: 13.0.4001.0
  • Product Name: SQL Server 2016
  • Product Level: SP1
  • Product Edition: Standard Edition (64-bit)

So I am guessing my only option is to use "always encrypted"? I have opened the wizard and can only pick columns one at a time, is there a way to select all columns in all tables and encrypt them?

Improve this question
4
  • 4
    Always Encrypted is not a panacea. Please review at least this post: learn.microsoft.com/en-us/sql/relational-databases/security/… From this you will see that there are many limitations on what you can do. Always Encrypted is intended to encrypt more sensitive data (like Social Security Number, Credit Card number),etc. NOT all data of all types in a database.
    – RLF
    Commented Nov 15, 2017 at 15:42
  • 1
    Can you get more details on the level of encryption you need and what layer? TDE will encrypt the data on your physical disks and backups. End users will still be able to run queries against the database server and get the data unencrypted. Always Encrypted will encrypt the data on the select statements with many caveats. What do you exactly need to encrypt?
    – Ali Razeghi - AWS
    Commented Nov 15, 2017 at 16:09
  • 1
    Encrypting all columns using always encrypted would be a big no from my side, this will severely hamper performance.
    – Shanky
    Commented Nov 15, 2017 at 16:29
  • Also, if you use always encrypted, the CLIENT has to unencrypt the data, so you can't do this unless the client side supports it.
    – Tony Hinkle
    Commented Nov 15, 2017 at 20:15

1 Answer 1

Reset to default
1

AlwaysEncrypted is not designed to be a solution for encrypting all of the data in the database. There are quite a number of limitations, such as those documented in T-SQL Tuesday #69 : Always Encrypted Limitations and the official Always Encrypted (Database Engine). For example, you can't encrypt columns with IDENTITY property--so right there is one reason it's unlikely anyone is going to encrypt an entire production database with AlwaysEncrypted.

AlwaysEncrypted is a solution for encrypting a few columns in a table that hold truly sensitive data, such as Social Security numbers, tax IDs, birth dates, credit card numbers, etc.

Additionally, the client application is what decrypts the data when AlwaysEncrypted is used. Accordingly, you won't be able to use it with a third-party application unless it specifically supports AlwaysEncrypted. If it is an in-house application, it would need to be modified to support AlwaysEncrypted.

If you need all of the data at rest to be encrypted to comply with regulations, you'll have to get an Enterprise Edition license to use TDE, encrypt it at the storage layer, or use a different database engine.

Improve this answer

Your Answer

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

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