Compartir por

Configuración del cifrado de columnas en contexto con Transact-SQL

  • Artigo

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

En este artículo se describe cómo realizar operaciones criptográficas en contexto en columnas mediante Always Encrypted con enclaves seguros con la instrucción ALTER TABLE/ALTER COLUMN. Para obtener información básica sobre el cifrado en contexto y los requisitos previos generales, consulte Configuración del cifrado de columna en contexto mediante Always Encrypted con enclaves seguros.

Con la instrucción ALTER TABLE o ALTER COLUMN, puede establecer la configuración de cifrado de destino de una columna. Al ejecutar la instrucción, el enclave seguro del lado servidor cifrará, volverá a cifrar o descifrará los datos almacenados en la columna, en función de la configuración de cifrado actual y de destino que se ha especificado en la definición de columna de la instrucción.

  • Si la columna no está cifrada actualmente, se cifrará si especifica la cláusula ENCRYPTED WITH en la definición de columna.
  • Si la columna está cifrada actualmente, se descifrará (es decir, se convertirá en una columna de texto no cifrado) si no se especifica la cláusula ENCRYPTED WITH en la definición de columna.
  • Si la columna está cifrada actualmente, se volverá a cifrar si se especifica la cláusula ENCRYPTED WITH y el tipo de cifrado de columna indicado o la clave de cifrado de columna son diferentes del tipo de cifrado usado actualmente o de la clave de cifrado de columna.

Nota:

No se pueden combinar operaciones criptográficas con otros cambios en una sola instrucción ALTER TABLE/ALTER COLUMN, salvo al cambiar la columna a NULL o NOT NULL, o bien al cambiar una intercalación. Por ejemplo, no puede cifrar una columna y cambiar un tipo de datos de la columna en una sola instrucción ALTER TABLE/ALTER COLUMN de Transact-SQL. Use dos instrucciones independientes.

Como todas las consultas que usan un enclave seguro del lado servidor, se debe enviar una instrucción ALTER TABLE/ALTER COLUMN que desencadene el cifrado en contexto a través de una conexión con Always Encrypted y los cálculos de enclave habilitados.

En el resto de este artículo se describe cómo desencadenar el cifrado en contexto mediante la instrucción ALTER TABLE/ALTER COLUMN de SQL Server Management Studio. Como alternativa, puede emitir ALTER TABLE/ALTER COLUMN desde Azure Data Studio o la aplicación.

Nota:

Actualmente, el cmdlet Invoke-Sqlcmd del módulo SqlServer de PowerShell y sqlcmd, no admiten el uso de ALTER TABLE/ALTER COLUMN para operaciones criptográficas en contexto.

Realización del cifrado en contexto con Transact-SQL en SSMS

Requisitos previos

Pasos

  1. Abra una ventana de consulta con Always Encrypted y los cálculos de enclave habilitados en la conexión de base de datos. Para obtener más información, vea Habilitación y deshabilitación de Always Encrypted para una conexión de base de datos.

  2. En la ventana de consulta, emita la instrucción ALTER TABLE/ALTER COLUMN, y especifique la configuración de cifrado de destino de una columna que quiera cifrar, descifrar o volver a cifrar. Si va a cifrar o volver a cifrar la columna, use la cláusula ENCRYPTED WITH. Si su columna es una columna de cadena (por ejemplo, char, varchar, nchar o nvarchar), es posible que también tenga que cambiar la intercalación a una intercalación BIN2.

    Nota:

    Si su clave maestra de columna se almacena en Azure Key Vault, es posible que se le pida que inicie sesión en Azure.

  3. Borre la caché de planes de todos los lotes y procedimientos almacenados que tienen acceso a la tabla para actualizar la información de cifrado de los parámetros.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Nota:

    Si no quita el plan para la consulta afectada de la memoria caché, la primera ejecución de la consulta tras el cifrado puede producir un error.

    Use ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHEDBCC FREEPROCCACHE para borrar la caché de planes con cuidado, ya que puede dar lugar a una degradación temporal del rendimiento de las consultas. Para minimizar el impacto negativo de haber borrado la memoria caché, únicamente puede quitar de forma selectiva los planes para las consultas afectadas.

  4. Llame a sp_refresh_parameter_encryption para actualizar los metadatos de los parámetros de cada módulo (procedimiento almacenado, función, vista, desencadenador) que persisten en sys.parameters y que pueden haberse invalidado al cifrar las columnas.

Ejemplos

Para cifrar una columna en contexto

En el ejemplo siguiente se da por sentado que:

  • CEK1 es una clave de cifrado de columna habilitada para el enclave.
  • La columna SSN es texto no cifrado y actualmente usa la intercalación de base de datos predeterminada, por ejemplo, una intercalación Latin1 distinta de BIN2 (como Latin1_General_CI_AI_KS_WS).

La instrucción cifra la columna SSN mediante cifrado determinista y la clave de cifrado de columna habilitada para enclave local. También sobrescribe la intercalación de base de datos predeterminada con la intercalación BIN2 correspondiente (en la misma página de código).

La operación se realiza en línea (ONLINE = ON). Tenga también en cuenta la llamada a ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, que vuelve a crear los planes de las consultas afectadas por el cambio en el esquema de la tabla.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Para volver a cifrar una columna en contexto a fin de cambiar el tipo de cifrado

En el ejemplo siguiente se da por sentado que:

  • La columna SSN está cifrada mediante cifrado determinista y una clave de cifrado de columna habilitada para el enclave, CEK1.
  • La intercalación actual, establecida en el nivel de columna, es Latin1_General_BIN2.

La siguiente instrucción vuelve a cifrar la columna mediante el cifrado aleatorio y la misma clave (CEK1).

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) 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

Para volver a cifrar una columna en contexto a fin de girar una clave de cifrado de columna

En el ejemplo siguiente se da por sentado que:

  • La columna SSN está cifrada mediante cifrado aleatorio y una clave de cifrado de columna habilitada para el enclave, CEK1.
  • CEK2 es una clave de cifrado de columna habilitada para el enclave (diferente de CEK1).
  • La intercalación actual, establecida en el nivel de columna, es Latin1_General_BIN2.

La siguiente instrucción vuelve a cifrar la columna con CEK2.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK2]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Descifrado de una columna en contexto

En el ejemplo siguiente se da por sentado que:

  • La columna SSN está cifrada mediante una clave de cifrado de columna habilitada para el enclave.
  • La intercalación actual, establecida en el nivel de columna, es Latin1_General_BIN2.

La instrucción siguiente descifra la columna y no modifica la intercalación. Como alternativa, puede optar por cambiar la intercalación. Por ejemplo, cambie la intercalación por una que no sea BIN2 en la misma instrucción.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
WITH (ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Pasos siguientes

Consulte también