Joey on SQL Server

Working with Secure Enclaves in Azure SQL Database

Microsoft announced a lot of Azure SQL news at Ignite this month, but few as critical to application development security than the public preview of Always Encrypted with secure enclaves. Here's how to get started with this new feature.

• By Joey D'Antoni
• 03/15/2021

At the recent Microsoft Ignite virtual conference, there were a few announcements around the Azure SQL (Microsoft's marketing term to describe all of its various SQL Server-related projects, both on-premises and in the cloud) platform. These announcements include the ability to patch Windows servers running on Azure without rebooting, and some enhancements to the Azure Synapse Analytics and Cosmos DB platforms.

However, what I wanted to focus on in this article is the public preview of Always Encrypted with secure enclaves in Azure SQL Database.

Encryption has always been challenging to implement, but if it is implemented infrequently, data breaches become much more damaging: If a bunch of encrypted data gets breached, it is not useful to anyone. If we think back to database encryption in SQL Server, until Always Encrypted was introduced, anyone who was a system administrator had access to the encryption keys, allowing them to view decrypted data. Always Encrypted changed that paradigm. Instead of storing encryption keys in the database, the keys that can decrypt data were stored in the client application. This meant administrators could only view the ciphertext (the result of the encrypted value) and not the plain text value.

Always Encrypted supports two types of encryption: deterministic (in which the value of the ciphertext will always be the same for the same seed value) and randomized (which provides a unique encrypted value for each record).

The way this worked was that your client application might issue a query such as:

DECLARE @AccountId INT=1234
SELECT Name FROM Customers WHERE AccountID=@AccountID

Since the application has the key, it encrypts the parametized value (you have to use a parameter) and submits the encrypted value to the database engine, as shown below:

SELECT Name FROM Customers WHERE AccountID='0x01700000016C006F00630'

While Always Encrypted was a big enhancement to security, the functionality was limited in terms of T-SQL functionality; you could only do equality queries. This meant your application had to do a lot of heavy lifting to sort and filter data. Additionally, these operations only worked on columns that used deterministic encryption (the same value, e.g., 1234, will always have the same encrypted value), which is a poor fit for data with a small range of unique values, such as birth dates or credit card verification numbers. Randomized encryption, where each instance of the data has a unique encrypted value, could only answer queries that scanned the entire column, like below:

SELECT Name FROM Customers

What that meant in practice is that Always Encrypted, while a very secure feature, could be challenging to implement in an existing application. The introduction of secure enclaves in SQL Server 2019 allowed for a richer set of data and index operations to take place on both randomized and deterministically encrypted data.

You may wonder how the database engine does that securely. The concept of confidential computing is fairly new and requires new hardware that contains Intel Software Guard Extensions (SGX) or virtualization-based security (VBS), which both allow for a secure area of CPU and memory that can perform operations on encrypted data. This works in conjunction with an external attestation service, which validates both the application and the server infrastructure, before allowing the server to operate on decrypted data. You can see the architecture of how this works with SQL Server in the below image:

The key difference here is that with secure enclaves in place, the database engine can send encrypted results into the secure enclave, where data operations can take place. Then the data is returned to the database engine, and in turn to the client operation in encrypted format. While the enclave is shown in its container, it is part of the SQL Server process on the server. The attestation service serves as a trusted party to validate that the data can be decrypted.

In my early experiments with Always Encrypted with secure enclaves, configuring an attestation service was a challenge. You need to configure the Host Guardian Service, which needs a Windows Server Failover Cluster (WSFC) and an independent Active Directory forest. This is not a show-stopper for enterprises, but for early-stage development it can be a challenge to get that level of infrastructure in place to test a new database feature.

Which brings us to the public preview of secure enclaves in Azure SQL Database. Instead of having to deploy a group of virtual machines to provide attestation, Azure offers a trusted attestation service that can be easily deployed. I was able to build out a demo environment in about an hour, even with a couple of code missteps on my part.

To take advantage of the public preview, you have to deploy your Azure SQL Database onto a DC-series virtual machine that has the Intel SGX extensions. Once you have done that and created an attestation service, you can follow the normal processes for encrypting columns using Always Encrypted. In my case, I used T-SQL commands in this tutorial, with SQL Server Management Studio to interact with the data (you will need version 18.4 or newer to pass in the attestation service), to encrypt the columns. I was able to add additional data and create indexes to validate performance and data operations. The supported data operations are listed below:

Comparison (less/greater than, equals)
MIN, MAX, AVG, STDEV
BETWEEN
IN
LIKE 
DISTINCT
Joins
Order and Group By (Only in Azure SQL Database)

Having all of these T-SQL operations available means Always Encrypted is available to a much broader set of applications without major code changes. That benefit, combined with the Azure Attestation service, means it's quite easy to deploy.

At a time when there are more threats to your data than ever, encrypting the sensitive data in your database is a fundamental part of secure application development. In the past, it has been difficult to manage encryption within databases, or even to fully secure data from high-privileged accounts. Always Encrypted initially provided the protection from administrators. With secure enclaves, it allows for rich querying that makes for straightforward application migration. Now that you can deploy secure enclaves with Azure SQL Database, it is a good time to try it with your application.