Tutorial: Creación y uso de índices en columnas habilitadas para enclave que usan cifrado aleatorio

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores: solo Windows Azure SQL Database

Este tutorial le enseña a crear y usar índices en columnas habilitadas para enclave que usan el cifrado aleatorio admitido en Always Encrypted con enclaves seguros. En él encontrará:

Requisitos previos

Descargue la última versión de SQL Server Management Studio (SSMS).

Asegúrese de que ha completado uno de los tutoriales Introducción a Always Encrypted con enclaves seguros en Azure SQL Database antes de seguir los pasos de este tutorial que se indican a continuación.

Paso 1: Habilitación de la recuperación de base de datos acelerada (ADR) en la base de datos

Microsoft recomienda encarecidamente habilitar ADR en la base de datos antes de crear el primer índice en una columna habilitada para enclave que usa cifrado aleatorio. Vea la sección Recuperación de la base de datos del tutorial Always Encrypted con enclaves seguros.

1. Cierre todas las instancias SSMS que usó en el tutorial anterior. Al cerrar SSMS se cerrarán las conexiones abiertas de base de datos, lo que es necesario para habilitar ADR.

2. Abra una nueva instancia de SSMS y conéctese a la instancia de SQL Server como sysadmin sin Always Encrypted habilitado para la conexión de base de datos.

   1. Inicie SSMS.
   2. En el cuadro de diálogo Conectar a servidor, especifique el nombre del servidor, seleccione un método de autenticación e indique sus credenciales.
   3. Seleccione Opciones >> y seleccione la pestaña Always Encrypted.
   4. Asegúrese de que la casilla Habilitar Always Encrypted (cifrado de columna)no esté activada.
   5. Seleccione Conectar.

3. Abra una nueva ventana de consulta y ejecute la siguiente instrucción para habilitar ADR.

   ALTER DATABASE ContosoHR SET ACCELERATED_DATABASE_RECOVERY = ON;
   

Paso 2: Creación y prueba de un índice sin separación de roles

En este paso, creará y probará un índice en una columna cifrada. Actuará como un usuario que asume los roles de un administrador de base de datos, que administra la base de datos, y de un propietario de datos, que tiene acceso a las claves que protegen los datos.

1. Abra una nueva instancia de SSMS y conéctese a la instancia de SQL Server con Always Encrypted habilitado para la conexión de base de datos.

   1. Inicie una nueva instancia de SSMS.
   2. En el cuadro de diálogo Conectar a servidor, especifique el nombre del servidor, seleccione un método de autenticación e indique sus credenciales.
   3. Seleccione Opciones >> y seleccione la pestaña Always Encrypted.
   4. Seleccione las casillas Habilitar Always Encrypted (cifrado de columna) y Habilitar enclaves seguros
   5. Si usa la atestación para la base de datos, seleccione un valor para el Protocolo de atestación de enclave que representa su servicio de atestación (Servicio de protección de host o Microsoft Azure Attestation) y rellene la dirección URL de atestación de enclave. De lo contrario, seleccione Ninguno.
   6. Seleccione Conectar.
   7. Si se le pide que habilite la parametrización para las consultas Always Encrypted, haga clic en Habilitar.

2. Si no se le solicita que habilite la parametrización de Always Encrypted, compruebe que está habilitada.

   1. Seleccione Herramientas en el menú principal de SSMS.
   2. Seleccione Opciones... .
   3. Vaya a Ejecución de consulta>SQL Server>Avanzadas.
   4. Asegúrese de que la opción Habilitar parametrización para Always Encrypted esté activada.
   5. Seleccione Aceptar.

3. Abra una ventana de consulta y ejecute las siguientes instrucciones para cifrar la columna LastName en la tabla Empleados. En pasos posteriores, deberá crear y utilizar un índice en esa columna.

   ALTER TABLE [HR].[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. Cree un índice en la columna LastName. Puesto que está conectado a la base de datos con Always Encrypted habilitado, el controlador cliente dentro de SSMS proporciona de modo transparente CEK1 (la clave de cifrado de columna que protege la columna LastName) al enclave, que es necesario para crear el índice.

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

5. Ejecute una consulta completa en la columna LastName y compruebe que SQL Server usa el índice al ejecutar la consulta.

   1. En la misma ventana de consulta o en otra nueva, asegúrese de que el botón Incluir estadísticas de consultas dinámicas de la barra de herramientas esté activado.

   2. Ejecute la siguiente consulta.

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

   3. En la pestaña Estadísticas de consultas dinámicas, observe que la consulta utiliza el índice.

Paso 3: Creación de un índice con separación de roles

En este paso, creará un índice en una columna cifrada, que simula ser dos usuarios distintos. Un usuario es un administrador de base de datos que necesita crear un índice, pero que no tiene acceso a las claves. El otro usuario es un propietario de datos, que tiene acceso a las claves.

1. Con la instancia de SSMS sin Always Encrypted habilitado, ejecute la siguiente instrucción para quitar el índice de la columna LastName.

   DROP INDEX IX_LastName ON [HR].[Employees]; 
   GO
   

2. Actúe como propietario de datos (o una aplicación que tenga acceso a las claves) y llene la caché dentro del enclave con CEK1.

   Nota:

   A menos que haya reiniciado la instancia de SQL Server después del Paso 2: Crear y probar un índice sin separación de roles, este paso es redundante, ya que el CEK1 ya está presente en la memoria caché. Lo hemos agregado para demostrar cómo un propietario de datos puede proporcionar una clave al enclave, si aún no está presente en él.

   1. En la instancia de SSMS con Always Encrypted habilitado, ejecute las siguientes instrucciones en una ventana de consulta. La instrucción envía todas las claves de cifrado de columna habilitadas para enclave al enclave. Consulte sp_enclave_send_keys para obtener más información.

      EXEC sp_enclave_send_keys;
      GO
      

   2. Como alternativa a ejecutar el procedimiento almacenado anterior, puede ejecutar una consulta DML que usa el enclave en la columna LastName. Esta acción rellenará el enclave solo con CEK1.

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

3. Actúe como un administrador de base de datos y cree el índice.

   1. En la instancia de SSMS sin Always Encrypted habilitado, ejecute las siguientes instrucciones en una ventana de consulta.

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

4. Como propietario de datos, ejecute una consulta completa en la columna LastName y compruebe que SQL Server usa el índice al ejecutarla.

   1. En la instancia de SSMS con Always Encrypted habilitado, seleccione una ventana de consulta existente o abra una nueva ventana de consulta y asegúrese de que el botón Incluir estadísticas de consultas dinámicas de la barra de herramientas esté activado.

   2. Ejecute la siguiente consulta.

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

   3. En Estadísticas de consultas dinámicas, observe que la consulta utiliza el índice.

Pasos siguientes

• Tutorial: Desarrollo de una aplicación de .NET mediante Always Encrypted con enclaves seguros
• Tutorial: Desarrollo de una aplicación de .NET Framework mediante Always Encrypted con enclaves seguros

Consulte también

• Creación y uso de índices en columnas mediante Always Encrypted con enclaves seguros