Exploration of SQL Server 2016 Always Encrypted – Part 2

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
Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles