Skip to content

tutorial-creating-using-indexes-on-enclave-enabled-columns-using-randomized-encryption.md

Latest commit

 

History

History
139 lines (121 loc) · 8.29 KB

tutorial-creating-using-indexes-on-enclave-enabled-columns-using-randomized-encryption.md

File metadata and controls

139 lines (121 loc) · 8.29 KB
title Tutorial: Creating and using indexes on enclave-enabled columns using randomized encryption | Microsoft Docs
ms.custom
ms.date 06/20/2019
ms.prod sql
ms.prod_service database-engine, sql-database
ms.reviewer vanto
ms.suite sql
ms.technology security
ms.tgt_pltfrm
ms.topic tutorial
author jaszymas
ms.author jaszymas
manager craigg
monikerRange >= sql-server-ver15 || = sqlallproducts-allversions

Tutorial: Creating and using indexes on enclave-enabled columns using randomized encryption

[!INCLUDE tsql-appliesto-ssver15-xxxx-xxxx-xxx]

This tutorial teaches you how to create and use indexes on enclave-enabled columns using randomized encryption supported in Always Encrypted with secure enclaves. It will show you:

  • How to create an index when you have access to the keys (the column master key and the column encryption key) protecting the column.
  • How to create an index when you do not have access to the keys protecting the column.

Prerequisites

This tutorial is the continuation of Tutorial: Getting Started with Always Encrypted with secure enclaves using SSMS. Make sure you have completed it, before following the below steps.

Step 1: Enable Accelerated Database Recovery (ADR) in your database

Microsoft strongly recommends you enable ADR in your database before creating the first index on an enclave-enabled column using randomized encryption. See the Database Recovery section in Always Encrypted with Secure Enclaves.

  1. Close any SSMS instances, you used in the previous tutorial. This will close database connections you have opened, which is required to enable ADR.
  2. Open a new instance of SSMS and connect to your SQL Server instance as sysadmin without Always Encrypted enabled for the database connection.
    1. Start SSMS.
    2. In the Connect to Server dialog, specify your server name, select an authentication method and specify your credentials.
    3. Click Options >> and select the Always Encrypted tab.
    4. Make sure the Enable Always Encrypted (column encryption) checkbox is not selected.
    5. Select Connect.
  3. Open a new query window and execute the below statement to enable ADR. 
    ALTER DATABASE ContosoHR SET ACCELERATED_DATABASE_RECOVERY = ON;

Step 2: Create and test an index without role separation

In this step, you will create and test an index on an encrypted column. You will be acting as a single user who is assuming the roles of both a DBA, who manages the database, and the data owner who has access to the keys, protecting the data.

  1. Open a new SSMS instance and connect to your SQL Server instance with Always Encrypted enabled for the database connection.
    1. Start a new instance of SSMS.
    2. In the Connect to Server dialog, specify your server name, select an authentication method and specify your credentials.
    3. Click Options >> and select the Always Encrypted tab.
    4. Select the Enable Always Encrypted (column encryption) checkbox and specify your enclave attestation URL (for example, http://hgs.bastion.local/Attestation).
    5. Select Connect.
    6. If prompted to enable parameterization for Always Encrypted queries, click Enable.
  2. If you were not prompted to enable Parameterization for Always Encrypted, verify it is enabled.
    1. Select Tools from the main menu of SSMS.
    2. Select Options....
    3. Navigate to Query Execution > SQL Server > Advanced.
    4. Ensure that Enable Parameterization for Always Encrypted is checked.
    5. Select OK.
  3. Open a query window and execute the below statements to encrypt the LastName column in the Employees table. You will create and use an index on that column in later steps. 
    USE [ContosoHR];
GO

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [LastName] [nvarchar](50) COLLATE Latin1_General_BIN2 
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
  4. Create an index on the LastName column. Since you are connected to the database with Always Encrypted enabled, the client driver inside SSMS transparently provides CEK1 (the column encryption key, protecting the LastName column) to the enclave, which is needed to created the index. 
    USE [ContosoHR];
GO

CREATE INDEX IX_LastName ON [Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO;
  5. Run a rich query on the LastName column and verify SQL Server uses the index when executing the query.
    1. In the same or a new query window, make sure the Include Live Query Statistics button on the toolbar is on .
    2. Execute the below query. 
      USE [ContosoHR];
GO

DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [dbo].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO
    3. In the Live Query Statistics tab (in the bottom part of the query window), observe that the query uses the index.

Step 3: Create an index with role separation

In this step, you will create an index on an encrypted column, pretending to be two different users. One user is a DBA, who needs to create an index, but does not have an access to the keys. The other user is a data owner, who has access to the keys.

  1. Using the SSMS instance without Always Encrypted enabled, execute the below statement to drop the index on the LastName column.

    USE [ContosoHR];
GO

DROP INDEX IX_LastName ON [Employees]; 
GO

  2. Acting as a data owner (or an application that has access to the keys), populate the cache inside the enclave with CEK1.

    [!NOTE] Unless you have restarted your SQL Server instance after Step 2: Create and test an index without role separation, this step is redundant as the CEK1 is already present in the cache. We have added it to demonstrate how a data owner can provide a key to the enclave, if it is not already present in the enclave.

    1. In the SSMS instance with Always Encrypted enabled, execute the below statements in a query window. The statement sends all enclave-enabled column encryption keys to the enclave. See sp_enclave_send_keys for details. 
      USE [ContosoHR];
GO

EXEC sp_enclave_send_keys;
GO
    2. As an alternative to executing the above stored procedure, you can run a DML query that uses the enclave against the LastName column. This will populate the enclave only with CEK1. 
      USE [ContosoHR];
GO

DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [dbo].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

  3. Acting as a DBA, create the index.

    1. In the SSMS instance without Always Encrypted enabled, execute the below statements in a query window. 
      USE [ContosoHR];
GO

CREATE INDEX IX_LastName ON [Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO;

  4. As a data owner, run a rich query on the LastName column and verify SQL Server uses the index when executing the query.

    1. In the SSMS instance with Always Encrypted enabled, select an existing query window or open a new query window, and make sure the Include Live Query Statistics button on the toolbar is on.
    2. Execute the below query. 
      USE [ContosoHR];
GO

DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [dbo].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO
    3. In the Live Query Statistics (in the bottom part of the query window), observe that the query uses the index.

Next Steps

See Configure Always Encrypted with secure enclaves for information on other use cases for Always Encrypted with secure enclaves.