These days we regularly hear of large corporations getting hacked and sensitive information getting leaked. It’s become more important than ever to make sure that your data is secure. With that in mind I thought I would run through our recent experience of setting up Always Encrypted, a Microsoft SQL Server feature used for encrypting column data at rest and in motion, for a project we were recently working on.
Always Encrypted is used to encrypt data in a database on a per column basis. This means that you can choose which data is encrypted and preserve performance in instances where less sensitive data does not need to be encrypted. The idea is that encryption / decryption is done seamlessly before your application tries to read the data and therefore the application doesn’t even need to know that it came from an encrypted source. Also of interest, is that Always Encrypted sits in between your database and application, rather than at the database level. This means that if your database is compromised then the keys are stored separately and therefore your data remains safe.
This particular application is an MVC application using Entity Framework to access the database. Both the application and database are being hosted on Azure. The solution that worked best for us was to use Azure Vault to store the encryption keys and Azure Active Directory to control application level access to that vault.
All seemed good in theory but practice proved a little more challenging than expected. It seems that Always Encrypted has some limitations in it’s support of Entity Framework. An example of such a limitation is the line of code below:
| context.Patients.Where(p => p.SSN == “123-45-6789”); |
|---|
This line would result in the string literal being passed through to the database and compared to the encrypted column before Always Encrypted had the opportunity to decrypt the column. Another issue we ran into was a Entity Framework query resulting in an sql query that looks like:
| select FirstName + ‘ ‘ + LastName as FullName from Users |
|---|
Or in our instances stored procedures with similar concatenations. In this instance FirstName and LastName are encrypted and concatenating the strings at the database level before Always Encrypted had an opportunity to decrypt them resulted in errors.
| Operand type clash: varchar is incompatible with varchar(50)
encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘CEK_Auto1’, column_encryption_key_database_name = ‘PB’) collation_name = ‘SQL_Latin1_General_CP1_CI_AS’ |
|---|
At first I thought we had a mistake in the configuration of Always Encrypted but after a fair amount of debugging and experimentation I uncovered these limitations mentioned above, and a number of others. We had to go through the application and find each of these database errors and re-factor the queries in a way that would force Entity Framework to do data manipulations at the application level rather than the database level to ensure that it was working with unencrypted data.
Luckily our testing team did a great job of tracking down all of the database errors so that we could fix them and ultimately meet the HIPPA requirements for this project.
