Configurar el cifrado de columnas directamente con Transact-SQL

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

En este artículo se describe cómo realizar operaciones criptográficas localmente en columnas con Always Encrypted con enclaves seguros y la instrucción ALTER TABLEStatement/ALTER COLUMN. Para obtener información básica sobre el cifrado in situ y los requisitos previos generales, consulte Configurar el cifrado de columnas in situ 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 cifra, vuelve a cifrar o descifra los datos almacenados en la columna, según la configuración de cifrado actual y de destino especificada en la definición de columna de la instrucción .

  • Si la columna no está cifrada actualmente, se cifra si especifica la ENCRYPTED WITH cláusula en la definición de columna.
  • Si la columna está cifrada actualmente, se descifra (se convierte en una columna de texto no cifrado), si no especifica la ENCRYPTED WITH cláusula en la definición de columna.
  • Si la columna está cifrada actualmente, se vuelve a cifrar si especifica la ENCRYPTED WITH cláusula y el tipo de cifrado de columna especificado o la clave de cifrado de columna son diferentes del tipo de cifrado usado actualmente o la clave de cifrado de columna.

Nota:

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

Al igual que con cualquier consulta que use un enclave seguro del lado servidor, debe enviar una ALTER TABLE/ALTER COLUMN instrucción que desencadene el cifrado local 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 Visual Studio Code o desde la aplicación.

Nota:

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

Realice el cifrado local con Transact-SQL en SSMS

Prerrequisitos

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, ejecute la ALTER TABLE/ALTER COLUMN instrucción . Especifique la configuración de cifrado de destino para una columna que desea cifrar, descifrar o volver a cifrar. Si va a cifrar o volver a cifrar la columna, use la cláusula ENCRYPTED WITH. Si la columna es una columna de cadena (por ejemplo, char, varchar, nchar, 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 acceden a la tabla, a fin de actualizar la información de cifrado de parámetros.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Nota:

    Si no quita el plan de la consulta afectada de la memoria caché, es posible que se produzca un error en la primera ejecución de la consulta después del cifrado.

    Use ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE o DBCC FREEPROCCACHE para borrar cuidadosamente la memoria caché del plan, ya que podría provocar una degradación temporal del rendimiento de las consultas. Para minimizar el impacto negativo de borrar la memoria caché, solo puede quitar los planes de las consultas afectadas de forma selectiva.

  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 se conservan en sys.parameters y que podrían invalidarse mediante el cifrado de las columnas.

Ejemplos

Cifrar una columna en el lugar

El siguiente ejemplo asume:

  • CEK1 es una clave de cifrado de columna habilitada para enclaves.
  • 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 en la propia columna mediante cifrado determinista y la clave de cifrado de columna habilitada para enclaves. También reemplaza el cotejamiento predeterminado de la base de datos por el cotejamiento BIN2 correspondiente (en la misma página de código).

La operación se realiza en línea (ONLINE = ON). Tome nota de la llamada a ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, que recrea los planes de las consultas afectadas por el cambio del 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;

Volver a cifrar una columna localmente para cambiar el tipo de cifrado

El siguiente ejemplo asume:

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

Volver a cifrar una columna in situ para rotar una clave de cifrado de columna

El siguiente ejemplo asume:

  • 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 compatible con 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;

Descifrar una columna en el mismo lugar

El siguiente ejemplo asume:

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

La siguiente instrucción descifra la columna y mantiene la intercalación sin cambios. También puede optar por cambiar el cotejamiento. Por ejemplo, cambie la ordenación por una ordenación distinta de 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;

Contenido relacionado

  • Last updated on