Upravit

Sdílet prostřednictvím

Always Encrypted with secure enclaves

  • Článek

Applies to: SQL Server 2019 (15.x) and later - Windows only Azure SQL Database

Always Encrypted with secure enclaves expands confidential computing capabilities of Always Encrypted by enabling in-place encryption and richer confidential queries. Always Encrypted with secure enclaves is available in SQL Server 2019 (15.x) and later, as well as in Azure SQL Database.

Introduced in Azure SQL Database in 2015 and in SQL Server 2016 (13.x), Always Encrypted protects the confidentiality of sensitive data from malware and high-privileged unauthorized users: Database Administrators (DBAs), computer admins, cloud admins, or anyone else who has legitimate access to server instances, hardware, etc., but shouldn't have access to some or all of the actual data.

Without the enhancements discussed in this article, Always Encrypted protects the data by encrypting it on the client side and never allowing the data or the corresponding cryptographic keys to appear in plaintext inside the Database Engine. As a result, the functionality on encrypted columns inside the database is severely restricted. The only operations the Database Engine can perform on encrypted data are equality comparisons (only available with deterministic encryption). All other operations, including cryptographic operations (initial data encryption or key rotation) and richer queries (for example, pattern matching) aren't supported inside the database. Users need to move their data outside of the database to perform these operations on the client-side.

Always Encrypted with secure enclaves addresses these limitations by allowing some computations on plaintext data inside a secure enclave on the server side. A secure enclave is a protected region of memory within the Database Engine process. The secure enclave appears as an opaque box to the rest of the Database Engine and other processes on the hosting machine. There's no way to view any data or code inside the enclave from the outside, even with a debugger. These properties make the secure enclave a trusted execution environment that can safely access cryptographic keys and sensitive data in plaintext, without compromising data confidentiality.

Always Encrypted uses secure enclaves as illustrated in the following diagram:

A diagram of the Data flow for Always Encrypted.

When parsing a Transact-SQL statement submitted by an application, the Database Engine determines if the statement contains any operations on encrypted data that require the use of the secure enclave. For such statements:

  • The client driver sends the column encryption keys required for the operations to the secure enclave (over a secure channel), and submits the statement for execution.

  • When processing the statement, the Database Engine delegates cryptographic operations or computations on encrypted columns to the secure enclave. If needed, the enclave decrypts the data and performs computations on plaintext.

During statement processing, both the data and the column encryption keys aren't exposed in plaintext in the Database Engine outside of the secure enclave.

Supported client drivers

To use Always Encrypted with secure enclaves, an application must use a client driver that supports the feature. Configure the application and the client driver to enable enclave computations and enclave attestation (see the Secure enclave attestation section below). For details, including the list of supported client drivers, see Develop applications using Always Encrypted.

Supported enclave technologies

Always Encrypted supports the following enclave technologies (or enclave types):

The type of the enclave available for your database depends on the SQL product hosting it (Azure SQL Database vs. SQL Server) and (in the case of Azure SQL Database) the configuration of your database.

  • In SQL Server 2019 (15.x) and later, Always Encrypted supports VBS enclaves. (Intel SGX enclaves aren't supported.)

  • In Azure SQL Database, a database can use either an Intel SGX enclave or a VBS enclave, depending on the hardware the database is configured to run on:

    • Databases using the DC-series hardware configuration (available with the vCore purchasing model) use Intel SGX enclaves.
    • Databases using a configuration other than DC-series with the vCore purchasing model and databases using the DTU purchasing model can be configured to use VBS enclaves.

    Note

    VBS enclaves are currently available in all Azure SQL Database regions except: Jio India Central.

See the Security considerations section for important information on the level protection each enclave type provides.

Secure enclave attestation

Enclave attestation is a defense-in-depth mechanism that can help detect attacks that involve tampering with the enclave code or its environment by malicious administrators.

Enclave attestation allows a client application to establish trust with the secure enclave for the database, the application is connected to, before the app uses the enclave for processing sensitive data. The attestation workflow verifies the enclave is a genuine VBS or Intel SGX enclave and the code running inside it is the genuine Microsoft-signed enclave library for Always Encrypted. During attestation, both the client driver within the application and the Database Engine communicate with an external attestation service using a client-specified endpoint.

Always Encrypted can use one of the two attestation services:

To enable Always Encrypted with secure enclaves for your application, you need to set an attestation protocol in the configuration of the client driver in your application. An attestation protocol value determines whether 1) the client app will use attestation, and, if so, 2) it specifies the type of the attestation service to it will use. The below table captures the supported attestation protocols for the valid SQL product and enclave type combinations:

