Exploration of SQL Server 2016 Always Encrypted – Part 2

Thursday Dec 10th 2015 by Greg Larsen
Share:

It is important to only allow authorized individuals access to confidential data.  Therefore when setting up an Always Encrypted table it is important to consider splitting configuration tasks between multiple individuals/groups to improve security.   Follow Greg Larsen as he shows how to setup up SQL Server Always Encrypted tables where database administrators can’t see the clear text confidential data.

In my last article (Exploration of SQL Server 2016 Always Encrypted – Part 1) I discussed how to setup and use the new SQL Server 2016 Always Encrypted feature, and the problem I had with restricting access from Database Administrators.  In this article I will expand my exploration of Always Encrypted.  I will explore the roles different individuals or groups should assume when setting up encryption keys and tables that contain Always Encrypted data.  By splitting the setup roles you can ensure only authorized individuals have access to Always Encrypted data.  Additionally I will show how I resolved the problem I had in Part 1 with Database Admins being able to see the Always Encrypted data.

Different Staff Role

As with anything security related there are always different roles individuals must play to keep things secure.   There are basically two types of access to always encrypted data.  Those that can see the encrypted data and those that can’t.    Additionally there are those that administer the application, which I’ll call Application Admins, and then there are those that administer the data and I’ll call them Database Admins.  In some large organizations you might even find the Security Admin staff might be involved in storing Always Encrypted data by creating and manage the certificate, the Column Master Key (CMK) and Column Encryption Key (CEK).   

In part 1, the Database Administrator created the CMK and CEK, and stored it on it on the SQL Server machine.  As we already saw in part 1, by doing this the Database Administrator could see the encrypted data.   To avoid this either the Application Admin, or the Security Admin needs to create the CMK and CEK.  Once the keys have been created they share the CMK key location and the CEK with the Database Admin, so the Database Admin can use the provided CMK and CEK to define a table that contains Always Encrypted columns.

Let’s see how to set up an Always Encrypted table where the Security Admin defines the certificate, the CMK and CEK, and then provide the Application Admin and Database Admin with the components they need to securely implement Always Encrypted.

Creating Certificate for Always Encrypted

In order to make it easy for the Security Admin to create a CMK and a CEK I set up another SQL Server virtual machine that was running SQL Server 2016 CTP3.   This way the Security Admin staff can use SSMS to define the CMK and CEK needed for Always Encrypted.   In addition to installing SQL Server 2016 on the Security Admin SQL Server machine I also installed the Windows SDK. I downloaded the SDK so I could get the makecert.exe.  I used the makecert.exe so I could create a self-signed certificate for testing Always Encrypted.

The first thing I do while assuming the role of the Security Admin is run the following markcert.exe command, from the command prompt:

makecert.exe -n
"CN=Part2_Demo" -pe -sr LocalMachine -r -eku
1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 -ss my -sky exchange -sp
"Microsoft Strong Cryptographic Provider" -sy 1 -len 2048 -a sha256

By running this command I created a certificate that gets stored in the LocalMachine key store on the Security Admin SQL Server machine.

Creating CMK and CEK

The next step I take as playing the Security Admin role is to generate and execute a script to create the Column Master Key (CMK).  To generate a script to create the CMK, I logged on to SSMS on my new Security Admin SQL Server 2016 machine.  I then created a database named SecAdmin.  After the database is created I then expanded the SecAdmin database, and Security item and then right clicked on the “Always Encrypted Keys”, which brought up the following menu.

SecAdmin
SecAdmin

Here I clicked on the “New Column Master Key…” item.  When I did this the New Column Master Key window was displayed:

New Column Master Key
New Column Master Key

On this screen I entered the name of my Column Master Key, which in this case is called “Demo_CMK”.  I used the drop down on the “Key Store” item to identify the certificates available in the LocalMachine key store.  As you can see there is only one.  It is the one I created with the makecert command I ran above.  After selecting the “Part2_Demo” cert I then clicked on the “Script” item and generated so I can generate the script to create the CMK.  Here is the script that was generated:

CREATE COLUMN MASTER KEY [Demo_CMK]WITH
(
     KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',     
     KEY_PATH = N'LocalMachine/My/61405FF54F0E2C2CA9879D0EDF9907D4988EC7BB'
)
GO

I reviewed the script and then executed it on an SSMS query window to create the Column Master Key on the Security Admin machine. 

Next I need to create the Column Encryption Key (CEK). I do this on the Security Admin machine, by expanding the Column Encryption Keys item in SSMS and then clicking on the “New Column Encryption Key…” option as shown below:

New Column Encryption Key…
New Column Encryption Key…

When I did this the New Column Encryption Key window was displayed. 

New Column Encryption Key Window
New Column Encryption Key Window

On this screen I entered the name of my CEK, which I called “Demo_CEK”.  I then used the drop down on the Column Master Key item to select the CMK I generated above, in this case that is “DEMO_CMK” item.  I then generated the script to create the CEK.  Here is the script that was generated:

