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 3

By Greg Larsen

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.



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