I have been working with the ‘Always Encrypted’ feature within SQL Server with a legacy project running the full .Net Framework (4.7) along with Entity Framework 6.
I had been exploring the different SQL Server Encryption options available, firstly looking at TDE (Transparent Data Encryption), however support for that is limited to Enterprise Edition. I needed something that would work with both SQL Express 2016 and SQL Server 2016 Standard Edition. You can see from the SQL Server 2016 version comparison below, Always Encrypted is the best option that suits my particular needs.
| Feature | Enterprise | Standard | Web | Express |
|---|---|---|---|---|
| Row-level security | Yes | Yes | Yes 1 | Yes 1 |
| Always Encrypted | Yes | Yes 1 | Yes 1 | Yes 1 |
| Dynamic data masking | Yes | Yes | Yes 1 | Yes 1 |
| Basic auditing | Yes | Yes | Yes | Yes |
| Fine grained auditing | Yes | Yes 1 | Yes 1 | Yes 1 |
| Transparent database encryption | Yes | No | No | No |
| Extensible key management | Yes | No | No | No |
| User-defined roles | Yes | Yes | Yes | Yes |
| Contained databases | Yes | Yes | Yes | Yes |
| Encryption for backups | Yes | Yes | No | No |
Digging into the feature set, it actually provides support for ‘data at rest’ along with ‘data in transit’, with one caveat that the consuming projects must be running a modern version of the .NET Framework (4.6 or above).
The initial conversion of columns to use an encrypted format was relatively painless.
Using SSMS (I’m using version 17.x) right click on a table you would like to encrypt data for and select ‘Encrypt Columns’
Select the columns you wish to encrypt and specify the encryption type and key to use (in my case I let it generate a new key).
Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining tables based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. This weakness is increased when there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.
Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns.
Note of you have any constraints on the column in question you will need to delete those first and handle the constraint in your application directly.
When you progress to the next page in the wizard you will be prompted to configure your column Master Key. Currently, you can store a CMK in the Windows certificate store, Azure Key Vault, or a hardware security module (HSM). Once you configure your key and key store provider you can progress to the end and either generate a powershell script to run the changes later, or just execute the changes now.
Executing the update itself was very fast, the data in question is very small, the largest table probably having 3 million records with a couple of columns being encrypted and it took less than a minute on my modest development machine.
Once you have encrypted your data, you will need to update your calling applications to support the Always Encrypted feature. Update any connection strings for the database adding the following keyword:
Column Encryption Setting=EnabledIn theory that should be you up and running and your reads and writes should now be transparently encrypting your data.
In my case however, things got a bit more interesting when testing the application. Queries began failing with errors such as..
Encryption scheme mismatch for columns/variables ‘Name’. The encryption scheme for the columns/variables is (encryptiontype = ‘DETERMINISTIC’, encryptionalgorithmname = ‘AEADAES256CBCHMACSHA256’, columnencryptionkeyname = ‘CEKAuto1’, columnencryptionkeydatabasename = ‘FOO’) and the expression near line ‘4’ expects it to be (encryptiontype = ‘PLAINTEXT’) (or weaker).
Statement(s) could not be prepared.
and
The data types nvarchar and nvarchar(50) encrypted with (encryptiontype = ‘DETERMINISTIC’, encryptionalgorithmname = ‘AEADAES256CBCHMACSHA256’, columnencryptionkeyname = ‘CEKAuto1’, columnencryptionkeydatabasename = ‘Foo’) are incompatible in the equal to operator.
From the official documentation
Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.
Ok so all of this is supposed to happen transparently to my application, why am I getting encryption related errors? (specifially around queries in my case). It turns out there are some limitations in using Always Encrypted You can read more about some of these limitation here.
Digging a bit further it appears Entity Framework support for the encrypted columns is also limited, particularly around queries (including order by, filter, projections etc) and there are workarounds for various issues.
The following post covers how to resolve some of these issues https://blogs.msdn.microsoft.com/sqlsecurity/2015/08/27/using-always-encrypted-with-entity-framework-6/
In my case, remediation of the issues I was having required modifying any offending LINQ queries against the Entity Framework context with the encrypted data.
For example a LINQ query with an Order by would need to be changed to perform the order by on the client rather than the server, and thus would go from
context.Patients.OrderBy(e => e.Name);To the following, with the .ToList() returning the unsorted data from the server, decrypting in the process, followed by sorting the decrypted data on the client
context.Patients.ToList().OrderBy(e => e.Name);In my case I had to update filters, queries and projections using LINQ against my EF context throughout my application to get everything back to working order.
You can find the various different changes required to EF queries at the bottom of the following blog post