RSS

SQL SERVER 2016 Always Encrypted

30 May

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.

1

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
)

GO

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
) );

 

2

 

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

 

3

Reference

 

 

 
Leave a comment

Posted by on May 30, 2016 in SQL Server 2016

 

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s