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.

Darren Kuik

Darren Kuik is a Principal Consultant for Imaginet in Hamilton, ON. With over 20 years of experience as a software developer and solution consultant, he is highly engaged in identifying quality solutions that meet customer objectives and solve problems. His two decades of experience provide extensive familiarity with many modern technologies and a readiness to understand the customer’s business domain.

discover moree

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.

Imaginet blog

5 Lessons Learned With SQL Always Encrypted

SQL Saturday Part 2: Learning About Microsoft Fabric

My Trip to SQL Saturday Atlanta (BI Edition): Part 1

Enabling BitLocker Encryption with Microsoft Intune

Let’s Build Something Amazing Together