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

Monday Jan 25th 2016 by Greg Larsen
Share:

In this third article of the exploring SQL Server 2016 Always Encrypted series, Greg Larsen looks at the differences between an Always Encrypted column that uses an encryption type of Deterministic and those that use encryption type of Randomized.

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:

https://www.microsoft.com/en-us/download/details.aspx?id=49502

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.

Exploring AdventureWorks2016CTP3 Always Encrypted Data

Now that I’ve got the AdventureWorks2016CTP3 databases restored to my SQL Server 2016 instance, and have imported the certificate used to encrypt the Always Encrypted columns I’m going to explore the data in the AdventureWorks2016CTP3 database. 

First I need to change my connection setting to have the “column encryption setting = enabled”.  By doing this I’m able to see the encrypted data in SSMS in clear text.

Next I need to identify the tables in the AdventureWorks2016CTP that contains Always Encrypted columns.  I can do that by running the following code:

SELECT
  OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
 ,t.name AS table_name
 , c.name AS column_name 
 , c.encryption_type_desc  
FROM sys.columns c JOIN sys.column_encryption_keys k 
 ON c.column_encryption_key_id = k.column_encryption_key_id 
 JOIN sys.tables t ON c.object_id = t.object_id;


When I run this code I get the following output:

Schema_name  table_name  column_name      encryption_type_desc
------------ ----------- ---------------- ----------------------------
Sales        CustomerPII SSN              DETERMINISTIC
Sales        CustomerPII CreditCardNumber DETERMINISTIC
 

As you can see there is only one table that contains Always Encrypted data and that is the “Sales.CustomerPII” table.  In that table there are two columns that contain encrypted data: SSN, and CreditCardNumber.  Both of these columns are encrypted using the encryption type of deterministic.

Now that I have opened up my query window with the additional connection option of “column encryption setting=enabled”, I will be able to see the encrypted data in the Sales.CustomerPII table when I run the following query:

SELECT TOP 5 CustomerId, FirstName, LastName, SSN, CreditCardNumber
FROM Sales.CustomerPII;

When I run this SELECT statement I get the following output:

CustomerId  FirstName   LastName        SSN         CreditCardNumber
----------- ----------- --------------- ----------- ----------------
19169       Arianna     Bryant          816-14-3866 77778052342171
17423       Arianna     Alexander       513-30-2965 77776580079386
17722       Jerome      Serrano         319-51-1661 55559881820491
22728       Arianna     Russell         239-35-4068 33338016910017
29187       Jerome      Ramos           918-75-6173 11115373226233

As you can see I am able to see the clear text value in the SSN and CreditCardNumber columns.  If I didn’t want the DBA’s and system admins to see the encrypted values I could just remove the “column encryption setting = enabled” from my connection string that I used when I connected to a SSMS query window.

Engineering Some Additional Always Encrypted Data

Since the AdventureWorks2016CTP database only contained Always Encrypted data using a deterministic encryption type I will need to engineer some randomized encrypted data.  This needs to be done so I can show how searching for columns that are encrypted with an encryption type of deterministic, differs from columns that are encrypted with an encryption type of randomized. 

To engineer some additional Always Encrypted data I will be creating the following two new tables that contain Always Encrypted data: Sales.CustomerPII_Randomized and Compare.

