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 February 1, 2016

Exploring SQL Server 2016 Always Encrypted – Part 4 – Encrypting Existing Data - Page 3

By Greg Larsen

Encrypting Existing SQL Server Data using the Always Encrypted Wizard

For my last method I will use the Always Encrypted Wizard.   I will be running the Always Encrypted Wizard on my CLIENT1 machine, and then pointing to my SERVER1 machine to encrypt the table name MyConfidential.  Since I have already encrypted this table I’m going to first drop the database where this table resides by running the following command:

DROP DATABASE Demo_Encrypt_Existing;

Once I have dropped this database I will then run the code from section Sample Data to create my sample data to encrypt.

To start the Always Encrypted Wizard I will bring up SSMS on my CLIENT1 machine.   When bringing up SSMS I will connect to SERVER1 where my Demo_Encrypt_Existing database resides.   I then expand this database until I can see my table named MyConfidentialData.  Next I will right click on my table to bring up the following menu:

MyConfidentialData Table Menu
MyConfidentialData Table Menu

On this menu to start the Always Encrypted Wizard I will select the “Encrypted Columns…” item.  Upon doing this the following window is displayed:

Always Encrypted Introduction
Always Encrypted Introduction

On this window I just click on the “Next>” button.  Upon doing that the following Column Selection window is displayed:

Always Encrypted Column Selection
Always Encrypted Column Selection

Here you can see the columns in my table.  I will select the SSN and DriverLicenseNumber columns by clicking on the checkbox.  In addition to checking the box I will also need to select the encryption type.  I will select Deterministic for SSN, and Randomized for the DriverLicenseNumber column.  When I get done selecting columns my window looks like this:

Always Encrypted Column Selection
Always Encrypted Column Selection

If you look at the window above you will see that I’ve got both the SSN and DriverLicenseNumber checked.  Also SSN is using Deterministic encryption type, where the DriversLicenseNumber is using Randomized encryption.   Also the wizard automatically set the name of the Encryption Key to “CEK_Auto1 (New)”.    Now that I’m done selecting columns to encrypt I click on the “Next” button.  When I do that the following Master Key Configuration window is displayed.

Always Encrypted Master Key Configuration
Always Encrypted Master Key Configuration

On this window I can select the key store provider for my encryption key.  I have two choices:  Windows certificate store or Azure Key Vault.  I just take the default, which is Window certificate store.  I also have the option to select the key source, in my case I’m just going to take the default, which is Current User.  When I click on the “Next>” button the following screen is displayed. 

Always Encrypted Validation
Always Encrypted Validation

Here you can see there are two ways to proceed.  I can either generate a PowerShell script, or have the wizard proceed.  Note the warning at the top of this screen.  When encrypting data it is important to make sure no one else is changing or inserting data into the table you are encrypting.   I just use the default value “Proceed to finish now”.  When I click on the “Next>” button the following window is displayed:

Always Encrypted Summary
Always Encrypted Summary

Here I review the summary, and then click on the “Finish” button.   When I do that the following window is displayed:

Always Encrypted Results
Always Encrypted Results

Once I review the output in this window I click on the “Close” button.  

At this point my data has been encrypted.   The  wizard also create the Column Master Key (CMK), which got stored in Certificate store on my CLIENT1 machine, as well as the wizard defined the Column Encryption key  on SERVER1.

Now let me verify my data has been encrypted, and the DBAs can’t see the encrypted data when logged onto SERVER1 using the “Column Encryption Setting=Enabled” connection properties.   First let me verify my data is encrypted by logging on to SERVER1 with no additional connection properties and running the following select statement:

SELECT SSN, DriverLicenseNumber from MyConfidetialData;

When I run this command I get the following output.

