Microsoft introduced Always Encrypted with Secure Enclaves in SQL Server 2019. This allows the database layer to create a secure area in memory to decrypt encrypted data to plaintext. This is a big enhancement to Always Encrypted. This blog provides an introduction to that feature.
Vanilla Always Encrypted
Way back in 2015, I blogged about Always Encrypted. I don’t want to go into the full details, but basically encryption keys are stored at the application layer rather than the database layer. This protects sensitive data from even the highest-access individuals.
At the time, I saw a lot of potential but noted there were substantial limitations. Most notably, the inability to perform range scans on randomly encrypted columns. Note this is not an issue when using a deterministic encryption. Because there’s no way to unencrypt the data within SQL Server, there’s no way for SQL Server to determine that range. It’s left to the client to return the full amount of data and sort within the application. This isn’t very efficient as you’re returning the full data over the network and using application memory to filter data. On very large tables, this can be a nonstarter.
Always Encrypted with Secure Enclave
Always Encrypted with Secure Enclaves lets SQL Server allow computations on encrypted columns in SQL Server memory. The secure enclave is a secure black box area in memory that acts as a trusted environment. No other processes can access this environment and there is no way to access this from other processes on the host. How this is performed is a fairly simple process:
- The client sends the column encryption keys to the secure enclave over a secure channel
- SQL Server parses the application’s query and determines if the enclave will be required.
- SQL Server passes the encrypted data and keys to the enclave and all plaintext processing occurs in the enclave.
- Data and keys are encrypted when it leaves the enclave
As mentioned, many previous limitations around randomly encrypted columns can now be performed in the database. This allows the application to minimize data in motion and utilize non-clustered indexes on randomly encrypted columns.
Another benefit is in-place encryption. It’s common practice to rotate encryption keys. Without secure enclaves, this data must be taken out of the database and re-encrypted with the new key at the application layer. Again, with a large data set, this can be a burdensome process. With secure enclaves, this can be performed within the database.
SQL Server uses virtualization-based security (VBS) to create the secure memory enclave. At a very high level, VBS uses the windows hypervisor to create that region in memory.
Secure Enclave Attestation
The client needs to be able to verify the enclave is genuine. This process is called Attestation and is handled by the client driver. SQL Server uses the Windows Defender System Guard runtime attestation which in turn relies on Host Guardian Service (HGS). HGS configuration will be discussed in a future blog post. But for now, know that the SQL Server instance must register itself with the HGS—and your application must be configured to use HGS as well.
Recommendations and Gotchas
Microsoft strongly recommends enabling Advanced Database Recovery (ADR). Due to the way database recovery occurs—and that encryption keys are stored at the application layer—SQL Server may be stuck waiting for the application to connect before it can fully recover. ADR drastically reduces this chance, and if it does occur, enables it to occur as a background process.
Finally, HGS supports a Trusted Module Platform (TMP) or a host key. If the SQL Server is virtualized, there’s a good chance it doesn’t have TPM. In the case of Host Key, there’s an opportunity for a hypervisor administrator to dump the VM’s memory to plaintext. Therefore, it is highly recommended to use a virtual machine that supports TPM.
Stay tuned for more posts that dive into HGS setup and database-security hardening. If your organization needs help with any SQL Server issues, Anexinet has nearly two decades of experience, so please contact us for a direct database security evaluation.
SQL Server Architect