Edit

Share via

Run Transact-SQL statements using secure enclaves

  • Article

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

Always Encrypted with secure enclaves allows some Transact-SQL (T-SQL) statements to perform confidential computations on encrypted database columns in a server-side secure enclave.

Statements using secure enclaves

The following types of T-SQL statement utilize secure enclaves.

DDL statements using secure enclaves

The following types of Data Definition Language (DDL) statements require secure enclaves.

DML statements using secure enclaves

The following Data Manipulation Language (DML) statements or queries against enclave-enabled columns using randomized encryption require secure enclaves:

Note

Operations on indexes and confidential DML queries using enclaves are only supported on enclave-enabled columns that use randomized encryption. Deterministic encryption is not supported.

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.

DBCC commands using secure enclaves

DBCC (Transact-SQL) administrative commands that involve checking the integrity of indexes might also require secure enclaves if the database contains indexes on enclave-enabled columns using randomized encryption. For example, DBCC CHECKDB (Transact-SQL) and DBCC CHECKTABLE (Transact-SQL).

Prerequisites for running statements using secure enclaves

Your environment needs to meet the following requirements to support executing statements that use a secure enclave.

  • Your SQL Server instance or your database server in Azure SQL Database must be correctly configured to support enclaves and attestation, if applicable/required. For more information, see Set up the secure enclave and attestation.

  • When you're connecting to your database from an application or a tool (such as SQL Server Management Studio), make sure to:

    • Use a client driver version or a tool version that supports Always Encrypted with secure enclaves.

    • Enable Always Encrypted for the database connection.

    • Specify an attestation protocol, which determines whether your application or tool must attest the enclave before submitting enclave queries, and which attestation service it should use. Most tools and drivers support the following attestation protocols:

      • Microsoft Azure Attestation - enforces attestation using Microsoft Azure Attestation.
      • Host Guardian Service - enforces attestation using Host Guardian Service.
      • None - allows using enclaves without attestation.

      The below table specifies attestation protocols valid for particular SQL products and enclave technologies:

      Product Enclave technology Supported attestation protocols
      SQL Server 2019 (15.x) and later VBS enclaves Host Guardian Service, None
      Azure SQL Database SGX enclaves (in DC-series databases) Microsoft Azure Attestation
      Azure SQL Database VBS enclaves None

  • Specify an attestation URL that is valid for your environment if you're using attestation.

Prerequisites for running T-SQL statements using enclaves in SSMS

Download the latest version of SQL Server Management Studio (SSMS).

Make sure you run your statements from a query window that uses a connection that has Always Encrypted and attestation parameters correctly configured.

  1. In the Connect to Server dialog, specify your server name, select an authentication method, and specify your credentials.

  2. Select Options >> and select the Connection Properties tab. Specify your database name.

  3. Select the Always Encrypted tab.

  4. Select Enable Always Encrypted (column encryption).

  5. Select Enable secure enclaves.

  6. Set Protocol to:

    1. Host Guardian Service if you're using SQL Server.
    2. Microsoft Azure Attestation if you're using Azure SQL Database with Intel SGX enclaves.
    3. None if you're using Azure SQL Database with VBS enclaves.

  7. Specify your enclave attestation URL. Not applicable when the Protocol is set to None. For example, https://hgs.bastion.local/Attestation or https://contososqlattestation.uks.attest.azure.net/attest/SgxEnclave.

    Connect to server with attestation using SSMS

  8. Select Connect.

  9. If you're prompted to enable Parameterization for Always Encrypted queries, select Enable.

For more information, see Enabling and disabling Always Encrypted for a database connection.

Prerequisites for running T-SQL statements using enclaves in Azure Data Studio

The minimum recommended version 1.23 or higher is recommended. Make sure you run your statements from a query window that uses a connection that has Always Encrypted enabled and both the correct attestation protocol and the attestation URL configured.

  1. In the Connection dialog, select Advanced....

  2. To enable Always Encrypted for the connection, set the Always Encrypted field to Enabled.

  3. To enable secure enclaves, set the Secure enclaves field to Enabled.

  4. Specify the attestation protocol and the attestation URL.

    • If you're using SQL Server set Attestation Protocol to Host Guardian Service and enter your Host Guardian Service attestation URL in the Enclave Attestation URL field.
    • If you're using a DC-series database with Intel SGX in Azure SQL Database, set Attestation Protocol to Azure Attestation and enter the attestation URL, referencing your policy in Microsoft Azure Attestation in the Enclave Attestation URL field.
    • If you're using a database with VBS enclaves enabled in Azure SQL Database, set Attestation Protocol to None.

    Connect to server with attestation using Azure Data Studio

  5. Select OK to close Advanced Properties.

For more information, see Enabling and disabling Always Encrypted for a database connection.

If you plan to run parameterized DML queries, you also need to enable Parameterization for Always Encrypted.

Examples

This section includes examples of DML queries using enclaves.

The examples use the below schema.

SQL 
CREATE SCHEMA [HR];
GO

CREATE TABLE [HR].[Jobs](
 [JobID] [int] IDENTITY(1,1) PRIMARY KEY,
 [JobTitle] [nvarchar](50) NOT NULL,
 [MinSalary] [money] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
 [MaxSalary] [money] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
);
GO

CREATE TABLE [HR].[Employees](
 [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY,
 [SSN] [char](11) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
 [FirstName] [nvarchar](50) NOT NULL,
 [LastName] [nvarchar](50) NOT NULL,
 [Salary] [money] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
 [JobID] [int] NULL,
 FOREIGN KEY (JobID) REFERENCES [HR].[Jobs] (JobID)
);
GO

The below query performs an exact match search on the encrypted SSN string column.

SQL 
DECLARE @SSN char(11) = '795-73-9838';
SELECT * FROM [HR].[Employees] WHERE [SSN] = @SSN;
GO

The below query performs a pattern matching search on the encrypted SSN string column, searching for employees with the specified last for digits of a social security number.

SQL 
DECLARE @SSN char(11) = '795-73-9838';
SELECT * FROM [HR].[Employees] WHERE [SSN] = @SSN;
GO

Range comparison

The below query performs a range comparison on the encrypted Salary column, searching for employees with salaries within the specified range.

SQL 
DECLARE @MinSalary money = 40000;
DECLARE @MaxSalary money = 45000;
SELECT * FROM [HR].[Employees] WHERE [Salary] > @MinSalary AND [Salary] < @MaxSalary;
GO

Joins

The below query performs a join between Employees and Jobs tables using the encrypted Salary column. The query retrieves employees with salaries outside of a salary range for employee's job.

SQL 
SELECT * FROM [HR].[Employees] e
JOIN [HR].[Jobs] j
ON e.[JobID] = j.[JobID] AND e.[Salary] > j.[MaxSalary] OR e.[Salary] < j.[MinSalary];
GO

Sorting

The below query sorts employee records based on the encrypted Salary column, retrieving 10 employees with the highest salaries.

Note

Sorting encrypted columns is supported in SQL Server 2022 (16.x) and Azure SQL Database, but not in SQL Server 2019 (15.x).

SQL 
SELECT TOP(10) * FROM [HR].[Employees]
ORDER BY [Salary] DESC;
GO

Next steps

See also