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
How to Utilize AI-Infused, Low-Code Development Tools in Microsoft Power Apps and Power Automate
Power Apps and Automate are the perfect tools for low-code development. With AI-infused features, you can quickly create custom apps and automate processes with minimal effort. Get started today and…
Strengthen Security With Enhanced Reporting Feature in Microsoft Azure Active Directory
Azure Active Directory’s Reporting Suspicious Activity feature helps you detect and respond to suspicious activities in your organization. Get real-time alerts, investigate suspicious activities, and take action quickly with our…
Five Tips to Choose the Right Tech Stack for Your Next Project
Choosing the right technology stack is crucial to the success of any software development project. It involves selecting the right set of tools and frameworks that can meet the project’s…
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.