To populate the Sales.CustomerPII_Randomized table I will be using the data in the Sales.CustomerPII table. If you remember from my Part 1 Always Encrypted article (http://www.databasejournal.com/features/mssql/exploration-of-sql-server-2016-always-encrypted-part-1.html) you cannot directly insert data into an Always Encrypted column using an INSERT statement from SSMS.  Therefore I will be using the C sharp code in the section label Code to Populating table Sales.CustomerPII_Randomized at the end of this article.  This code will insert the randomized Always Encrypted data into the Sales.CustomerPII_Randomized  table that I created with this CREATE TABLE statement:

CREATE TABLE [Sales].[CustomerPII_Randomized](
       [CustomerID] [int] NOT NULL,
       [FirstName] [dbo].[Name] NOT NULL,
       [LastName] [dbo].[Name] NOT NULL,
       [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], 
			ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
       [CreditCardNumber] [nvarchar](25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], 
			ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
       [EmailAddress] [nvarchar](50) MASKED WITH (FUNCTION = 'email()') NULL,
       [PhoneNumber] [nvarchar](25) MASKED WITH (FUNCTION = 'default()') NULL,
       [TerritoryID] [int] NULL
);

After running the C Sharp code at the end of this article to populate the Sales.CustomerPII_Randomized table, I can verify it contains data by running the following code:

SELECT TOP 5 CustomerId, FirstName, LastName, SSN, CreditCardNumber
FROM Sales.CustomerPII_Randomized;

When I run this command I get the same output as above when I selected the top 5 rows from the Sales.CustomerPII table.

Next I need to create some data so I can compare deterministics encrypted columns with randomized column.  To engineer this kind of data I will be creating the Compare table by using following CREATE TABLE statement:   

CREATE TABLE Compare (
   DeterministicString [nvarchar](11) COLLATE Latin1_General_BIN2 
                  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
                           ENCRYPTION_TYPE = Deterministic, 
                           ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'), 
   RandomizedString [nvarchar](11) COLLATE Latin1_General_BIN2 
                  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
                           ENCRYPTION_TYPE = Randomized, 
                           ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'), 
   );
 

I then populate this table using the C Sharp code that can be found in the section titled “Code to Populate Deterministic and Randomized fields“.

Now that all my sample data has been created, let me start researching using search, join, and grouping operations to see how these operations are affected by data that is encrypted with the two different encryption types.

Comparing the Different Encrypted Values by Encryption Type

As already stated deterministic encryption type Always Encrypted columns to the same encrypted value for same clear text column value.  Whereas randomized encryption type columns can have different encrypted values for the same clear text value.  To prove this let me run the following SELECT statement in an SSMS against the test data I created using the code in the section titled “Code to Populate Deterministic and Randomized fields”:

SELECT * FROM Compare;

When I run this SELECT statement I get the following output:

DeterministicString                                                                                                                                                  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
0x01C9010E5CAAFF2774A7F0E5E0EF24E4C6B2C913DB916E801244E4B99ACC0D126EA043C2914E803EEFAF4EC1DF27023BEB626606CE7E5EAAF68B7DCB2F52C2EFB8                                 
0x01C9010E5CAAFF2774A7F0E5E0EF24E4C6B2C913DB916E801244E4B99ACC0D126EA043C2914E803EEFAF4EC1DF27023BEB626606CE7E5EAAF68B7DCB2F52C2EFB8                                 
0x01C9010E5CAAFF2774A7F0E5E0EF24E4C6B2C913DB916E801244E4B99ACC0D126EA043C2914E803EEFAF4EC1DF27023BEB626606CE7E5EAAF68B7DCB2F52C2EFB8                                 
RandomizedString
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x0145A81EAE68078D50568C37241F430A080F4AE64A769E7295BAB924F417DBA38809F1B54D30D5EB8416BC006E3322C91DEAC6D63838AD8F4FBCCAD440D8F1074B
0x01BC968678E8445DF99411AB1BA91C403E930DC23CBE390A8F0CD5DA69A90FF139AF489E279E3616EF6AE22674CCDEBA7011CB7AF422B5EC54BE744587124095EA
0x01016FBE2074FD167D095F7091387FB0A30EEA7816C8A14DB9091A8036B576562898170C009012FD7D7AF642CF44C93F35F0BCBA642FB96E597FA1ADCBCFA2D947
 
Note: The above output has been reformatted for readability
 

If you review the output above you can see that same encrypted column value is created for each of DeterministicString column values. Whereas for each RandomizedString column value there is a different encrypted value for each row.  By performing this test I verified that randomized encrypted columns do in fact get different values when the same string of text is encrypted.

How the Encryption Type Affects Grouping Data

As already stated deterministic Always Encrypted columns can be used for grouping your data, whereas randomized Always Encrypted columns can’t.  To demonstrate this let me run the following code:

SELECT top 5 CreditCardNumber
           , count(*) AS NumberRecordWithCreditCardNumber 
FROM Sales.CustomerPII 
GROUP BY CreditCardNumber;

When I run this code I get the following output:

 CreditCardNumber          NumberRecordWithCreditCardNumber
------------------------- --------------------------------
33335999138597            1
77775866444377            1
11111459798908            1
11112723971521            1
33338568095365            1

Here we can see I can use a Deterministic Always Encrypted column to group the data.  Now let’s see what happens when I run this code:

SELECT top 5 CreditCardNumber
           , count(*) AS NumberRecordWithCreditCardNumber 
FROM Sales.CustomerPII_Randomized 
GROUP BY CreditCardNumber;

When I run this code I get the following output:

Msg 33299, Level 16, State 2, Line 22
Encryption scheme mismatch for
columns/variables 'CreditCardNumber'. The encryption scheme for the
columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name
= 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1',
column_encryption_key_database_name = 'AdventureWorks2016CTP3') and the
expression near line '4' expects it to be (encryption_type = 'DETERMINISTIC')
(or weaker).

