Skip to content

Latest commit

 

History

History
228 lines (163 loc) · 14.9 KB

always-encrypted-enclaves-query-columns.md

File metadata and controls

228 lines (163 loc) · 14.9 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.custom ms.topic
Run Transact-SQL statements using secure enclaves
Run Data Definition Language (DDL) statements to configure encryption for your column or manage indexes on encrypted columns, and query encrypted columns
Pietervanhove
pivanho
vanto
11/14/2023
sql
security
ignite-2023
how-to

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 is not supported.

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

In [!INCLUDE ssazure-sqldb] and in [!INCLUDEsql-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 [!INCLUDEsql-server-2019], 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 [!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
      [!INCLUDEsql-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

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 [!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.

    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 [!INCLUDEssNoVersion] 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 [!INCLUDE ssazure-sqldb], 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 [!INCLUDE ssazure-sqldb], 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.

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

Exact match search

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

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

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

Next steps

See also