Semper Secura–Introduction To SQL Server Always Encrypted

With the introduction of SQL Server 2016 we now have a new way to encrypt columns called Always Encrypted. What makes it different from older encryption features in SQL Server like Column Encryption and Transparent Data Encryption is that it doesn’t just encrypt data at rest or while being transmitted on the wire, but only feature that ensures that the database never sees unencrypted values of sensitive columns, as data gets encrypted on the client tier via SQL Client\database driver. While the database only sees encrypted values, the application code works exclusively with unencrypted data. When a query is executed, the driver automatically looks up the master key in the Windows Certificate Store (or other OS-dependent location). The master key is then used to decrypt a column specific key, which in turn is used for encrypting and decrypting fields and parameters.

pic2

Microsoft advertises following use cases for Always Encrypted feature:

· Client and Data On-Premises

A customer has a client application and SQL Server both running on-premises, at their business location. The customer wants to hire an external vendor to administer SQL Server. In order to protect sensitive data stored in SQL Server, the customer uses Always Encrypted to ensure the separation of duties between database administrators and application administrators. The customer stores plaintext values of Always Encrypted keys in a trusted key store which the client application can access. SQL Server administrators have no access to the keys and, therefore, are unable to decrypt sensitive data stored in SQL Server.

· Client On-Premises with Data in Azure

A customer has an on-premises client application at their business location. The application operates on sensitive data stored in a database hosted in Azure (for example in SQL Server running in a virtual machine on Microsoft Azure). The customer uses Always Encrypted and stores Always Encrypted keys in a trusted key store hosted on-premises, to ensure Microsoft cloud administrators have no access to sensitive data.

· Client and Data in Azure

A customer has a client application, hosted in Microsoft Azure (e.g. in a worker role or a web role), which operates on sensitive data stored also stored in Microsoft Azure. The customer uses Always Encrypted to reduce security attack surface area (the data is always encrypted in the database and on the machine hosting the database).

pic1

 

Types of Always Encrypted encryption:

SQL Server offers two encryption modes: deterministic and random.

· Deterministic encryption ensures that a given value always has the same encrypted representation. This allows you to use the column for equality comparisons, joins, and grouping.

· For more security, you can use random encryption. This prevents guessing by ensuring that a given value’s encrypted representation is never the same twice.

You should use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records, or used to join tables.

Limitations.

If a column is encrypted, then all range-like operations such as greater/less than, pattern matching using LIKE, etc. are disallowed. Furthermore, you can’t pass encrypted values to functions, user-defined or otherwise, because the database doesn’t have access to the unencrypted values.

Equality comparisons can only be performed on columns that use deterministic encryption.

Indexes can only be applied to deterministically encrypted columns.

If joining between two columns, both columns need to use the same column encryption key.

Constants expressions that refer to encrypted columns are not allowed. For example, you cannot write WHERE SSN = ‘111-11-1111’, but you can write WHERE SSN = @SSN. This is necessary because the driver works with the SqlParameter class to handle encryption requirements.

Unsupported data types include: xml, rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, and user defined-types.

Current drivers that support these feature are listed below:

pic3

 

Here is a quick tutorial on how to set up Always Encrypted

· Open you SQL Server Management Studio and connect to your SQL Server 2016 instance or SQL Azure. As far as on premise SQL Server you can use AlwaysEncrypted feature in Enterprise or Developer Editions

pic4

· First thing we will create is Column Master Key. You can do so either in script or via nice wizard in SSMS. Below is my script to do so , but of course based on your certificate , machine, etc. your script will be different

CREATE DATABASE AEDemo;

GO

USE [AEDemo]
/****** Object:  ColumnMasterKey [test]    Script Date: 9/15/2016 6:44:15 PM ******/
CREATE COLUMN MASTER KEY [test]
WITH
(
	KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
	KEY_PATH = N'LocalMachine/My/A9B9AFE1993FA0FAD052F49195FDF10DE5A953DB'
)
GO

•    Next thing is to create Column Encryption Key. Again your actual script will vary from mine below or you can use nice SSMS wizard to do so

CREATE COLUMN ENCRYPTION KEY [test]
WITH VALUES
(
	COLUMN_MASTER_KEY = [test],
	ALGORITHM = 'RSA_OAEP',
	ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F0061003900620039006100660065003100390039003300660061003000660061006400300035003200660034003900310039003500660064006600310030006400650035006100390035003300640062003B237BB1F1C8A7ADBB0BEA972AB6FC321B7BAB43C6EEEF20CC4BBAC52BE1582868C7E9BF92B01D7022AF9838D625ED5F1CE2D35975BFF600F96A5A5A07EDFA946DBDE08DE896A632B06BAFC5A861DC8298E36E408BE5EBAAEB94C674C04765D0138F30B6B0E676294B5D3FF5374434273B823550D3A89D6337BEEE1D93FBDAB50025ED7A91BE2F2C3C032A636D0049F5614EC248097BD0B12F79FF3A51DC242987D931A2473EAB98BCB7217CFF61B447E435F5FE19BB5DCAF1B7C8D8FA606FE5354EB9773A78C1F1EEFCC4D3D401CB3C602EBB3C197A2421637304D94BC58F129F20A5685A700C6BEDF8D5080F959B57B3F65721DDEF78BBDA035923D3A93D3380E52465663A72916CE4DCF0D904C27DB8298D6F44AACC95998EA7CC1F895BBD55553523A5558778252A5E4AE2DED30D6DE04DB24ACE18771D0B8C27F06A228F67F950C10E0DA035D12934DA5DB5D65E947789EBA86A5375C71DC386FB854FCE702D642CB5ED695B96E864C3F9CC34E52D87178B2DF75B6AAE3A996B2F6BC9EB99445F6311142E8C3F85A545EE0614AFCAB02A547664B31E546FFAA7667D86D7DF239CB4EAB66B847EBD78BDD01707C7C7DE04EE8F52F5F7D722A71BC8E5015F9EFB2A0DBDEB9B732C163D8C32F9DA00AABCDBE68067715CB1C56A385B0EA908A51EC51F8290D701689E112C646A10462636766066F703D82844CA7237F721EF
)
GO

· Now that keys are created lets create table that uses these keys to encrypt columns

CREATE TABLE dbo.EncryptedTable
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  
  LastName NVARCHAR(32) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = DETERMINISTIC, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = test2
    ) NOT NULL,
    
  Salary INT 
    ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = RANDOMIZED, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = test2
    ) NOT NULL
);
GO

So essentially we followed this workflow:

pic6

Well , we can try to insert some data into a table

 

DECLARE @LastName NVARCHAR(32) = N'Gennady', @Salary INT = 720000;
INSERT dbo.EncryptedTable(LastName,Salary) SELECT @LastName, @Salary;
go

This  will actually error out, but why? Since encryption and decryption is occurring on driver level this will be way you populate these encrypted columns from client side code. You don’t have to make any changes to your data access layer of your application except addition of following to your connection string:]

Column Encryption Setting=Enabled

Together with rolling out your Column Master Key to the client it should make it easy for your application to work with AlwaysEncrypte

pic8

Great details on Always Encrypted can be found here –

BOL – https://msdn.microsoft.com/en-us/library/mt163865.aspx

Channel 9 – https://channel9.msdn.com/Shows/Data-Exposed/Getting-Started-with-Always-Encrypted-with-SSMS?ocid=relatedentry

As every security and encryption feature there is performance overhead to enabling Always Encrypted, Aaron Bertrand has attempted some benchmarking that is shown here – http://sqlperformance.com/2015/08/sql-server-2016/always-encrypted-performance-follow-up

Advertisements