Getting Started with Always Encrypted Part 2
In this blog post I am going to continue discussing the new Always Encrypted feature in SQL Server 2016. There are 2 main aspects to Always Encrypted – first is generating the Column Master Key and Column Encryption Keys in the database where the encrypted database will be stored. Second is the usage of a specialized driver in the client applications so that the encrypted data can be used by the application. This driver allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server. This driver does this by automatically encrypting and decrypting sensitive data in the SQL Server client application. The driver encrypts the data in sensitive columns before passing the data to SQL Server, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data stored in encrypted database columns that are contained in query results. To make use of the driver, you have to ensure that the .Net framework version 4.6 or higher is installed on the client computer.
In this setup I have two separate machines – a server with SQL Server 2016 (named SQL2016A) installed and a client machine with Visual Studio 2013 installed – which I have named DevMachine. On my DevMachine (which has the .Net 4.6 framework installed), I've written a small C# application that makes a connection to my SQL Server 2016 instance and calls a stored procedure to insert some data (the code of which I'll reveal below).
Below is the CREATE TABLE statement I used in last week's tip for creating the encrypted columns in the dbo.People table.
USE AdventureWorks2016
Here are the stored procedures that my application will call to INSERT and SELECT data from the dbo.People table. Note that there is a SQL User in my database named Sam that the application will be running under. So, I also give Sam the permission to EXECUTE these stored procedures. 
In the C# application I've written, I'll set the connection string to my SQL2016A machine – in which I must specify a new option for making use of Always Encrypted. This is the 'Column Encryption Setting = Enabled' option. This allows DML operations against an encrypted table.
On my DevMachine (where the C# application will be running from) I have a certificate loaded that originally I created through setting up the Always Encrypted feature on my SQl2016A machine. Once I had the feature set up, I exported the certificate with the Private Keys to a file. I then imported that certificate on my DevMachine client machine. Once I had done that, I deleted the certificate from the SQLServer2016A machine. So, as of right now – the ONLY machine that has the certificate installed is my client DevMachine server. The certificate is not needed on my SQL server machine (and in fact, should NOT be there).
It is worth noting at this point that this deployment model may not be advantageous for your environment - especially if you have a large number of client machines that will be interacting with SQL Server. The reason is that each client must have the certificate located on the machine. In which case, storing the certificate in Azure Key Vault or another Hardware Security Module may be a better choice.
Here is a snippet of code from the application that calls the dbo.InsertPerson stored procedure. Notice that I have to use the Parameters object. Behind the scenes the driver is encrypting and decrypting the parameter values as necessary. The stored procedure sp_describe_parameter_encryption is used to do this.
Once my application has called the stored procedure and inserted the record on my SQL machine, I can run a normal SELECT statement against the table. Here we can see that the SSN and LastName columns are both encrypted. In fact, even though I am connected to the SQL instance as a sysadmin, I still cannot decrypt the data to see the actual contents. This is because the certificate is NOT located on the SQL machine (or any client machine that the sysadmin may be running queries from) – so my sysadmin account has no access to it.
USE AdventureWorks2016
GO
SELECT *
FROM dbo.People
Taking things a step further, I can try to use the new Column Encryption setting when connected to the SQL instance through the 'Additional Connection Parameters' option inside of SSMS:
I am able to connect just fine, but when I run a query to return data from the People table, I get an error. The reason is that this query was not executed from a machine where the certificate lives (the DevMachine box). Since the certificate does not exist on the SQL Server instance, there is no way for me to decrypt it. The only way the encryption/decryption can happen is if the certificate is available. This is why any client application that needs to use Always Encrypted must have the certificate loaded to that machine (OR – the case of web clients, have the certificate loaded to a central application server).
SELECT *
FROM dbo.People 
Now, if I log onto my DevMachine and issue the same query while using the 'Column Encryption Setting = Enabled' option, I am able to return the data in a decrypted fashion (which is how the application would use it). Very cool!!
SELECT *
FROM dbo.People 