A quick Step by step guide on setting up always encrypted on the server as well as the client, and a quick approach on migrating existing tables to Always Encrypted.
Right click the database > Tasks > Encrypt Columns
Press Next on the Introduction screen (BTW nice touch with the image)
On the Column Selection Grid, identify the table and columns that need to be encrypted, in this example I am encrypt the password column and LastLogin Column. The Encryption key is CEK Auto by default, then press Next
Select the master key location, as you will see there are options for both Hosted as well as on premise systems, you can also see that the master key is created automatically. I am not sure if this is a typical self-signed certificate. Press Next
 |  |
On the validation screen you are provided with options to implement Always Encrypted immediately or generate a PowerShell script for scheduled deployment. Note the warning below. There is potential data loss if writes are being performed during this process. If you choose PowerShell a PS1 script is saved to the location specified in the location you select. Press Next
You can review the process by visiting the log link at the bottom of the page and then press Close.
Once created you will find the certificate listed in certificate store of the machine as well as
As well as a pointer to it under SSMS
If you already have a master key created you can create a table enforcing Always Encrypted at creation time as shown below
Note once a table has Always encrypted enabled you cannot perform DML on the table as Ad hoc queries executed directly in SSMS. The fact that all the data is encrypted outside of SQL limits these tables from interacting with most other features in SQL Server. To a large extent you can consider Always Encrypted as being mutually exclusive from other database features like in memory OLTP , replication etc. There is currently no documentation on how it behaves with multiple replicas in Always ON.
Once Always Encrypted is setup on the server the rest of the work is done on the client side. The first thing is to have .Net framework 4.6 installed. The connection string for the application
The rest of it is just normal Ado.net programming. Where the input data needs to be passed as SqlParameter. If you’re not familiar with .Net you can still test the setup using Import Export wizard within SSMS . Just make sure to enable Column Encryption Setting as shown below when selecting the destination
The above approach can be used when migrating existing tables to Always Encrypted as well.
References
https://msdn.microsoft.com/en-us/library/mt147923.aspx
Please Consider Subscribing
