Microsoft introduced Always Encrypted with SQL Server 2016 as an approach to encrypting data at rest and in transit to protect personally identifiable information and financial transactions. Always Encrypted works by encrypting the data on the client side and hiding the encryption keys from the server. Without explicit permission and configuration, even database administrators cannot read the information stored in an encrypted column. This is obviously a significant step forward in security and allows users to confidently store information knowing only they control who can see it, whether it is in the cloud or on-premise. For more information on Always Encrypted, click here.
Imaginet has adopted Always Encrypted with several customers. Here are some of the key lessons we have learned from using it.
1. Columns Double in Size
You can expect the size of the columns you encrypt to roughly double. Depending on the number of columns you need to encrypt and the data type of those columns, this may be significant. We found this to be the most problematic with one customer that stores documents in their database as varbinary data. The database doubled in size when the document column was encrypted.
2. Consider datetime2 With Entity Framework
When using Always Encrypted with Entity Framework, consider designing or converting your database to use datetime2 for dates you plan to encrypt. Using datetime leads to type mismatch errors. Entity Framework uses datetime2. SQL Server can do the conversion for plain text columns, but not for encrypted ones.
3. Type Mismatch Errors When Decrypting
Although you can encrypt columns of type varchar(max), nvarchar(max) etc. it can be problematic to decrypt them. We frequently had type mismatch errors when decrypting. Using a non-max upper limit for columns that store a lot of text did not present the same issues.
4. Double Check Limitations Before Encrypting Data
There is a lengthy list of other limitations in the Microsoft documentation that describes when you cannot use Always Encrypted. Before diving into adopting it, make sure the data you need to encrypt does not fall into any of those limitations.
5. Differences Between Randomized and Deterministic Encryptions
The initial Always Encrypted offer allowed the choice of two encryption types: Randomized and Deterministic. If you require the ability to do equality comparison and indexing, you must choose Deterministic. However, you still will not be able to perform comparison (for example LIKE) or arithmetic operations. Also, because Deterministic encryption will encrypt a value the same way every time, Microsoft notes that this may allow users to guess information by pattern matching encrypted values.
The issue of being able to only do equality search on Deterministic columns presented a big problem for one of our customers. However, Microsoft had already been working on the solution to this issue. Enter Secure Enclaves.
Always Encrypted with Secure Enclaves, introduced in SQL Server 2019, addresses this limitation. Secure enclaves allow plaintext computations to occur inside a protect region of memory on the SQL server. Before permitting this computation to run the client drivers, verify that the enclave is genuine through an attestation process executed through Host Guardian Service. Secure Enclaves allowed us to use the more secure Randomized encryption, but still can perform queries using the LIKE operator and arithmetic operations such as COUNT. You can read more about Always Encrypted with Secure Enclaves here.
Need help with security?
Always Encrypted is a good option for data protection, and it is more robust than ever with the addition of secure enclaves. However, you should carefully evaluate what you need to do in both short and long-term before you adopt it and be aware of the limitations it has (which Microsoft has extensively documented).
As a Microsoft Gold Partner in Application Development, Cloud Platform, and DevOps, we’ve provided one-of-a-kind solutions for over 22 years. For help with your application, find out more about our Application Development services or contact us for a free virtual consultation.