Under the Hood: The Research Behind Microsoft SQL, part 3
Encryption at rest solves one threat. It stops someone who walks off with the disk. It does nothing about someone with administrative access to the running server, because the database decrypts data into memory to do anything useful with it.
For a lot of workloads that gap doesn’t matter. For health records, financial data, or anything where the customer’s threat model includes the cloud operator, it matters a great deal.
Azure SQL Database Always Encrypted (SIGMOD 2020) describes how SQL Server runs real queries, including range comparisons and LIKE patterns, against data it never holds the keys to.
The paper’s claim at the time was blunt (I love it, but we all know I’m biased):
“Microsoft SQL Server is the first and to date only industrial-strength database system that provides this kind of protection.”
The threat model is the interesting part
Most security features assume the attacker is outside. This paper assumes the attacker owns the machine:
“We introduce the strong adversary, who has unbounded power over the SQL Server process and can not only view the contents of the server’s memory/disk at every instant, along with all external and internal communication, but also tamper with it, for instance by attaching a debugger to SQL.”
Someone with access to a debugger attached to the SQL Server process. That’s the bar to protect against, and the team stepped up and met it. Reminds me of that movie, When a Stranger Calls, when the babysitter realizes the call is coming from inside the house (anyone??).
How the keys work
There are two levels. A column encryption key (CEK) is a 32-byte AES key that actually encrypts your column data. A column master key (CMK) encrypts the CEK. The CMK lives somewhere you control, like Azure Key Vault or an HSM, and never goes to the server.
The server stores encrypted data and an encrypted CEK. It cannot decrypt either one on its own. Your client driver can, because it can reach the CMK.
This is why the feature is transparent to applications. The driver calls a server procedure, sp_describe_parameter_encryption, to learn which parameters need encrypting and which keys are involved. It encrypts parameters on the way out and decrypts results on the way back. Your application code sees plaintext and mostly doesn’t know anything happened.
The hard problem: comparisons
Encryption comes in two flavors here, and the trade-off between them is the whole story.
Deterministic encryption maps the same input to the same ciphertext every time. That means equality works: the server can match WHERE LastName = @p by comparing ciphertext, and it can use a normal B-tree index. The cost is that it leaks frequency. If one encrypted value appears 40,000 times, an observer learns something even without decrypting it.
Randomized encryption uses a random initialization vector, so the same input encrypts differently every time. It leaks nothing beyond length. It also makes the data useless for querying, because equal values don’t look equal. I honestly had no idea before this paper that this was a thing that was possible. Really cool.
Version two solved that with secure enclaves. An enclave is a protected region of memory inside the SQL Server process that the host OS, the hypervisor, and administrators cannot inspect. Microsoft ported a subset of SQL Server’s expression evaluation into the enclave. Encrypted values go in, get decrypted inside the protected region, get compared, and only the boolean result comes out.
That unlocks range queries, BETWEEN, and LIKE pattern matching over randomized encryption.
The piece that makes this trustworthy is attestation. Before your client sends any keys to the enclave, it verifies the enclave is genuine: correct binary, correct signature, healthy platform, confirmed by an independent attestation service. Only after that check passes does the client establish a shared secret and hand over the column encryption keys. Without attestation, SQL Server could claim to have an enclave and simply lie.
Source: Figure 3 from Azure SQL Database Always Encrypted (SIGMOD 2020).
The paper is careful about what still leaks. Deterministic comparison leaks frequency. Enclave range comparison leaks ordering. LIKE against an index leaks ordering plus proximity. That honesty is worth noting, because “encrypted” is often sold as binary when it isn’t.
What it costs
Measured on TPC-C with six encrypted columns in the Customer table, where about 28% of the workload touched encrypted data, at 100 client threads:
- Plaintext baseline: 100%
- Plaintext over an Always Encrypted connection: 64% of baseline, mostly the extra metadata round trip
- Randomized encryption with enclave computation, four enclave threads: about 50% of baseline
Randomized-with-enclave ran 12.3% slower than deterministic encryption, with the authors noting “we expect this gap to narrow as we add further optimizations.”
So this definitely isn’t free. It’s a feature you apply to the columns that really need it, not the whole schema. But it’s a real option, running real queries, at a cost people accept when the alternative is not being able to move the workload to the cloud at all.
Where you see this today
- Always Encrypted is generally available across Microsoft SQL.
- Always Encrypted with secure enclaves adds in-place encryption, rich confidential queries, and index support on randomized columns.
- Key management goes through Azure Key Vault, and column master keys can now live in a managed HSM.
The enclave work also fed the broader Azure confidential computing effort, so this line of research spread well beyond the database.
What’s changed since the paper
Quite a lot, and most of it addresses the limitations the paper was honest about.
You no longer need special hardware. Azure SQL Database first shipped enclaves on Intel SGX, which meant choosing DC-series hardware. VBS enclaves are now available in Azure SQL Database across every offering, including elastic pools, in most regions. You turn them on by setting the database’s preferredEnclaveType property rather than picking a hardware generation. That removes the single biggest adoption blocker the feature had.
Worth knowing the tradeoff: VBS enclaves in Azure SQL Database currently have no enclave attestation, so they protect against high-privileged users in your own organization but not against an attacker who can replace the enclave binary. Intel SGX on DC-series, paired with Microsoft Azure Attestation, is still the stronger option and the one that matches the paper’s threat model. Azure Attestation also replaced the Host Guardian Service the paper describes.
The queries got richer. The paper covers comparisons and LIKE. SQL Server 2022 added JOIN, GROUP BY, and ORDER BY on encrypted columns inside the enclave. That moves encrypted columns from “you can filter on them” to “you can actually use them in real queries.”
One connection I like: Microsoft recommends enabling Accelerated Database Recovery before you create that first index. Undoing an index change on an encrypted column normally has to wait for an application to hand the enclave a key, which could take a very long time. ADR sidesteps most of that. It comes straight out of the Constant Time Recovery work which I blogged about previously, which is a nice example of two unrelated papers in this series depending on each other.
The performance gap the authors expected to narrow has narrowed, though I’d treat the paper’s TPC-C figures as a 2020 snapshot rather than what you’d measure today. You should always consider benchmarking your own workload.
Why this matters if you’re building for regulated industries
The usual answer to “we can’t put that data in the cloud” is a pile of compensating controls: tokenization services, application-side encryption libraries, and a lot of custom code that breaks your ability to query.
Always Encrypted moves that guarantee into the database engine, and keeps the query surface. You still write T-SQL. You still get indexes. The application still gets plaintext. The separation of duties between whoever runs the database and whoever can read the data becomes a property of the system rather than a policy in a binder.
For a developer, the practical takeaway is smaller and more useful: you can encrypt the four columns that actually matter, keep querying them, and keep the keys somewhere the database provider can’t reach.
Credits
Azure SQL Database Always Encrypted was written by Panagiotis Antonopoulos, Arvind Arasu, Kunal D. Singh, Ken Eguro, Nitish Gupta, Rajat Jain, Raghav Kaushik, Hanuma Kodavalla, Donald Kossmann, Nikolas Ogg, Ravi Ramamurthy, Jakub Szymaszek, Jeffrey Trimmer, Kapil Vaswani, Ramaratnam Venkatesan, and Mike Zwilling (Microsoft Azure and Microsoft Research).
Next steps
- Read the paper: Azure SQL Database Always Encrypted (SIGMOD 2020)
- Start here: Always Encrypted overview
- Go further: Tutorial: Getting started with Always Encrypted with secure enclaves
- Next in this series: A decades-old bottleneck on a single byte, and the lock-free fix
0 comments
Be the first to start the discussion.