Why did this happen?  This occurred because SQL Server 2016 doesn’t allow me to group on an Always Encrypted column that uses an encryption type of randomized.

These two tests helped proved that deterministic encrypted column values can be used in a GROUP BY clause, but randomized encrypted columns throw an exception when used in a GROUP BY clause.

How the Encryption Type Affects Searching

As mentioned, deterministic Always Encrypted columns can be using in equality operations, whereas randomized Always Encrypted columns cannot be used in an equality search operation.  To demonstrate this I will run the code below from SSMS:

SELECT CustomerID, CreditCardNumber 
FROM Sales.CustomerPII
WHERE CreditCardNumber = '11115373226233'; 

When I run this code I get the following output:

Msg 206, Level 16, State 2, Line 15

Operand type clash: varchar is
incompatible with varchar(8000) encrypted with (encryption_type =
'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',
column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name =
'AdventureWorks2016CTP3') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

Why did this error occur?   Wasn’t I supposed to be able to use the equality operator to search deterministic Always Encrypted columns?  Well that is not the case when I use SSMS.  I’m guessing this occurs because SSMS doesn’t have the hooks into the Always Encrypted driver code to encrypt the string “11115373226233” prior to performing the search operations.  

Instead of using SSMS to verify that an equality search operation works against a deterministic Always Encrypted column, I will use some C Sharp code that can be found in the code labeled “Code to Search CustomerPII Table” below.  Here is the guts of that code that defines the SELECT statement with an equality operator in the WHERE clause:

           // issue select statement 
            SqlCommand cmd= new SqlCommand("SELECT CustomerID, CreditCardNumber FROM Sales.CustomerPII WHERE CreditCardNumber = @CCN", conn);
            SqlParameter CCN = new SqlParameter("@CCN", System.Data.SqlDbType.NVarChar, 25);
            CCN.Value = "11115373226233";
            cmd.Parameters.Add(CCN);
            SqlDataReader reader = cmd.ExecuteReader();
 
            string CustomerID = "";
 
            // iterate through read buffer
            while (reader.Read())
            {
                //get CustomerID read
                CustomerID = reader["CustomerId"].ToString();
                //display CustomerID read
                MessageBox.Show("CustomerId = " + CustomerID);
            }

When I run this code I get the following message box display:

Message Box

Message Box

If you review the code above you will see that I’m trying to find the CustomerID in the Sales.CustomerPII table that has CreditCardNumber of "11115373226233".  As you can see by the message box my code found “CustomerID = 29187”.   By reviewing some of my output above you will see that this is the correct CustomerID value for the credit card number I used in the WHERE constraint.  

Now let me make a slight change to the .Net code that I executed to test if I can use the equality operation against a randomized Always Encrypted column.  The change I will make is to make my select statement search against the Sales.CustomerPII_Randomized table. Here is what that new SELECT statement looks like:

           // issue select statement 
            SqlCommand cmd= new SqlCommand("SELECT CustomerID, CreditCardNumber FROM Sales.CustomerPII_Randomized WHERE CreditCardNumber = @CCN", conn);

When I run my modified .Net C Sharp code I get the following error:

Additional information: Encryption
scheme mismatch for columns/variables '@CCN', 'CreditCardNumber'. The
encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED',
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',
column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name =
'AdventureWorks2016CTP3') and the expression near line '1' expects it to be
(encryption_type = 'DETERMINISTIC') (or weaker).

Here you can see that SQL Server 2016 will not let me use a randomized encrypted Always Encrypted column in a WHERE equality operation.