Hosting product Enclave type Supported attestation protocols
SQL Server 2019 (15.x) and later VBS enclaves HGS, No attestation
Azure SQL Database SGX enclaves (DC-series databases) Microsoft Azure Attestation
Azure SQL Database VBS enclaves No attestation

A few important points to call out:

  • Attesting VBS enclaves in SQL Server 2019 (15.x) and later requires HGS. You can also use VBS enclaves without attestation (the latest client drivers are required).
  • With Intel SGX enclaves (in DC-series databases) in Azure SQL Database, attestation is mandatory and it requires Microsoft Azure Attestation.
  • VBS enclaves in Azure SQL Database don't support attestation.

For more information, see:

Terminology

Enclave-enabled keys

Always Encrypted with secure enclaves introduces the concept of enclave-enabled keys:

  • Enclave-enabled column master key - a column master key that has the ENCLAVE_COMPUTATIONS property specified in the column master key metadata object inside the database. The column master key metadata object must also contain a valid signature of the metadata properties. For more information, see CREATE COLUMN MASTER KEY (Transact-SQL)
  • Enclave-enabled column encryption key - a column encryption key that is encrypted with an enclave-enabled column master key. Only enclave-enabled column encryption keys can be used for computations inside the secure enclave.

For more information, see Manage keys for Always Encrypted with secure enclaves.

Enclave-enabled columns

An enclave-enabled column is a database column encrypted with an enclave-enabled column encryption key.

Confidential computing capabilities for enclave-enabled columns

The two key benefits of Always Encrypted with secure enclaves are in-place encryption and rich confidential queries.

In-place encryption

In-place encryption allows cryptographic operations on database columns inside the secure enclave, without moving the data outside of the database. In-place encryption improves the performance and the reliability of cryptographic operations. You can perform in-place encryption using the ALTER TABLE (Transact-SQL) statement.

The cryptographic operations supported in-place are:

  • Encrypting a plaintext column with an enclave-enabled column encryption key.
  • Re-encrypting an encrypted enclave-enabled column to:
    • Rotate a column encryption key - re-encrypt the column with a new enclave-enabled column encryption key.
    • Change the encryption type of an enclave-enabled column, for example, from deterministic to randomized.
  • Decrypting data stored in an enclave-enabled column (converting the column into a plaintext column).

In-place encryption is allowed with both deterministic and randomized encryption, as long as the column encryption keys involved in a cryptographic operation are enclave-enabled.

Confidential queries

Note

SQL Server 2022 (16.x) adds additional support for confidential queries with JOIN, GROUP BY and ORDER BY operations on encrypted columns.

Confidential queries are DML queries that involve operations on enclave-enabled columns performed inside the secure enclave.

The operations supported inside the secure enclaves are:

Operation Azure SQL Database SQL Server 2022 (16.x) SQL Server 2019 (15.x)
Comparison Operators Supported Supported Supported
BETWEEN (Transact-SQL) Supported Supported Supported
IN (Transact-SQL) Supported Supported Supported
LIKE (Transact-SQL) Supported Supported Supported
DISTINCT Supported Supported Supported
Joins Supported Supported Only nested loop joins supported
SELECT - ORDER BY Clause (Transact-SQL) Supported Supported Not supported
SELECT - GROUP BY- Transact-SQL Supported Supported Not supported

Note

The above operations inside secure enclaves require randomized encryption. Deterministic encryption is not supported. Equality comparison remains the operation available for columns using deterministic encryption.

The compatibility level of the database should be set to SQL Server 2022 (160) or higher.

