How to implement Always Encrypted?

With SQL Server 2016 was released a new feature called Always Encrypted. Allow encrypt both data in rest and data in flight, we can set encryption on column level. The EA support two types of encryption:

1. Deterministic - This type always generates the same ciphertext for any plaintext value. This method supports grouping, indexing, joins point lookups and etc. In some cases, users can guess value of column, such as Gender or State.

2. Randomized - This method is more secure because uses randomized encryption, which means the different ciphertext generates the same plaintext. Simultaneously there are PROS and CONS in this method and it is very limited.

There are a couple of datatypes which are not supported:

• XML

• FILESTREAM

• IMAGE

• TEXT

• NTEXT

• GEOGRAPHY

• TIMESTAMP

• ROWVERSION

• etc..

• You cannot provide alter on column level and encrypt that. You have to add new column and encrypt it and import data from another column.

• Operators LIKE or arithmetic functions are not supported as well as operations like greater/less than.

• You cannot create index on encrypted column

• Temporary table are nor supported

• Change data Capture does not works, you can track only ecnrypted values.

• Etc...

---

Always Encrypted uses two types of keys:

Column Master Key(CMK) - This key need to be accessible by applications client for encrypting and decrypting data. It saved in ADO.NET Library Information about CMK keys you can find in the system catalog [sys].[column_master_keys].

Column Encrypiton Key(CEK) - This CEK encrypt sensitive data stored in table's columns. Information about CMK keys you can find in the system catalog [sys].[column_encryption_key_values].

Now, I am gonna show you how to implement via Management Studio.

1) I have table dbo.Pigeons with uncrypted data

Demo table dbo.Pigoens here:

2) Right click on table dbo.Pigeons and choose Encrypt columns

3) Click on "Next"

4) In my case alter columns LastName by Randomized encryption and BirthDate by Deterministic encryption. As you can see there is a warning icon which informs me that there will be change on collation. After setup click on "Next"

5) I left default setting in this window. Click on "Next"

6) And another "Next"

7) Now, you can see summary of your setting and click "Finish"

6) Always encrypted has been impleneted successfully.

7) And now you can select your table and result will look like below:

How to implement Always Encrypted via PowerShell?

For more information, please visit official documentation on MSDN.

See you next time guys!