Let me do one last test.  This time I’m going to try to do a range operation against the AdventureWorks2016CTP3 Sales.CustomerPII table using the CredetCardNumber column.  Remember this column has an encryption type of deterministic.  To run this test I will tweak my SELECT statement to look like this: 

           // issue select statement 
            SqlCommand cmd= new SqlCommand("SELECT CustomerID, CreditCardNumber FROM Sales.CustomerPII WHERE CreditCardNumber < @CCN", conn);
            

If you review this code you will see the WHERE constraint operator is now using a less than (“<”) operator.

When I run my modified .Net C Sharp code I get this error:

Additional information: Encryption
scheme mismatch for columns/variables '@CCN', 'CreditCardNumber'. The
encryption scheme for the columns/variables is (encryption_type =
'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',
column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name =
'AdventureWorks2016CTP3') and the expression near line '1' expects it to be
(encryption_type = 'PLAINTEXT') (or weaker).

As you can see, I can’t even use an Always Encrypted column that uses deterministic encryption in a range constraint. 

Performing a Join Operation on an Always Encrypted Column

In order to see how the JOIN operator works differently with deterministic and randomize encryption I’m going to tweak my C Sharp two more times.  But first I will create a table that I can join against for this testing.  I will use this code to create this table:

CREATE TABLE JOIN_SSN (
   JOIN_SSN [nvarchar](11) COLLATE Latin1_General_BIN2 
                  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
                           ENCRYPTION_TYPE = Deterministic, 
                           ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
   );

Next I will populate this table with the following three SSN values: “319-51-1661”, “555-51-1661”, “918-75-6173”) using the C Sharp code found below in section Code to Populate JOIN_SSN table.

Now to verify I can perform a join operation against Always Encrypted data let me first try running the following query directly in SSMS:

SELECT CustomerID, CreditCardNumber 
FROM Sales.CustomerPII PII JOIN JOIN_SSN SSN 
ON PII.SSN = JOIN_SSN;
 
 

When I run this code I get the following output:

CustomerID  CreditCardNumber
----------- -------------------------
17722       55559881820491
29187       11115373226233

If you compare these CustomerID’s and CreditCardNumber’s with the values I displayed earlier in this article, you can see I successfully joined data using an Always Encrypted column that used Deterministic Encryption in SSMS.  Why could I do this in SSMS but not an equality operation?  This is because this time the SELECT statement doesn’t pass an unencrypted value to SSMS, but instead use joined columns that have the same data type and encryption type.

Now let me test to determine if I can perform a join operation using a randomized Always Encrypted column.  In order to perform that test, first I will need to create and populate a table to join against.  To create the table I will use the following code:

CREATE TABLE JOIN_SSN_Randomized (
   JOIN_SSN [nvarchar](11) COLLATE Latin1_General_BIN2 
                  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
                           ENCRYPTION_TYPE = Randomized, 
                           ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
   );

Once the table was created I used the C Sharp code in section Code to Populate JOIN_SSN_Randomized table at the end of this article to populate this table with sample test data.

Once my test data was all setup I ran the following code in SSMS to determine if I could join randomized encryption type columns:

SELECT CustomerID, CreditCardNumber 
FROM Sales.CustomerPII_Randomized PII JOIN JOIN_SSN_Randomized SSN 
ON PII.SSN = JOIN_SSN;

When I ran this code I got the following results:

Msg 33299, Level 16, State 2, Line 29
Encryption scheme mismatch for
columns/variables 'SSN', 'JOIN_SSN'. The encryption scheme for the
columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name
= 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1',
column_encryption_key_database_name = 'AdventureWorks2016CTP3') and the
expression near line '3' expects it to be (encryption_type = 'DETERMINISTIC')
(or weaker). 

From reading this message you can see SQL Server 2016 seems to expect a deterministic column, and not a randomized column.  This proves that a join operation doesn’t work when randomized encrypted type Always Encrypted columns are involved in a join operation.

Making Sure you Select the Correct Encryption Type

