How to Disable Always Encrypted Feature in SQL Server 2016.

Hello, my name is Stefano Altamura and I’m an Italian PFE dedicated to SQL Server and Data Insights.
In my first post I want to talk about an approach that I often use to better understand how new features works.

A wonderful new feature in SQL Server 2016 is Always Encrypted. You can find lots of details here: https://msdn.microsoft.com/en-us/library/mt163865.aspx.

I created a little demo to show Always Encrypted to my clients.
After that I want to “disable” the feature and bring back my database to the original state.
SQL Server Management Studio provides a way to decrypt the data, but i realize how to do it only after some time.

My first thought was: let’s create a new table with the same schema, load the plain text data in the new table, drop the crypted version and rename the plain text version. An inelegant solution. I don’t like it and I don't want to use it!

I need a different approach.

Always Encrypted wizard offers the chance to generate a Powershell Script to set up the encryption to run it in a different moment.

This is what SQL Server Management Studio generates for my database:

 

 

The 2 more interesting lines are the last cmdlets at line 18 and 19:

The first command adds to the variable $encryptionChanges the encryption setting for the field CreditCardNumber on MyTargetTable.

The second cmdlet apply the settings on my database.

That's the way I like it!

A couple of changes in the code and I can decypher the column and have it in plain text:

I was happy about the result, but I was struggling how to accomplish the task using SQL Server Management Studio.
The answer is very simple: run again the Encryption Wizard and set the "Encryption Type" to plaintext.

 

 

Long story short:
If you cannot find a way, always remember that the "Script" button can help you!

 

 

Have fun!

 

Stefano