SQL 2016 : Always Encrypted

Recently I have gone for Annual Summit of SSGAS2015 and got a chance to grasp some knowledge from some top SQL Server Expert and the main topic was SQL Server 2016.

There are a lot of new things coming up in this new version and one of the features which I like was Always Encrypted.

In the days of SQL Server 2005, column level encryption was allowed within database engine. We used to encrypt the sensitive data by an application and decrypted whenever is required. Post encryption data will be encrypted on disk, in memory and on the network. However column level application used to require changing the application code with the help of functions. e.g Encrypt By Key and Decrypt by Key.

When SQL Server 2008 came into existence it got option of TDE (Transparent Data Encryption) which will encrypt the database without having to change the application code or using any third party tool. We actually got two concepts in TDE. First are “In Rest” and “In Motion”. Data in rest will be encrypt but as soon as data will be in “In Motion” state like into memory or over the network, the information will decrypted automatically and therefore not protected.

Now with SQL Server 2016 Microsoft has introduced “Always Encrypted” option which includes end to end encryption without the need for application code change. Always Encrypted provides a separation between users who own the data and users who manage the data, such as administrators, because the encryption/decryption occurs at the client layer.

So your code will look like this

Create Table Contact

(

Contract_no nvarchar(60),

Contract_Short_Desc nvarchar(1000)

            Encrypt WITH (COLUMN_ENCRYPTION_KEY=CEK1,

            Encryption_Type=RANDOMIZED,

            ALGORITHAM=’Algoritham Value’

)

GO

There are two kind of Encryption Type available

Deterministic encryption - a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining tables based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. This weakness is increased when there is a small set of possible encrypted values, such as True/False, or North/South/East/West region.

Randomized encryption - a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns.

Two types of encryption keys are required for encryption: A Column Encryption Key (CEK) and a Column Master Key (CMK). The CEK is used to perform fast, synchronous data encryption while the CMK is used to encrypt the CEK asynchronously for high security.

We can get Algorithm value under Column Encryption Key Properties. Once we set the algorithm and enable the Column Encryption Setting enabled we will get the query result like below. 

There are some constraints as well available with Always Encrypted

   In Deterministic Encryption queries can perform equality comparison but we will not be able to use greater/less, pattern matching or any arithmetical operations.

   Queries cannot perform on columns encrypted by using randomized encryption

Currently, the only driver supporting Always Encrypted is SqlClient in .NET Framework 4.6.

     Following data types are not supported

o    Xml

o    Image

o    ntext

o    text

o    user defined data types

o    sql_variant

o    hierarchyid

Happy Reading

mesanjaybakshi@gmail.com