Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


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.


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


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

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