In Azure SQL Database and in SQL Server 2022 (16.x), confidential queries using enclaves on a character string column (char, nchar) require the column uses a binary-code point (_BIN2) collation or a UTF-8 collation. In SQL Server 2019 (15.x), a_BIN2 collation is required.

For more information, see Run Transact-SQL statements using secure enclaves.

Indexes on enclave-enabled columns

You can create nonclustered indexes on enclave-enabled columns using randomized encryption to make confidential DML queries using the secure enclave run faster.

To ensure an index on a column that is encrypted using randomized encryption doesn't leak sensitive data, the key values in the index data structure (B-tree) are encrypted and sorted based on their plaintext values. Sorting by the plaintext value is also useful for processing queries inside the enclave. When the query executor in the Database Engine uses an index on an encrypted column for computations inside the enclave, it searches the index to look up specific values stored in the column. Each search might involve multiple comparisons. The query executor delegates each comparison to the enclave, which decrypts a value stored in the column and the encrypted index key value to be compared, it performs the comparison on plaintext and it returns the result of the comparison to the executor.

Creating indexes on columns that use randomized encryption and aren't enclave-enabled remains unsupported.

An index on a column using deterministic encryption is sorted based on ciphertext (not plaintext), regardless if the column is enclave-enabled or not.

For more information, see Create and use indexes on columns using Always Encrypted with secure enclaves. For general information on how indexing in Database Engine works, see the article, Clustered and Nonclustered Indexes Described.

Database recovery

If an instance of SQL Server fails, its databases might be left in a state where the data files might contain some modifications from incomplete transactions. When the instance is started, it runs a process called database recovery, which involves rolling back every incomplete transaction found in the transaction log to make sure the integrity of the database is preserved. If an incomplete transaction made any changes to an index, those changes also need to be undone. For example, some key values in the index might need to be removed or reinserted.

Important

Microsoft strongly recommends enabling Accelerated database recovery (ADR) for your database, before creating the first index on an enclave-enabled column encrypted with randomized encryption. ADR is enabled by default in Azure SQL Database, but not in SQL Server 2019 (15.x) and later.

With the traditional database recovery process (that follows the ARIES recovery model), to undo a change to an index, SQL Server needs to wait until an application provides the column encryption key for the column to the enclave, which can take a long time. Accelerated database recovery (ADR) dramatically reduces the number of undo operations that must be deferred because a column encryption key isn't available in the cache inside the enclave. Consequently, it substantially increases the database availability by minimizing a chance for a new transaction to get blocked. With ADR enabled, SQL Server still might need a column encryption key to complete cleaning up old data versions but it does that as a background task that doesn't impact the availability of the database or user transactions. You might see error messages in the error log, indicating failed cleanup operations due to a missing column encryption key.

Security considerations

The following security considerations apply to Always Encrypted with secure enclaves.

  • VBS enclaves help protect your data from attacks inside the VM. However, they don't provide any protection from attacks using privileged system accounts originating from the host. Intel SGX enclaves protect data from attacks originating from both the guest OS and host OS.
  • Using enclave attestation is recommended if it's available for your environment and if you're concerned about protecting your data from attacks by users with the OS-level admin access to the machine hosting your database. If you use attestation, you need to ensure the attestation service and its configuration are managed by a trusted administrator. Also, both supported attestation services offer different policies and attestation modes, some of which perform minimal verification of the enclave and its environment, and are designed for testing and development. Closely follow the guidelines specific to your attestation service to ensure you're using the recommended configurations and policies for your production deployments.
  • Encrypting a column using randomized encryption with an enclave-enabled column encryption key might result in leaking the order of data stored in the column, as such columns support range comparisons. For example, if an encrypted column, containing employee salaries, has an index, a malicious DBA could scan the index to find the maximum encrypted salary value and identify a person with the maximum salary (assuming the name of the person isn't encrypted).
  • If you use Always Encrypted to protect sensitive data from unauthorized access by DBAs, don't share the column master keys or column encryption keys with the DBAs. A DBA can manage indexes on encrypted columns without having direct access to the keys by using the cache of column encryption keys inside the enclave.

Considerations for business continuity, disaster recovery, and data migration

When configuring a high availability or disaster recovery solution for a database using Always Encrypted with secure enclaves, make sure that all database replicas can use a secure enclave. If an enclave is available for the primary replica, but not for the secondary replica, any statement that attempts to use the functionality of Always Encrypted with secure enclaves will fail after the failover.

When you copy or migrate a database using Always Encrypted with secure enclaves, make sure the target environment always supports enclaves. Otherwise, statements that use enclaves won't work on the copy or the migrated database.

Here are the specific considerations you should keep in mind:

  • SQL Server

    • When configuring an Always On availability group, make sure that each SQL Server instance hosting a database in the availability group support Always Encrypted with secure enclaves, and have an enclave and attestation configured.
    • When restoring from a backup file of a database that uses the functionality of Always Encrypted with secure enclaves on a SQL Server instance that doesn't have the enclave configured, the restore operation will succeed and all the functionality that doesn't rely on the enclave will be available. However, any subsequent statement using the enclave functionality will fail, and indexes on enclave-enabled columns using randomized encryption will become invalid. The same applies when attaching a database using Always Encrypted with secure enclaves on the instance that doesn't have the enclave configured.
    • If your database contains indexes on enclave-enabled columns using randomized encryption, make sure to enable Accelerated database recovery (ADR) in the database before creating a database backup. ADR will ensure the database, including the indexes, is available immediately after you restore the database. For more information, see Database Recovery.

  • Azure SQL Database

    • When configuring active geo-replication, make sure a secondary database supports secure enclaves, if the primary database does.

In both SQL Server and Azure SQL Database, when you migrate your database using a bacpac file, you need to make sure you drop all indexes for enclave-enabled columns using randomized encryption before creating the bacpac file.

Known limitations

Always Encrypted with secure enclaves addresses some limitations of Always Encrypted by supporting in-place encryption and richer confidential queries with indexes, as explained in Confidential computing capabilities for enclave-enabled columns.

All other limitations for Always Encrypted listed in Limitations also apply to Always Encrypted with secure enclaves.

The following limitations are specific to Always Encrypted with secure enclaves:

  • Clustered indexes can't be created on enclave-enabled columns using randomized encryption.
  • Enclave-enabled columns using randomized encryption can't be primary key columns and can't be referenced by foreign key constraints or unique key constraints.
  • In SQL Server 2019 (15.x) (this limitation doesn't apply to Azure SQL Database or SQL Server 2022 (16.x)) only nested loop joins (using indexes, if available) are supported on enclave-enabled columns using randomized encryption. For information about other differences among different products, see Confidential queries.
  • In-place cryptographic operations can't be combined with any other changes of column metadata, except changing a collation within the same code page and nullability. For example, you can't encrypt, re-encrypt, or decrypt a column AND change a data type of the column in a single ALTER TABLE/ALTER COLUMN Transact-SQL statement. Use two separate statements.
  • Using enclave-enabled keys for columns in in-memory tables isn't supported.
  • Expressions defining computed columns can't perform any computations on enclave-enabled columns using randomized encryption (even if the computations are among the supported operations listed in Confidential queries).
  • Escape characters aren't supported in parameters of the LIKE operator on enclave-enabled columns using randomized encryption.
  • Queries with the LIKE operator or a comparison operator that has a query parameter using one of the following data types (that become large objects after encryption) ignore indexes and perform table scans.
    • nchar[n] and nvarchar[n], if n is greater than 3967.
    • char[n], varchar[n], binary[n], varbinary[n], if n is greater than 7935.
  • Tooling limitations:
    • The only supported key stores for storing enclave-enabled column master keys are Windows Certificate Store and Azure Key Vault.
    • To trigger an in-place cryptographic operation via ALTER TABLE/ALTER COLUMN, you need to issue the statement using a query window in SSMS or Azure Data Studio, or you can write your own program that issues the statement. Currently, the Set-SqlColumnEncryption cmdlet in the SqlServer PowerShell module and the Always Encrypted wizard in SQL Server Management Studio don't support in-place encryption. Move the data out of the database for cryptographic operations, even if the column encryption keys used for the operations are enclave-enabled.
  • When you restore a VBS enclave-enabled database, it's essential to reconfigure the VBS enclave setting again.

Related content