Always encrypted is a new feature introduced to encrypt the in rest as well as during transport. It’s been quite long time the feature has been released to general public. In this encryption method the encryption will be done at the client side. Encryption keys can be stored in Azure vault, Windows certificate store in client server or in hardware module. Since the encryption keys doesn’t sit in the database engine it doesn’t reveal the actual data and even this is applicable for an administrators.
In the existing world the business team worries about sensitive data visibility to administrators and this feature will be a way to start protecting against it. Let me write a quick over view about this feature as what it can do and what not!
- SQL Server 2016 (all editions from SP1)
- Azure SQL PaaS Database (v13 and above)
- Deterministic – Generate same encrypted text every time for a given text
- Randomized – Different encrypted text every time for a given text
|Use this type for the columns used in Joins, group, lookup and search predicates
|Use for confidential PII or PCI data. Not supported to use the column in joins or search predicates
|Requires string data type to be in *_Bin2 collation (for example Latin1_General_BIN2) . No other collation supported for string data types||Collation change is not required
|Initial change is slower as it has to lock the table to change the collation and to perform the encryption. Down time may be required based on the table size.||Initial change is slower . Down time may be required based on the table size.|
As of now it supports only AEAD_AES_256_CBC_HMAC_SHA_256 encryption algorithm.
- Supported from .Net framework 4.6 and above
- SSMS 2016
- .Net Framework Data Provider for SQL Server
- Always encrypted with JDBC driver
- Always encrypted with ODBC driver
It’s pretty straight forward. Any one of the below tools can be used.
- Configure through SSMS, follow the wizard. This even allows to create the certificate and store the configuration as a powershell script.
- Configure through Powershell cmdlets.
Limitations \ things to consider
- xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types are not supported
- Randomized encryption columns can’t be used as key columns in clustered and non-clustered indices.
- Sparse \ Replication \ CDC \ Dynamic data masking \ Stretch database are not supported
To View & Modify certificates
GRANT ALTER ANY COLUMN MASTER KEY TO USERRole
GRANT ALTER ANY COLUMN ENCRYPTION KEY TO USERRole
To View all the columns (irrespective of actual data)
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO USERRole
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO USERRole
Users without VIEW ANY definition permission won’t be able to query all columns from the table. They can query un-encrypted columns but if they query the encrypted column they will be ended up with error. If the encryption is enabled for an existing table then this might be a breaking change, proper testing need to be performed.
Users with VIEW ANY definition can view unencrypted data when they connect as normal. However if they use “Column Encryption Setting = Enabled” in their connection string then they will be able to view the actual data.
Alter can’t be done on encrypted columns. However it allows to add\modify any other unencrypted columns.
RW can be performed from the application for all the columns included encrypted columns.
Only read can be performed for all the columns and RW can be performed to unencrypted columns through SSMS by adding “Column Encryption Setting = Enabled” connection string parameter.
Using deterministic encryption method allows to use the encrypted columns in JOIN clause however it also requires the other table column to be encrypted, need to be wise in choosing the encryption columns.
More details in the upcoming articles as soon as Im done exploring this feature.