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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted January 25, 2016

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

By Greg Larsen

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.



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