In this article I used the data in the AdventureWorks2016CTP3 sample database to prove that randomized Always Encrypted columns could not be used in an equality, group by operation, as well as deterministic Always Encrypted columns could not be used in a range operation.  Additionally I found that I could perform the GROUP BY operation against a deterministic Always Encrypted column but not a randomized encryption column.  I also found that I could run a query in SSMS to perform a join operation against deterministic Always Encrypted columns.  Due to all these limitations around Always Encrypted columns you need to make sure you select an encryption type depending on the type of operations you plan to perform against the Always Encrypted data.  If you want to perform an equality, group by or join operation against Always Encrypted data then the appropriate encryption type is deterministic, whereas randomized encryption type should be used for data that you just need to read and display.

Code to Populating table Sales.CustomerPII_Randomized

Here is the C Sharp code that reads from the Sales.CustomerPII table and then inserts the read records into the Sales.CustomerPII_Randomized table.

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Populated Sales.CustomerPII_Randomized table with data
class AlwaysEncryptedInsert
{
    //Read connection
    SqlConnection conn;
    //Insert connnection
    SqlConnection conn2;
    public AlwaysEncryptedInsert()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
        conn2 = new SqlConnection(
  "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
    }
 
   static void Main()
    {
        AlwaysEncryptedInsert scd = new AlwaysEncryptedInsert();
 
        scd.ReadInsertdata();
    }
 
    public void ReadInsertdata()
    {
        try
        {
            // open connections
            conn.Open();
            conn2.Open();
           // issue select statement 
            SqlCommand cmd= new SqlCommand("select * from Sales.CustomerPII", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            
 
            string CustomerID = "";
            string FirstName = "";
            string LastName = "";
            string SSN = "";
            string CreditCardNumber = "";
            string EmailAddress = "";
            string PhoneNumber = "";
            string TerritoryId = "";
 
            // iterate tbrough read buffer
            while (reader.Read())
            {
                //get column data
                CustomerID = reader["CustomerId"].ToString();
                FirstName = reader["FirstName"].ToString();
                LastName = reader["LastName"].ToString();
                SSN = reader["SSN"].ToString();
                CreditCardNumber = reader["CreditCardNumber"].ToString();
                EmailAddress = reader["EmailAddress"].ToString();
                PhoneNumber = reader["PhoneNumber"].ToString();
                TerritoryId = reader["TerritoryId"].ToString();
                
                // Issue insert into new table using randomized encryption
                SqlCommand com1 = new SqlCommand("insert into Sales.CustomerPII_Randomized values(@CustomerID, @FirstName, @LastName, @SSN, @CreditCardNumber, @EmailAddress, @PhoneNumber,@TerritoryId)", conn2);
                com1.Parameters.AddWithValue("@CustomerId", CustomerID);
                com1.Parameters.AddWithValue("@FirstName", FirstName);
                com1.Parameters.AddWithValue("@LastName", LastName);
                com1.Parameters.AddWithValue("@SSN", SSN);
                com1.Parameters.AddWithValue("@CreditCardNumber", CreditCardNumber);
                com1.Parameters.AddWithValue("@EmailAddress", EmailAddress);
                com1.Parameters.AddWithValue("@PhoneNumber", PhoneNumber);
                com1.Parameters.AddWithValue("@TerritoryId", TerritoryId);
                com1.ExecuteNonQuery();
            }
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
            if (conn2 != null)
            {
                conn.Close();
            }
        }
 
    }
    
}
 
 
 
 

Code to Populate Deterministic and Randomized fields

Here is the code to populate the table named “Compare “.

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Populate Tables to show differences between encryption types
class AlwaysEncryptedShowDiffernces
{
    //Insert connection
    SqlConnection conn;
    public AlwaysEncryptedShowDiffernces()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
 
    }
 
    static void Main()
    {
        AlwaysEncryptedShowDiffernces scd = new AlwaysEncryptedShowDiffernces();
 
        scd.Joindata();
    }
 
    public void Joindata()
    {
        try
        {
            // open connections
            conn.Open();
 
 
            string String1D = "ABC";
            string String1R = "ABC";
            string String2D = "ABC";
            string String2R = "ABC";
            string String3D = "ABC";
            string String3R = "ABC";
            // Populate Compare Table
 
            SqlCommand cmd = new SqlCommand("INSERT INTO Compare (DeterministicString, RandomizedString) Values (@String1D, @String1R),(@String2D, @String2R),(@String3D, @String3R)", conn);
            cmd.Parameters.AddWithValue("@String1D", String1D);
            cmd.Parameters.AddWithValue("@String2D", String2D);
            cmd.Parameters.AddWithValue("@String3D", String3D);
            cmd.Parameters.AddWithValue("@String1R", String1R);
            cmd.Parameters.AddWithValue("@String2R", String2R);
            cmd.Parameters.AddWithValue("@String3R", String3R);
            SqlDataReader reader = cmd.ExecuteReader();
 
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
 
        }
 
    }
 
}

 

