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?
Best Answer
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.