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