Code to Search CustomerPII Table

This code will perform a WHERE constraint against an Always Encrypted column:

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Search Sales.CustomerPII table with data
class AlwaysEncryptedSearch
{
    //Read connection
    SqlConnection conn;
 
    public AlwaysEncryptedSearch()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
 
    }
 
    static void Main()
    {
        AlwaysEncryptedSearch scd = new AlwaysEncryptedSearch();
 
        scd.Searchdata();
    }
 
    public void Searchdata()
    {
        try
        {
            // open connections
            conn.Open();
 
            // issue select statement 
            SqlCommand cmd = new SqlCommand("SELECT CustomerID, CreditCardNumber FROM Sales.CustomerPII WHERE CreditCardNumber = @CCN", conn);
            SqlParameter CCN = new SqlParameter("@CCN", System.Data.SqlDbType.NVarChar, 25);
            CCN.Value = "11115373226233";
            cmd.Parameters.Add(CCN);
            SqlDataReader reader = cmd.ExecuteReader();
 
            string CustomerID = "";
 
            // iterate tbrough read buffer
            while (reader.Read())
            {
                //get CustomerID read
                CustomerID = reader["CustomerId"].ToString();
                //display CustomerID read
                MessageBox.Show("CustomerId = " + CustomerID);
            }
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
 
        }
 
    }
 
}

Code to Populate JOIN_SSN table

The code is used to populate the JOIN_SSN table:

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Populate Sales.JOIN_SSN table with data
class AlwaysEncryptedInsert
{
    //Insert connection
    SqlConnection conn;
    public AlwaysEncryptedInsert()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
 
    }
 
    // call methods that demo Determinstic and Randomized Always Encrypted Columns
    static void Main()
    {
        AlwaysEncryptedInsert scd = new AlwaysEncryptedInsert();
 
        scd.Joindata();
    }
 
    public void Joindata()
    {
        try
        {
            // open connections
            conn.Open();
 
           
            string SSN1 = "319-51-1661";
            string SSN2 = "555-51-1661";
            string SSN3 = "918-75-6173";
           // issue Insert statement 
            SqlCommand cmd= new SqlCommand("INSERT INTO JOIN_SSN Values (@SSN1),(@SSN2),(@SSN3)", conn);
            cmd.Parameters.AddWithValue("@SSN1", SSN1);
            cmd.Parameters.AddWithValue("@SSN2", SSN2);
            cmd.Parameters.AddWithValue("@SSN3", SSN3);
            SqlDataReader reader = cmd.ExecuteReader();
 
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
 
        }
 
    }
    
}

 

Code to Populate JOIN_SSN_Randomized table

The code to populate my JOIN_SSN_Randomized table:

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Populate Sales.JOIN_SSN table with data
class AlwaysEncryptedInsert
{
    //Insert connection
    SqlConnection conn;
    public AlwaysEncryptedInsert()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
 
    }
 
    // call methods that demo Determinstic and Randomized Always Encrypted Columns
    static void Main()
    {
        AlwaysEncryptedInsert scd = new AlwaysEncryptedInsert();
 
        scd.Joindata();
    }
 
    public void Joindata()
    {
        try
        {
            // open connections
            conn.Open();
 
           
            string SSN1 = "319-51-1661";
            string SSN2 = "555-51-1661";
            string SSN3 = "918-75-6173";
           // issue Insert statement 
            SqlCommand cmd= new SqlCommand("INSERT INTO JOIN_SSN_Randomized Values (@SSN1),(@SSN2),(@SSN3)", conn);
            cmd.Parameters.AddWithValue("@SSN1", SSN1);
            cmd.Parameters.AddWithValue("@SSN2", SSN2);
            cmd.Parameters.AddWithValue("@SSN3", SSN3);
            SqlDataReader reader = cmd.ExecuteReader();
 
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
 
        }
 
    }
    
}

See all articles by Greg Larsen

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved