·
7 min read

Confidential computing using Always Encrypted with secure enclaves in SQL Server 2019 preview

SQL Server 2019 preview brings encryption technology to a broader set of scenarios by enabling rich confidential computing capabilities with the enhanced Always Encrypted feature, Always Encrypted with secure enclaves. Always Encrypted with secure enclaves allows rich computations on encrypted data, boosts performance when encrypting large columns of data or complex schemas, and enables customers to protect sensitive Personally Identifiable Information (PII) data when running rich queries.

Always Encrypted debuted in SQL Server 2016 as a solution for protecting sensitive data used during the processing of Transact-SQL queries. With Always Encrypted, the data is encrypted and decrypted on the client-side, and is not exposed in plaintext in memory of the SQL Server process. As a result, even DBAs and administrators of machines hosting the database can’t see plaintext data. This makes Always Encrypted a great way to keep your data secure, but it restricts computations that SQL Server can perform on the data.

The only operation SQL Server 2016 and 2017 support on encrypted database columns is equality comparison, providing you use deterministic encryption. For anything else, your apps need to download the data to perform the computations outside of the database. Similarly, if you need to encrypt your data for the first time or re-encrypt it later (e.g. to rotate your keys), you need to use special tools that move the data and perform crypto operations on a different machine than your SQL Server computer. These restrictions are not an issue if equality comparison is all your applications need and if the tables containing your sensitive data are small. However, many types of sensitive information, e.g. a person’s name or phone number, often require richer operations, including pattern matching and sorting, and it’s not uncommon for sensitive data to be too large to move outside of the database for processing.

To address the above challenges, Always Encrypted in SQL Server 2019 is enhanced with secure enclaves. A secure enclave is a protected region of memory that appears as a black box to the containing process and to other processes running on the machine, including the operating system. There is no way to view the data or code inside the enclave from the outside, which makes enclaves ideal for processing sensitive data. There are several enclave technologies that differ in how enclave isolation is accomplished. SQL Server 2019 preview uses a Windows Server technology called Virtualization Based Security (VBS), which relies on Hypervisor to protect and isolate enclaves.

A SQL Server 2019 instance can be configured to contain a secure enclave that is used for computations on data protected with Always Encrypted, which is illustrated in the diagram below. This secure enclave logically extends client applications’ trust boundary to the server side. While it is contained by the SQL Server environment, the secure enclave is not accessible to SQL Server, the operating system, or the database or system administrators. This means the enclave can safely perform cryptographic operations on sensitive data or decrypt the data to perform rich computations on the plaintext, without exposing the data to potential adversaries in the database environment.

If you are wondering how it all works under the covers, please see the online documentation and other resources listed at the end of this post. In the remainder of this article, we will focus on the benefits of Always Encrypted with secure enclaves and discuss the new scenarios the enhanced feature enables.

Boost performance of encrypting columns in large tables or complex database schemas

Always Encrypted with secure enclaves allows you to encrypt your data and re-encrypt it (e.g. to rotate column encryption keys) in-place, inside a server-side secure enclave. In contrast, without secure enclaves, crypto operations on columns protected with Always Encrypted requires loading the data to a trusted machine, where the operations are performed and then the data is uploaded back to the database. This process is prone to network errors and can take a long time if your sensitive data resides in large tables. In-place encryption with secure enclaves improves the resiliency and dramatically reduces the duration of cryptographic operations. As a result, it makes Always Encrypted a practical solution for protecting sensitive data in large tables.

Always Encrypted with secure enclaves also gives you the option to perform cryptographic operations using ALTER TABLE ALTER COLUMN Transact-SQL statements, which is particularly useful if you have a very large database schema that contains many database objects. Until now, cryptographic operations have required using tools such as the Always Encrypted wizard in SQL Server Management Studio (SSMS) or the Set-SqlColumnEncryption PowerShell cmdlet. These tools automatically handle all dependencies between the columns and other database objects, such as foreign key constraints, indexes, stored procedures, views, etc. Both SSMS and PowerShell leverage DAC Framework to detect, remove, and (after completing cryptographic operations) re-create all dependencies. This convenience, however, can be costly: if your database contains thousands of objects, the tools need to issue many queries to retrieve the metadata for your database, which can take a long time and consume a lot of resources, both on the server side and on the machine running the tool. When using Transact-SQL for cryptographic operations, you need to manually create scripts for handling dependencies. While this requires manual work, it gives you more control over handling dependencies and is typically a better or in some cases the only practical method for running such operations on databases with large schemas.

Please note in the current preview of SQL Server 2019 (CTP 2.1), in-place encryption is only supported when using ALTER TABLE ALTER COLUMN. SSMS and PowerShell will be updated later to take advantage of in-place encryption if your database configuration supports secure enclaves.

Here is an example of an ALTER TABLE ALTER COLUMN statement triggering a crypto operation. The example assumes the LastName column is not encrypted initially. Once the statement is completed, the column will become encrypted using randomized encryption and the specified key.

ALTER TABLE [dbo].[Patients]
ALTER COLUMN [LastName] [nvarchar](50) 
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH (ONLINE = ON)
GO

Note the use of the ONLINE=ON switch, which causes the operation to perform in the online mode to minimize interruptions for applications that may be querying the table. Also note that the statement must be issued over a connection with Always Encrypted enabled and the user must have access to the column master key, protecting the column encryption key (CEK1) that is being used. This requirement is the same when running the wizard or the above-mentioned PowerShell cmdlet. Full details on this can be found in the Getting started with Always Encrypted with secure enclaves using SSMS documentation.

Protect PII and other sensitive data while running rich queries

Besides making encryption and key rotation easier, the main goal of secure enclaves is to enable SQL Server to support rich computations on encrypted database columns, while preserving the security benefits of Always Encrypted. Equipped with a secure enclave, a SQL Server instance can delegate computations to the enclave, which decrypts the data and performs the requested operations on plaintext. SQL Server 2019 preview supports pattern matching using the LIKE operator and comparison operators (<, >, =, etc.) on columns using randomized encryption. The example below shows a query searching patient records based on the last name prefix (assuming the LastName column is encrypted):

DECLARE @SearchPattern nvarchar(11) = N'Ab%'
SELECT * FROM [dbo].[Patients] WHERE [LastName] LIKE @SearchPattern
GO

Enabling rich queries unlocks Always Encrypted to a much broader set of scenarios, including applications that process PII such as people’s names or phone numbers, which typically require more complex computations than just equality comparison supported in SQL Server 2016/2017. This makes both preventing insider attacks and meeting regulatory compliance requirements, such as the EU General Data Protection Regulation (GDPR), much easier. With secure enclave, you do not need to re-engineer your apps to load the data and implement your query logic inside the applications. Instead, you can continue running your rich queries inside the database.

Note: Rich computations are pending several performance optimizations and include limited functionality (e.g., you cannot create indexes to support rich computations yet). Therefore, they remain disabled by default in SQL Server 2019 CTP 2.1. You can see the online documents on how to enable rich queries here.

Conclusion and next steps

Always Encrypted with secure enclaves in SQL Server 2019 preview helps you protect your sensitive data from malicious insiders and cloud operators or malware while supporting richer processing of your data inside the database. Here are the types of applications that can particularly benefit from using the enhanced Always Encrypted technology:

  • Applications that store sensitive data in large tables. With in-place encryption, you can encrypt columns or change the encryption scheme of columns in large tables much faster, as you don’t need to move the data to a machine running an encryption tool, such as SSMS or PowerShell.
  • Applications using databases with large schemas, containing thousands of database objects. Always Encrypted with secure enclaves gives you the flexibility of triggering cryptographic operations using Transact-SQL and create custom scripts that typically handle database dependencies more efficiently, unlike the client-side tools (SSMS, PowerShell). This makes encrypting columns in databases with large database schemas feasible or much faster.
  • Applications that process PII or other types of sensitive information that require pattern matching or range queries to be performed inside the database. You can protect the confidentiality of your sensitive data without re-implementing your business logic inside your apps, which is often expensive or even impossible, depending on the size of your data.

Here are a few links to learn more information about Always Encrypted using enclaves:

We would love to learn about your scenarios and requirements for protecting sensitive data, partner with you on your Proof of Concept projects, and help you become early adopters of Always Encrypted with secure enclaves. We also welcome your feedback while we continue our journey to the RTM of SQL Server 2019 and beyond. Please contact us directly at [email protected] or by signing up for the SQL Server Early Adoption Program.

To get started with SQL Server 2019 preview, you can find download instructions on the SQL Server 2019 web page.

We look forward to working with and learning from you!

a person smiling for the camera
Jakub Szymaszek
Senior Program Manager, Azure Data

Related Posts