Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted January 25, 2016

Exploring SQL Server 2016 Always Encrypted – Part 3 – Differences between Deterministic or Randomize Encryption

By Greg Larsen

This is the third article in my exploring SQL Server 2016 Always Encrypted series.  In this article I will be looking at the differences between an Always Encrypted column that uses encryption type of Deterministic and those that use encryption type of Randomized.   In order to review these two encryption types I will be showing you a number of examples of selecting, inserting, grouping and joining Always Encrypted columns. But first…

What is Deterministic and Randomize Encryption?

There are two different kinds of encryption types that SQL Server 2016 uses when encrypting Always Encrypted columns.  The two encryption types are Deterministic and Randomized. When a specific plain text value of a deterministic encryption type column is encrypted it always produces the same encrypted value. Whereas when a randomized encrypted type Always Encrypted column is encrypted it does it in a less predictable manner.  Meaning that SQL Server doesn’t always encrypt a single plain text value to the same encrypted value.

Based on my discussion with Microsoft staff, that are responsible for the code that encrypts and decrypts Always Encrypted columns, they have built the encryption code to meet the requirements documented in this white paper: http://tools.ietf.org/html/draft-mcgrew-aead-aes-cbc-hmac-sha2-05. In fact they have also shared the code that does the encryption, which can be found here: http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlAeadAes256CbcHmac256Algorithm.cs

If you look at the code you can see that initialization vector (IV) is different depending on the encryption type. An IV is just an arbitrary value/number that is used along with the encryption key when encrypting data. For deterministic encryption the IV value is derived from the plaintext value and the encryption key, whereas for randomized encryption, the IV value is set to a random value. Because the IV for deterministic columns use the plain text and the encryption key to determine the IV value, this means that the any two column values that have the same plain text value and encryption key will have the same IV value and therefore will have the same final encrypted value for an Always Encrypted column. On the flip side since every randomized Always Encrypted column is encrypted using a random value for the IV, each encrypted value of a randomized Always Encrypted column will most likely be different, unless the plain text value and the random IV value just so happens to be the same.

Because these two encryption strategies encrypt plan text data slightly differently, there are benefits and draw backs to using one over the other.  Since the randomized Always Encrypted column values are not always the same, it makes it harder for someone to hack away to determine the value of a randomized Always Encrypted Column value.  On the flip side of that an Always Encrypted column value is easier to hack because a specific plain text value will always be encrypted to the same encrypted value.  This makes the randomized Always Encrypted column more secure over the deterministic encrypted type value. Because a deterministic Always Encrypted column always encrypts to the same value, care should be taken in using this encryption type when the deterministic Always Encrypted column contains a small set of unique values.  The fewer unique values the easier it is for a hacker to guess the values.

Deterministic Always Encrypted columns can be used for grouping, filtering and joining tables, but cannot be used in a range operation; whereas the randomized Always Encrypted Columns can’t be used in equality or search operations, grouping, indexing, or be used for joining tables.  I will explore more about using both deterministic and randomized Always Encrypted columns in the following sections.

Test Data

In order to show the difference between deterministic and randomized encryption I will need some test data.  I will be using the data that comes in the AdventureWorks2016CTP3 database, but I will also be engineering some of my own sample data.  If you want to following and execute the scripts I’ll be showing you in your SQL Server 2016 environment then you can download the AdventureWorks2016CTP3 databases from here:


For that test data I will be generating I will also be creating some additional tables in the AdventureWorks2016CTP3 database.  I will be creating these additional test data table as I explore Always Encrypted columns in the different sections below.

Setting up SQL Server Environment

In order for me to show you the differences between deterministic and randomized encryption I want to be able to see the encrypted data in clear text format.  There are a number of different ways to do this.  In order to accomplish this I will be putting the certificate that is used to encrypt and decrypt Always Encrypted data, on my SQL Server 2016 machine.  Note I would never do this on a SQL Server 2016 machine where I wanted to hide the encrypted data from the DBAs and system administrators.  But for the purpose of this article it makes it easier to browse Always Encrypted data within a query window in SSMS, verses having to write a bunch of client .Net code to show unencrypted data values.

To import the certificate for the AdventureWorks2016CT3 database I use the following steps:

    1. Create a folder name C:\EncryptionCerts on my SQL Server 2016 virtual machine.  This folder could be anywhere with a name of your choosing.  Therefore I just decided to put them in this folder.  If you are following along you could create this folder in a different location with a different name to make sense in your environment.
    2. Once the folder was created I copied the AlwaysEncryptedCMK.pfx file that I downloaded from the Microsoft site listed above to the C:\EncryptionCerts folder. Note the pfx file is located in the following downloaded folder: ..\ SQLServer2016CTP3Samples.zip\Always Encrypted, where “..” represent the folder where I download the AdventureWorks2016CTP3 sample database.
    3. To import the certificate I double clicked on the pfx file, which brings up the following window:

      Welcome to the Certificate Import Wizard
      Welcome to the Certificate Import Wizard

    4. On this window I just take the default for the Store Location “Current User” and click on the “Next” button, which brings up the following window:

      File to Import
      File to Import

    5. On this window I just review the information and then click on the “Next” button.  Upon doing this the following window is displayed:

      Private key protection
      Private key protection

    6. On this window I enter the password for the certificate file, which is “AlwaysEncrypted”.  When I do that the following window is displayed:

Certificate Store
Certificate Store

    1. On this screen I just click on the “Next” button, which brings up the following window:

Completing the Certificate Import Wizard
Completing the Certificate Import Wizard

  1. Here I review the information and then click on the “Finish” button.  Upon doing this I get a message box that pops up that states “The import was successful.”

Now that my Always Encrypted certificate is stored on my SQL Server 2016 machine I just need to add the “column encryption setting=enabled” setting to the “Additional Connection Parameters” on the connections “Option” option when I bring up a query window in SSMS.

In addition to storing the certificate on my SQL Server 2016 instance, I also restore the database backup contained in the zip file when I downloaded the AdventureWorks2016CTP3.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM