Skip to content

always-encrypted-enclaves-query-columns.md

Latest commit

 

History

History
220 lines (158 loc) · 13 KB

always-encrypted-enclaves-query-columns.md

File metadata and controls

220 lines (158 loc) · 13 KB
title Run Transact-SQL Statements Using Secure Enclaves
description Run Data Definition Language (DDL) statements to configure encryption for your column or manage indexes on encrypted columns, and query encrypted columns
author Pietervanhove
ms.author pivanho
ms.reviewer vanto, randolphwest
ms.date 01/28/2026
ms.service sql
ms.subservice security
ms.topic how-to
ms.custom
ignite-2023
sfi-image-nochange

Run Transact-SQL statements using secure enclaves

[!INCLUDE sqlserver2019-windows-only-asdb]

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 isn't supported.

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

In [!INCLUDE ssazure-sqldb] and in [!INCLUDE sql-server-2022], 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 [!INCLUDE sql-server-2019], a_BIN2 collation is required.

DBCC commands using secure enclaves

DBCC 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 and DBCC CHECKTABLE.

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 [!INCLUDE ssnoversion-md] instance or your database server in [!INCLUDE ssazure-sqldb] 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
      [!INCLUDE sql-server-2019] and later VBS enclaves Host Guardian Service, None
      [!INCLUDE ssazure-sqldb] SGX enclaves (in DC-series databases) Microsoft Azure Attestation
      [!INCLUDE ssazure-sqldb] 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

Install 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 [!INCLUDE ssnoversion-md].
    2. Microsoft Azure Attestation if you're using [!INCLUDE ssazure-sqldb] with Intel SGX enclaves.
    3. None if you're using [!INCLUDE ssazure-sqldb] 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.

    :::image type="content" source="media/always-encrypted-enclaves/ssms-connect-microsoft-azure-attestation.png" alt-text="Screenshot of 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.

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)
);

Exact match search

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

DECLARE @SSN AS CHAR (11) = '795-73-9838';

SELECT *
FROM [HR].[Employees]
WHERE [SSN] = @SSN;

Pattern matching search

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 AS CHAR (11) = '795-73-9838';

SELECT *
FROM [HR].[Employees]
WHERE [SSN] = @SSN;

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 AS MONEY = 40000;
DECLARE @MaxSalary AS MONEY = 45000;

SELECT *
FROM [HR].[Employees]
WHERE [Salary] > @MinSalary
      AND [Salary] < @MaxSalary;

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] AS e
     INNER JOIN [HR].[Jobs] AS j
         ON e.[JobID] = j.[JobID]
        AND e.[Salary] > j.[MaxSalary]
        OR e.[Salary] < j.[MinSalary];

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 [!INCLUDE sssql22-md] and Azure SQL Database, but not in [!INCLUDE sql-server-2019].

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

Next step

[!div class="nextstepaction"] Develop applications using Always Encrypted with secure enclaves

Related content