CREATE COLUMN ENCRYPTION KEY [Demo_CEK]
WITH VALUES
(
       COLUMN_MASTER_KEY = [Demo_CMK],
       ALGORITHM = 'RSA_OAEP',
       ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F003600310034003000350066006600350034006
        6003000650032006300320063006100390038003700390064003000650064006600390039003000370064003400390038003800650063003700620062001DDC14E
        99DD9D38CCC086647378BAED93B3C605A375A6CEB81D71BC7117CF40F6E28ADD5C1C28F21E85FF367755939CBE2F4A43B6C40FDB6B047B2AB184A9E4FC882E09F6
        9AB2073BCD090C3863A6CD8D09C7D6317A65E17E84C8945447AA2A52D9A64AA2EAB4811DC32C0AD065A318DA881CE49D6CA90BAB7F3B687C0A2056011A8A7F4A71
        53F3FAE7C9E73614D0BFA458900A423C13FA935C4721C68587EAE75C67AF155BB7271927168AFA15BB33006031585365B72F506ABBB0E3D6B7E454DE62B5BF2538
        FB1D4D81E737C285B2880F441F3D89C3B7D8CE2E62AF10BA1EF79BC060FC567EF52CDE3BC154B48049E7BA1A4189354ABCAEBB1CF7457CCD49C2E182F58349FF08
        3770E6ECD74F9C72193037E7AE948EB35598AC79C9BBDF8EB60BCA037CD0A61CE2CF984FD3B626AAE23C790100852285B7B2276125E3D5919A8C3D05A9FD29AD44
        D81B65C611CBFDC8FE18D3D29C11B36AF2F5974192251C08676B4452128D89A7A560EC26891B561459A387E54043226DFAE7E2B7A3C472FF0F197ABE98896DE05C
        E39B5527B2EF8B2311A318F7CAE203D4CEABE8E9C00AC5BAA89392F7566782CFEA201A6764C33E54A4E8AEC2A160D7B5C4331048C430238B01D88735DE15935D24
        4F3EB1D08C9CCADD54654B748AE595745A44CE2C156D256B0A3A863DF4324009C258B131C827270D2D16DA397AB4AF875D81E973957
)

GO

Exporting Certificate from Security Admin’s SQL Server 2016 machine

Next I exported the certificate, still playing the Security Admin role.  I did this by running mmc.exe from the command prompt on my Security Admin machine.  When I ran the mmc.exe the following window was displayed:

Export the Certificate
Export the Certificate

On this window I expanded the “File” menu option and then selected the “Add/Remove Snap-in”.  When I did this the following window was displayed:

Add or Remove Snap-ins
Add or Remove Snap-ins

Here I clicked on the Certificate item and then clicked on the “Add>” button.  When I did this the following window was displayed:

Certificates Snap-in
Certificates Snap-in

On this window I clicked on the “Computer Account” radio button and then clicked on the “Next>” button.  When I did this the following window was displayed:

Select Computer
Select Computer

Here I verify that the “Local Computer” radio button was selected and then I clicked on the “Finish” button.  When I do this the following window is displayed:

Add or Remove Snap-ins
Add or Remove Snap-ins

On this screen I just clicked on the “OK” button.  When I do that the following window is displayed:

Console Root > Certificates (Local Compter) > Personal > Certificates
Console Root > Certificates (Local Compter) > Personal > Certificates

On this window I expanded the “Console Root” node and the other nodes and then clicked on the “Certificates” item under the “Personal” item.  When I did I was able to see the “Part2_Demo” certificate I created with the makecert command above. 

‘Part2_Demo’ Certificate
‘Part2_Demo’ Certificate

I then right clicked on the “Part2_Demo” certificate, hovered over “All Tasks” item and then select the “Export” item.  Upon doing that the following window was displayed:

Certificate Export Wizard
Certificate Export Wizard

On this screen I clicked on the “Next” button.    When I did this the following screen was displayed:

Certificate Export Wizard – Export Private Key
Certificate Export Wizard – Export Private Key

Here I selected the “Yes, export the private key” radio button and then clicked on the “Next” button, which brought up the following window:

Certificate Export Wizard – Export File Format
Certificate Export Wizard – Export File Format

On this window I just took the defaults and clicked on the “Next” button.   Upon doing that the following screen was displayed:

Certificate Export Wizard - Security
Certificate Export Wizard - Security

Here I checked the “Password” checkbox and then entered a password twice and then clicked on the “Next” button.  When I did this the following screen was displayed:

Certificate Export Wizard – File to Export
Certificate Export Wizard – File to Export

On this screen I typed in a location where I wanted my exported certificate to be stored and then I clicked on the “Next” button.  This brought up the following completion window:

Certificate Export Wizard – Completing the Certificate Export Wizard
Certificate Export Wizard – Completing the Certificate Export Wizard

Here I reviewed the settings and then clicked on the “Finish” button.  When I did this I got a message box that stated ”The export was successful.” 

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

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