Imaginet blog
5 Lessons Learned With SQL Always Encrypted
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 always use 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 comparisons (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.
Secure Enclaves
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 a 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 protected 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.
Thank you for reading this post! If you enjoyed it, please check out some of our other content on this blog. We have a range of articles on various topics that I think you’ll find interesting. Also, don’t forget to subscribe to our newsletter to stay updated with our recent successful application development projects.
discover more
What Legacy Systems Can Teach Us About Resilience and Risk
Legacy systems often get a bad rap because of how rapidly tech is shifting. They’re labeled as outdated, clunky, and risky (for good reason). But what if we paused for…
Migrating to SharePoint Without Going Crazy: A Step-By-Step Guide
Imagine you’ve just bought your dream home. You’re excited about the move. You’ve been planning all the ways in which you’re going to decorate it. You’re picking out furniture and…
Why Developer Experience Matters in Enterprise Tools
Why Developer Experience Matters in Enterprise Tools With enterprise software, internal tools often get the short end of the stick. While customer-facing applications receive the majority of design attention, internal…
Let’s Build Something Amazing Together
From concept to handoff, we’d love to learn more about what you are working on.
Send us a message below or call us at 1-800-989-6022.