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 December 10, 2015

Exploration of SQL Server 2016 Always Encrypted – Part 2 - Page 3

By Greg Larsen

Importing Certificate to Application Server

The next step is to place the certificate that I created on my Security Admin machine on my Application server.  To do that I switch roles and now play the role of the Application Admin.  The first step I performed was to copy the exported certificate file over to a folder on my application server.  Once that was done,  I then right click on the copied file and select the “Install PFX” task.   Upon doing this the following window is displayed:

Certificate Import Wizard
Certificate Import Wizard

Here I click on the “Local Machine” radio button and then click on the “Next” button.  When I do this the following screen is displayed:

Certificate Import Wizard – File to Import
Certificate Import Wizard – File to Import

Here I entered the location where the exported cert file was stored on my Application machine, and then I clicked on the “Next” button to bring up the next window in the import process:

Certificate Import Wizard – Private Key Protection
Certificate Import Wizard – Private Key Protection

On this window I enter the password for my exported cert file, and then I click on the “Next” button.  When I do that the following window is displayed:

Certificate Import Wizard – Certificate Store
Certificate Import Wizard – Certificate Store

On this screen I just took the defaults and then clicked on the “Next” button, which brings up the following window:

Certificate Import Wizard – Completing the Certificate Import Wizard
Certificate Import Wizard – Completing the Certificate Import Wizard

Here I review the import settings and then click on the “Finish” button.  When I did this a message box was displayed indicating my certificate was successfully imported to my Application server machine.

Creating CMK and CEK on SQL Server Machine

The next step is to place the CMK and CEK that I created on my Security Admin box on my SQL Server box and associate them with the DEMO database I created in my part 1 article.  I once again switch roles to the Database Admin, since I’m working on the database server now.   To create the CMK and the CEK I run the “CREATE MASTER KEY…” and “CREATE COLUMN ENCRYPTION KEY…” scripts that the Security Admin created above in the context of my DEMO database my SQL Server machine.

Now that I have my CMK and CEK in my DEMO database I run the following script to create my Always Encrypted table.  This script first drops my old Demo_Always_Encrypted table, since it was using the CMK/CEK I created in my Part 1 article and then I re-created the Demo_Always_Encrypted table using the new CMK/CEK I created in this article:

USE DEMO;
GO
DROP TABLE dbo.Demo_Always_Encrypted;
GO
CREATE TABLE dbo.Demo_Always_Encrypted 
(
  ID INT IDENTITY(1,1) PRIMARY KEY,

  LastName NVARCHAR(45),
  FirstName NVARCHAR(45),
  BirthDate DATE ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = RANDOMIZED, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = Demo_CEK
    ),
  SSN CHAR(10) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = DETERMINISTIC, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = Demo_CEK
) );

Testing to Verify SQL Server Sysadmin’s Cannot See Encrypted Data

The next step is to test to see if I can hide the clear-text values in my Always Encrypted table from a login that is a sysadmin on my SQL Server machine.  Prior to this testing let me first summarize what I’ve accomplished with all the steps above.

  1. I have created a Demo_Part2 certificate on my Security Admin server.  This certificate was then copied to my Application Server.
  2. I create a new CMK in my DEMO database on my SQL Server machine that identifies the location of the certificate on my application server.
  3. I created a  new CEK in my DEMO database on my SQL Server machine that references the CMK I created in step 2.
  4. I created the “Demo_Always_Encrypted” table in my DEMO database that will contain two different Always Encrypted columns, which are encrypted based on the CEK I created in step3.

All that is left is to test inserting a row into my Demo_Always_Encrypted”  table and verifying that the SQL Server Administrators can’t see the data in my Demo_Always_Encrypted table, even if they add the additional connection property of “Column Encryption Setting=Enabled” to their connection string. 

To insert records I run the C# code I created in Part 1 of this article (link to this article displayed above).   After inserting a record with my C# code I then used the Query Window inside of SSMS with the “Column Encryption Setting=Enabled” set.  I then run the following SELECT statement:

SELECT * FROM [DEMO].[dbo].[Demo_Always_Encrypted];

When I run this command I get the following error:

Msg 0, Level 11, State 0, Line 0
Failed to decrypt column 'BirthDate'.
Msg 0, Level 11, State 0, Line 0
Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. 
The last 10 bytes of the encrypted column encryption key are: '97-AB-4A-F8-75-D8-1E-97-39-57'.
Msg 0, Level 11, State 0, Line 0
Certificate with thumbprint '61405FF54F0E2C2CA9879D0EDF9907D4988EC7BB' not found in certificate store 'My'
in certificate location 'LocalMachine'. Verify the certificate path in the column master key definition 
in the database is correct, and the certificate has been imported correctly into the certificate location/store.
Parameter name: masterKeyPath

This error seems to indicate SQL Server can’t find the certificate that it needs to decrypt the encrypted data stored in my SQL Server Demo_Always_Encrypted table.   Of course it can’t because it is not in the LocalMachine key store on my SQL Server machine.  Remember I created this cert on my Security Admin machine and then imported it only into my application machine.

If I removed the “Column Encryption Setting=Enabled” from my connection string to SQL Server and re-connect and run the above SELECT statement I get the following results returned:

ID   LastName       FirstName      BirthDate                                           SSN

---- ------------- --------------  ------------------------------------------          --------------------------------------------------------

1    Larsen        Greg            0x010F34336855345BB9D8A1...                         0x0117323E402850A52C89811E6A7AB5519…

As you can see my BirthDate and SSN columns are encrypted, which proves that SQL Server sysadmin’s can’t see the Always Encrypted columns value in clear-text.  

Hiding Column Values from SQL Server SysAdmins

As this article showed, I could store values for both BirthDate, and SSN encrypted in my DEMO database and keep the clear-text values for these columns from the prying eyes of a person with SQL Server sysadmin rights.  I did this by making sure that the certificate used to support encryption didn’t reside in the Local Machine key store of my SQL Server machine.  Being able to use Always Encrypted functionality in SQL Server 2016 will make it easier for developers to encrypt sensitive data and meet auditor’s encryption requirements.    

See all articles by Greg Larsen



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