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!