As discussed introduction of Always Encryption blog and initial Encryption at rest as TDE which is introduced on SQL Server 2008, TDE is at rest has limitation as data is open anyone who has access to SQL Server could able to see the data, to overcome the data security issues, TDE is Server side Encryption where as Always Encryption is Client side encryption, it is next level security where data at SQL Server is not visible/accessible to even DBA’s – which makes it more strong security. eg. Credit card , SSN , PII data which is critical/ compliance related data and should be secure.
SQL Server 2016 introduced Always Encryption. which is at column level encryption on SQL Server. Similar to DMK, DEK . Always Encryption has Column Master Key (CMK) and Column Encryption keys(CEK). CMK is external /outside used by application and has control for data and CEK is encrypting the data at column level and inside SQL Server . DBA/Administrator can manage the system but they can view the data. There can be one or many CEK mapped with one or many CMK. Generally we can 1 CEK can be used for all column for 1 CMK.
Similar to DMK, CMK has to be associated with Certificate, CMK uses “Windows Certificate Store” “KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE‘,” to encrypt the data.
both the CMK and CEK keys should be created at user database where your database columns required to be always encryption
Column Master Key(CMK): following is the script generated to create column master key for given database (ensure to generate certificate button for windows certificate store to be included. to create the CMK. (for now do not enable /check box for enclave , we will talk about Enclave in next blog)
CEK: once CMK with certificate created, we can create CEK with CMK as shown in following screenshot, here we can see algorithm RSA and reference of CMK.
Now as Encryption setup is done, we have to configure select column for AE (Always Encryption), there are different types of Encryption
- Deterministic: this encryption time convert/encrypt the data (Binary) deterministic /static retrieval on every encrypted output. query output is specific so you can use where/order by clause. this is less strong then randomized.
- Randomized: this encryption is randomized and return random output on every retrieval so you can not where clause query and other conditional queries. this is strongest encryption.
It requires “Latin1_General_BIN2′” collation , encryption will be on BIN format
As always encryption is at client side so we have to consider that this is outside sql server we have control on interact with SQL Server so we have to use C#, powershell or use SSMS as client system.
We can create table with encrypted column or we can existing encrypt the table – column.
- Existing table convert Always Encryption for testing purpose – EASY WAY 🙂 ;using SSMS
TSQL: you have to enable AE only at the time of table creation you can alter table AE only at Enclave enabled (will discuss in next blog )
once it is encrypted the data will be encrypted.when you query you will get it in encrypted values
to view in SSMS unencrypted as a workaround – you can view it using C#, powershell as well.
open new SSMS connection with :
in connection string – Column Encryption Setting=’Enabled’
in Query Options- Enable Parameterization for Always Encrypted
After this you can query Encrypted columns, to work on encrypted columns you can validate it using sp
exec sp_describe_parameter_encryption
[ @tsql = ] N’Transact-SQL_batch’ ,
[ @params = ] N’param’
Query To check the CMK & CEK :
Limitation:
For perform pattern matching, comparison operators with other paintext columns and encrypted data.
Columns using one of the following data types: xml, timestamp, rowversion, image, ntext, text, sql_variant, FILESTREAM columns.IDENTITY,ROWGUIDCOL,Partitioning,default constraints.referenced,change data capture.DDM
SQL Server Replication
Distributed queries -linked servers, OPENROWSET, OPENDATASOURCE.
So Always encryption has so many limitations it self as it is isolated with rest of the objects so MS introduced Enclave to overcome this issue
Ref: Microsoft site has detail about limitation and issues associated with Always Encryption, which has been mitigated/improved using ENCLAVE (next blog )
Vinay Thakur 