SSN                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------ 
0x012713226F8DF676B23D3F74535A453EF0F512A862F6D0D76AC804D64FE99E687889E7A45784F00B0D9EDE0367A151E292AE866D65A8473DC0C3F9E551718826AE 
0x0173FFA327C23DBD5C9C05E17C995C53601036A23518FDEA0377CDA4852744E5FD5444D67E1EC73CFB7A4E06C882CC4BCD39DA1CCA2D008E9B99FFC4A970A31947 
0x01CEBD1B420A229EBEC103E1FF6F32118CDCE0EF1F2B6CA30F406EDFAE4BB05DE35921A15FE283F52E8CCB1623B3809283B468750BDDD3D92FCD50D12D217D1625 
0x0178C288245CA5E02E0AC4329EEC95E1E77F472C8EB7E4B00A05C4C3AFFB43B500CC3E8C749C716C91E3AD41E1B16D54555E5383D775B478F3840444DC4DC23098 
0x01036D999EA3D3C1B787746671FEDAD092C7AD89E63F7F36C7F8C8A68CBBE58C9ACFE38C558769013E687DF0F8CD60E8A96D57F151B89521A02B0F11989B23D96D 
DriverLicenseNumber
------------------------------------------------------------------------------------------------------------------------------------
0x011F0728F7CFC5AAE6022239F980BB1358E7B4202149F2ED4B33AFDF46E43204766A2E66391ACBA982420D9E383A5757B2E5F37BE5DAECAEF94D1BF34E0F5E13A6
0x0182CD7DEA29C639F186A0D5B53EC92E7F2D9C7215C078807C718303542FAEAFE2F436FD2A573DF0D3E6697B3A7AA74A285AE38C763109FDB8D14161317AEF4012
0x010CA713707EB2F90042FD4631CC75CD83D6139C5E2BB05CF382E87AB7B9D139D1F695DBC5DF3334E5F4820E08439E9366CBFB3B036FDD905D6907BFDBC4CDD388
0x0126393EFA2431F1D5BD3BD83A98EE377D1D8FB2F1F79B274B70CB2F3550ACF6D43B52238BCB655E646A00DBA3CE024DC8BBF3064D1DA038D3CA19FA1DFE3BCFCF
0x01442316D2480002089FA95AB59AF86BE1E93A85CF911DD5F0C42DB4D275828CB906AC097813B67C82F2FC3D976B422911700B5A3228A73629E8F3188AAEAFD720

Note:  This output has been formatted for readability

As we can see these two columns do contain encrypted data.

Now let me connect with the additional connection property of “Column Encryption Setting=Enabled”.  Once connected with the additional connection property I run the same SELECT statement as above; when I do that I get the following error:

Msg 0, Level 11, State 0, Line 0

Failed to decrypt column 'SSN'.

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: 'F5-97-11-AF-D9-13-D0-CD-74-3B'.

Msg 0, Level 11, State 0, Line 0

Certificate with thumbprint 'D96CB4F691559E0B8753AF160F6663922C4ABE6F' not found in certificate store 'My' in certificate location 'CurrentUser'. 
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 indicates that I can’t decrypt the information when I run my SELECT statement on SERVER1.

The Always Encrypted Wizard is a simple way to encrypt your existing data.  With this method you don’t have to worry about creating keys, migrating your data to a temporary table and then renaming it, as you had to do with the other methods above.  With the wizard all these steps are accomplished behind the scenes.  But keep in mind there are some security concerns regarding using wizard to encrypt existing data.  The user that I used to run the Always Encrypted Wizard on CLIENT1 had sysadmin permissions on SERVER1.  I didn’t research if I could run the wizard with less permission than sysadmin.  Therefore keep in mind if you use the Always Encrypted Wizard to encrypt your data, you might need to temporarily provide the user that is encrypting data using the wizard with elevated permissions in order to use the wizard.  This fact alone might make using one of the other method of encrypting existing data more attractive from a security perspective.

For more information on the Always Encrypted Wizard please review information available here: https://msdn.microsoft.com/en-us/library/mt459280.aspx

Summary

Encrypting existing data isn’t that hard.  As you can see there are a number of different ways to encrypt your data, from wizard based to custom C sharp code. When you decide to encrypt existing data using one of these methods you need to make sure your database admins don’t get access to CMK, and/or non-admin users don’t end up with elevated server rights more than they would need to manage the application data.

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