SQL SERVER 2016 Always Encrypted

It is the new way of Data encryption introduced with SQL Server 2016 used for encrypting the sensitive date encrypted at the application layer via ADO.NET. This means you can encrypt your confidential data with the yours.NET application before the data being sent across the network to SQL Server.

Column master key:

The Column Master Key is stored on an application machine, in an external key store. This key used for protecting the column encryption key and SQL Server doesn’t have any access to this core directly

Column Encryption Key:

But this one is stored in SQL Server and it used for encrypting/decrypt the Always Encrypted column at this time the scenario of the encryption will be the first ADO.NET has decrypted the Column Encryption Key, using the Column Master Key then SQL Server use Encryption Key for encrypting/decrypt the Always Encrypted column.

Technical Demo:

Expand your DB under security you will find “Always Encrypted Keys “
Right click create new column master key

USE [SQL2016DEMO]
CREATE COLUMN MASTER KEY [Demo_Always_Encrypted_CMK]
WITH
(
KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
KEY_PATH = N’CurrentUser/My/09D607EDCEC14A9E009FC59B67E7F423DBEE9C9E’
)

Create new column encrypted key

USE [SQL2016DEMO]
CREATE COLUMN ENCRYPTION KEY [Demo_Always_Encrypted_CEK]
WITH VALUES
(
COLUMN_MASTER_KEY = [Demo_Always_Encrypted_CMK],
ALGORITHM = ‘RSA_OAEP’,
ENCRYPTED_VALUE = 0x016E008000630075007200720065006E00740075007300650072002F006D0079002F003000390064003600300037006500640063006500630031003400610039006500300030003900660063003500390062003600370065003700660034003200330064006200650065003900630039006500CED4B2CBFF72D5C7FFA919CCB674193B20576B5D819967A8427868689D99A637B204F1CE5939F70AE932819D30C76DA26E9F9D3BFA4CFFDA2543E4ADC6AA44B57C906CE8B5B410D3473C06EE1E06EBD034DACAC30E49F57530513BBE58C7B0FAAE8FB511B945168696E6D32E7AD603194036A1EB643C0D5CA22D4E0B4DC4571E6A76878B4D611C863355C528DEF35833B614A150303EEA5A32442F05688C10F6821A361A649AF8E45F524A0C9830D5C0326EAD8C47028E2823D98E4CAAB9885EC1058DE48C857054BD8E7DAA09791116FF3760393A558FA062D883E5D4C907AFC2E383199CA7350971E68A97E5619E9C083A75080CEA7D20AF50E516145C3A9B
)

Create table using the Always Encrypted feature

ENCRYPTION_TYPE = Randomized (More secure but can’t be indexed and can’t be used in operation can be used for write / display only)
ENCRYPTION_TYPE = Deterministic (can be used for certain operations (point lookups, distinct, group by) and can be indexed)

CREATE TABLE dbo.Demo_Always_Encrypted
(
ID INT IDENTITY(1,1) PRIMARY KEY,

LastName NVARCHAR(45),
FirstName NVARCHAR(45),
BirthDate DATE ENCRYPTED WITH
(
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
COLUMN_ENCRYPTION_KEY = Demo_Always_Encrypted_CEK
),
SSN CHAR(10) COLLATE Latin1_General_BIN2
ENCRYPTED WITH
(
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
COLUMN_ENCRYPTION_KEY = Demo_Always_Encrypted_CEK
) );

NOTE: To insert data in this table should be through.Net application if we tried to insert data into this encrypted columns we would receive the below error

insert into Demo_Always_Encrypted
(LastName,FirstName)
values
(‘Elmasry’,’Mostafa’,’01/01/2016′,test’)

Msg 110, Level 15, State 1, Line 42
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Conclusion:

Data is encrypted at all times
Encryption keys are not stored on the server
Always on Encrypted columns support equality operators only in version 1
You’ll need to upgrade your client software to.NET 4.6
This is not TDE
Encrypted columns take significantly more space

Limitation

Currently only supported using ADO.NET as part of framework 4.6
ODBC and JDBC may be backed up in the future
CDC does not work on encrypted columns, but change tracking does. Only tracks changes of encrypted values
Replication is not officially supported – however Availability Groups, and Mirroring/Log Shipping is
Temporal tables cannot include encrypted columns

Triggers may fail if they reference encrypted columns

Performance:

Troubleshooting data issues become more complicated
Additional management overhead of having to install certificates on all clients
performance is significantly slower compared to non-encrypted inserts and updates