Always Encrypted feature in SQL Server 2016

SQL Server

Today, database security is more important than ever. With data breaches and hacking in the news, we all know we have to do more in the area of security. Some of these risks can be reduced by applying encryption and hashing techniques, but doing this can be difficult to implement so developers sometimes only encrypt passwords, credit cards, or Social Security numbers.

The new SQL Server 2016 hopes to make encryption easier via the new “Always Encrypted” feature. This new feature provides an easy way to ensure that the sensitive columns in your database are always encrypted. In order to address performance concerns, non-sensitve columns such as primary keys can be left unencrypted.

The actual process is handled in the database driver level. While the database only sees encrypted values, the application code works exclusively with unencrypted data. When your query is executed, the driver automatically looks up the master key in the Windows Certificate Store. The master key is then used to decrypt a column specific key, which in turn is used for encrypting and decrypting fields and parameters. Currently the only driver that supports this feature is .NET 4.6.

Microsoft offers three use-cases for Always Encrypted.

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

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.

The downside of deterministic encryption is that is can “allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column”. This is especially true when there are a small number of possible values.

For more security, you can use random encryption. Like salting a password, this prevents guessing by ensuring that a given value’s encrypted representation is never the same twice. Microsoft continues,

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.

While there are limitations, we look forward to seeing this new feature in a test environment to verify if it works as well as expected.

The following Transact-SQL creates a column master key, column encryption key and a table with encrypted columns using the Always Encryption available in SQL Server 2016:

CREATE COLUMN MASTER KEY DEFINITION MyCMK
WITH (
     KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE', 
     KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420'
   );
---------------------------------------------
CREATE COLUMN ENCRYPTION KEY MyCEK 
WITH VALUES
(
    COLUMN MASTER KEY DEFINITION = MyCMK, 
    ALGORITHM = 'RSA_OAEP', 
    ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F003200660061006600640038003100320031003400340034006500620031006100320065003000360039003300340038006100350064003400300032003300380065006600620063006300610031006300284FC4316518CF3328A6D9304F65DD2CE387B79D95D077B4156E9ED8683FC0E09FA848275C685373228762B02DF2522AFF6D661782607B4A2275F2F922A5324B392C9D498E4ECFC61B79F0553EE8FB2E5A8635C4DBC0224D5A7F1B136C182DCDE32A00451F1A7AC6B4492067FD0FAC7D3D6F4AB7FC0E86614455DBB2AB37013E0A5B8B5089B180CA36D8B06CDB15E95A7D06E25AACB645D42C85B0B7EA2962BD3080B9A7CDB805C6279FE7DD6941E7EA4C2139E0D4101D8D7891076E70D433A214E82D9030CF1F40C503103075DEEB3D64537D15D244F503C2750CF940B71967F51095BFA51A85D2F764C78704CAB6F015EA87753355367C5C9F66E465C0C66BADEDFDF76FB7E5C21A0D89A2FCCA8595471F8918B1387E055FA0B816E74201CD5C50129D29C015895CD073925B6EA87CAF4A4FAF018C06A3856F5DFB724F42807543F777D82B809232B465D983E6F19DFB572BEA7B61C50154605452A891190FB5A0C4E464862CF5EFAD5E7D91F7D65AA1A78F688E69A1EB098AB42E95C674E234173CD7E0925541AD5AE7CED9A3D12FDFE6EB8EA4F8AAD2629D4F5A18BA3DDCC9CF7F352A892D4BEBDC4A1303F9C683DACD51A237E34B045EBE579A381E26B40DCFBF49EFFA6F65D17F37C6DBA54AA99A65D5573D4EB5BA038E024910A4D36B79A1D4E3C70349DADFF08FD8B4DEE77FDB57F01CB276ED5E676F1EC973154F86
);
---------------------------------------------
CREATE TABLE Customers (
    CustName nvarchar(60) 
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,
        ENCRYPTION_TYPE = RANDOMIZED,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'), 
    SSN varchar(11) 
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,
        ENCRYPTION_TYPE = DETERMINISTIC ,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'), 
    Age int NULL
);
GO
Advertisements

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