Configure Always Encrypted on existing table with data

In the First blog post, we understood the basic concept of Always Encrypted, different keys and the available Encryption types and then in the second post we saw different limitations and not supported column types in Always Encrypted. Now in this blog post let’s see how the actual implementation to see how to configure Always Encrypted on existing table with data.

The wizard will provision the required keys and configure encryption for selected columns. If the columns, you are setting encryption for, already contain some data, the wizard will encrypt the data. The following example demonstrates the process for encrypting a column.

 

Configure Always Encrypted on existing table with data

Let’s see how to Configure Always Encrypted on existing table with data, we have SQLChamp_Emp table which has around 14500+ records. The records were inserted by a script which selects a random date and assigns it as DOB to each employee record.

 

Let’s use the Always Encrypted wizard by selecting Encrypt Column option from the context menu (the menu you get when you right click) of the database.

 

 

This Always Encrypted welcome page looks like this:

 

Once you click next you’ll be moved to the main screen (Column Selection) which allows you to select Columns to be encrypted and choose encryption type along with column encryption key.

Pay close attention to the screenshot below which shows a warning sign. This warning comes because there is already a Clustered index on Employeeno column which doesn’t qualifies this column for Always Encrypted, for the complete list of not supported types read this blog <provide link>

 

So I unchecked this column and selected DOB (Date of Birth) column & choose Deterministic encryption type.

 

Since I already had column encryption key, it was selected by default. However I choose to create a new one for this demo from the drop down menu as visible from screenshots below:

 

If you select pre-existing column encryption key you will get something like this, means no further action is required.

 

But I wanted to check what happens if I select to create a new column encryption key, and when I choose to create a new column encryption key SQL Engine gave me option to either choose the pre-existing column master key or create a new one and deploy that to a key vault.

 

Once the master key gets deployed you’ll get two options and a list of warnings (if any). You can choose whether to go-ahead directly with encryption or save the task as a PowerShell script if you want to execute it at a later time.

The first warning you see in the screenshot below is common and you’ll also get the same, this warns you not to do any DML operation on your table until the encryption is complete, and doing any DML operation may result in data loss.

The second warning is related to the performance of basic service tier of my database, since encryption is a resource intensive task this may lead to performance issues on my basic tier DB since DTUs are very limited.

 

I could have choose to get a PowerShell script and encrypt the data post changing the service tier of my database but since this is just a demo & I am not in a hurry, I selected the second option “proceed to finish now”

 

 

And finally my data is encrypted using Always Encrypted. So with this configure Always Encrypted on existing table with data completes.

Let’s cross verify if the data has been encrypted or not. To cross verify I again used SSMS and after querying the same table I found the data was encrypted.

 

Read here how to find Always Encrypted Wizard Log Report

Hope you got answer to your questions & Learn how to Configure Always Encrypted on existing table with data! Happy Learning!

feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp
Follow @Sarab_SQLGeek