SQL Server 2016 and Azure SQL Database Always Encrypted feature in action
SQL Server 2016 and Azure SQL database from v12 onwards offer a new security functionality called Always encrypted (*SQL Database will support in Preview the feature within the next month). Let's see what it does with a simple example.
Always encrypted feature promises you to protect your data from prying eyes and does that simply by storing the encrypted form only. SQL Server is totally unaware of the plain text data so nobody, not even sysadmins, can read your data. This is good because this separates the duties from sysadmin - who handles the SQL instance - and data admin - who handles the data consistency.
Enhanced ADO.net library
You might wonder: if the data is always encrypted, how can we get the unencrypted form? Must we write the cryptographic code ourselves?
The magic here is done by the Enhanced ADO.net library (from .Net 4.6 on). The library will handle the decryption/encryption automagically without special code: we will create our queries without worrying about encryption and the library will take care of everything. We don't even need to be aware of encrypted columns since the library discovers them at runtime.
Isn't it cool? Let's see it in action.
Sensitive data
For my example we are about to create very simple weight log table:
Selecting the data will give away your dieting efforts:
Of course we want our data to be kept private so we consider reversible encryption (especially if we are above the recommended weight ;)). Where do we store the certificate? SQL Server has all the necessary tools to support encryption and decryption. The problem is, if we store the encryption certificate in SQL Server any sysadmin can retrieve it and decrypt our data.
The solution is to encrypt the data offline and then store it in SQL Server: https://msdn.microsoft.com/en-us/library/mt163865.aspx.
Certificate
The first step is to select the encryption certificate. You can use the handy SSMS wizard (note: these screenshots are from SQL Server 2016 CTP 2.4 so are bound to be different from RTM):
If you don't have a valid certificate you can generate a self-signed one clicking on the specific button.
This wizard is equivalent to the following T-SQL:
Of course your thumbprint will be different. Note that we can specify a custom key store provider (more on this here: https://msdn.microsoft.com/en-us/library/mt147923.aspx): here we are accepting the windows certificate store. Also note that we are not storing the certificate in SQL Server, we are merely storing its address in the certificate store.
After we have created the column master key definition we must create the Column encryption key:
This will generate something like this:
Table creation
Now that we have everything in place all we need to do is to create the encrypted field table:
This table is virtually the same as before but here we are specyfing the COLUMN_ENCRYPTION_KEY and the ENCRYPTION_TYPE. COLUMN_ENCRYPTION_KEY specifies the encryption key (so, yes, you can pick a different key for each field) and ENCRYPTION_TYPE specifies whether the encryption will be deterministic or not.
Deterministic or not?
Deterministic encryption will give the same ciphertext from the same data.
So, for example, if we encrypt "OliveOil" once and we get the encrypted string "Bassaeedd33543aaa" we will always get "Bassaeedd33543aaa" for "OliveOil".
This is good because we can write a query like this: SELECT * FROM encTable WHERE food="Bassaeedd33543aaa" and benefit from index seeks. It's bad because we could infer from other rows the unencrypted data.
So if security is paramount we must use the randomized - ie non-deterministic - encryption and filter our data client-side.
Data insert
We cannot insert rows using SSMS with a simple:
Because we would get this error:
In order to interact with this table we can use the new ADO.net library. The library handles everything on its own provided we add Column Encryption Setting=enabled to the connection string. Aside from that it's just plain old Ado.net code:
Some restrictions apply (https://msdn.microsoft.com/en-us/library/mt163865.aspx). The most notable is the need to use parametrized queries... but you are not using concatenated tsql anyway, right ;)?
As you can see this code is fairly standard: the library will inspect the table (using sys.sp_describe_parameter_encryption system SP) and if it finds encrypted fields will look for the relative certificate, encrypt the value in the parameter and send the ciphertext. If you are worried about the performance impact of this operation you might want to fine tune its behaviour using the relevant SqlCommand overload (see https://msdn.microsoft.com/en-us/library/mt147923.aspx).
The full program code is in this gist. You can clone, compile and execute with:
1. clone with: git clone https://gist.github.com/MindFlavor/0a5302058a19ddc99105
2. Compile it: csc .a5302058a19ddc99105\Program.cs
3. Execute it (pass your SQL instance as parameter): Program.exe <your_instance_here>
It will go like this:
If you get:
Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '20-53-22-8E-69-67-F1-08-1D-A4'. Certificate with thumbprint '775FCBC49A4B9240BD51D2AED40BEC07077187A0' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store. Parameter name: masterKeyPath
it's because you forgot to import the certificate in the local machine's certificate store (or in current user, depending of the configuration). Also note that you need the private key so if you have imported the public key only you will get something like this:
Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '20-53-22-8E-69-67-F1-08-1D-A4'. Certificate specified in key path 'CurrentUser/My/775FCBC49A4B9240BD51D2AED40BEC07077187A0' does not have a private key to decrypt a column encryption key. Verify the certificate is imported correctly. Parameter name: masterKeyPath
Encrypted data in SQL
Are you curious on what a sysadmin will find on SQL Server?
Let's try it out:
Now my weight log is safely encrypted. Bear in mind that sysadmin can always modify the data so don't forget to audit the table access. Audit should always complement other security features.
Happy coding,
Francesco Cogno