Run Transact-SQL statements using secure enclaves

Applies to: yesSQL Server 2019 (15.x) - Windows only YesAzure 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.

In SQL Server 2019 (15.x), confidential queries using enclaves on character string columns (char, nchar) require a binary2 sort order (BIN2) collation configured for the column. In Azure SQL Database, using BIN2 or UTF-8 collations is required.

DBCC commands using secure enclaves

DBCC (Transact-SQL) administrative commands that involve checking the integrity of indexes may 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 and server in Azure SQL Database must be correctly configured to support enclaves and attestation. For more information, see Set up the secure enclave and attestation.

  • You need to obtain an attestation URL from your environment from your attestation service administrator.

  • If you're connecting to your database using your application, it must use a client driver that supports Always Encrypted with secure enclaves. The application must connect to the database with Always Encrypted enabled for the database connection and the attestation protocol and the attestation URL properly configured. For detailed information, see Develop applications using Always Encrypted with secure enclaves.

  • If you're using SQL Server Management Studio (SSMS) or Azure SQL Data Studio, you need to enable Always Encrypted and configure the attestation protocol and the attestation URL when connecting to your database. See the following sections for details.

Note

Connecting to the database with Always Encrypted and attestation configured is not required for the following operations if you are using cached column encryption keys: DDL queries that create or alter indexes, DML queries that update indexes, and DBCC commands that check index integrity. For more information, see Invoke indexing operations using cached column encryption keys.

Prerequisites for running T-SQL statements using enclaves in SSMS

Minimum version requirements for SQL Server Management Studio:

  • SSMS 18.3 when using SQL Server.
  • SSMS 18.8 when using Azure SQL Database.

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

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

  2. Click Options >> and select the Always Encrypted tab.

  3. Select the Enable Always Encrypted (column encryption) checkbox and specify your enclave attestation URL. For example, https://hgs.bastion.local/Attestation or https://contososqlattestation.uks.attest.azure.net/attest/SgxEnclave.

    Connect to server with attestation using SSMS

  4. Select Connect.

  5. If you're prompted to enable Parameterization for Always Encrypted queries, select Enable if you plan to run parameterized DML queries. For more information, see Parameterization for Always Encrypted.

For additional 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, click Advanced....

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

  3. Specify the attestation protocol and the attestation URL.

    • If you're using SQL Server 2019 (15.x) 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 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.

    Connect to server with attestation using Azure Data Studio

  4. Click OK to close Advanced Properties.

For additional 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.

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.

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.

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.

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.

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 Azure SQL Database, but not in SQL Server 2019 (15.x).

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

Next Steps

See also