Tutorial: Introducción a Always Encrypted

Artículo
05/23/2023

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance

En este tutorial se explica cómo empezar a trabajar con Always Encrypted. En él encontrará:

Cómo cifrar las columnas seleccionadas en la base de datos.
Consulta de columnas cifradas.

Nota

Si busca información sobre Always Encrypted con enclaves seguros, consulte en su lugar los siguientes tutoriales:

Prerrequisitos

En este tutorial, necesitará:

Una base de datos vacía en Azure SQL Database, Azure SQL Managed Instance o SQL Server. En las instrucciones siguientes se supone que el nombre de la base de datos es ContosoHR. Debe ser propietario de la base de datos (miembro del rol db_owner ). Para obtener información sobre cómo crear una base de datos, consulte Inicio rápido: Creación de una base de datos única: Azure SQL Database o Creación de una base de datos en SQL Server.
Opcional, pero recomendado, especialmente si la base de datos está en Azure: un almacén de claves en Azure Key Vault. Para información sobre cómo crear un almacén de claves, consulte Inicio rápido: Creación de un almacén de claves mediante Azure Portal.
- Si el almacén de claves usa el modelo de permisos de directiva de acceso, asegúrese de que tiene los siguientes permisos de clave en el almacén de claves: get, list, create, unwrap key, wrap key, verify y sign. Consulte Asignación de una directiva de acceso de Key Vault.
- Si usa el modelo de permisos de control de acceso basado en rol (RBAC) de Azure, asegúrese de que es miembro del rol Agente criptográfico de Key Vault en el almacén de claves. Consulte Acceso a las claves, los certificados y los secretos de Key Vault con un control de acceso basado en rol de Azure.
La versión más reciente de SQL Server Management Studio (SSMS) o la versión más reciente de los módulos sqlServer y Az de PowerShell. El módulo Az de PowerShell solo es necesario si usa Azure Key Vault.

Paso 1: Crear y rellenar el esquema de la base de datos

En este paso, creará el esquema de RR. HH . y la tabla Employees . A continuación, rellenará la tabla con algunos datos.

SSMS
PowerShell

Conéctese a la base de datos. Para obtener instrucciones sobre cómo conectarse a una base de datos desde SSMS, consulte Inicio rápido: Conexión y consulta de una base de datos de Azure SQL o una Azure SQL Managed Instance mediante SQL Server Management Studio (SSMS) o Inicio rápido: Conexión y consulta de una instancia de SQL Server mediante SQL Server Management Studio (SSMS).
Abra una nueva ventana de consulta para la base de datos ContosoHR .

Pegue y ejecute las instrucciones siguientes para crear una nueva tabla, denominada Employees.

CREATE SCHEMA [HR];
GO

CREATE TABLE [HR].[Employees]
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL
    , [SSN] [char](11) NOT NULL
    , [FirstName] [nvarchar](50) NOT NULL
    , [LastName] [nvarchar](50) NOT NULL
    , [Salary] [money] NOT NULL
) ON [PRIMARY];

Pegue y ejecute las instrucciones siguientes para agregar algunos registros de empleados a la tabla Employees .

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '795-73-9838'
    , N'Catherine'
    , N'Abel'
    , $31692
);

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '990-00-6818'
    , N'Kim'
    , N'Abercrombie'
    , $55415
);

En una sesión de PowerShell, ejecute los siguientes comandos. Asegúrese de actualizar la cadena de conexión con la dirección del servidor y la configuración de autenticación que son válidas para la base de datos.

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Server = myServerAddress; Database = ContosoHR; ..."

# Create a new table, named Employees.
$query = @'
    CREATE SCHEMA [HR];
    GO
    
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL
        , [SSN] [char](11) NOT NULL
        , [FirstName] [nvarchar](50) NOT NULL
        , [LastName] [nvarchar](50) NOT NULL
        , [Salary] [money] NOT NULL
    ) ON [PRIMARY];
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Paso 2: Cifrar columnas

En este paso, aprovisionará una clave maestra de columna y una clave de cifrado de columna para Always Encrypted. A continuación, cifrará las columnas SSN y Salario en la tabla Empleados .

SSMS
PowerShell

SSMS proporciona un asistente que le ayuda a configurar fácilmente Always Encrypted mediante la configuración de una clave maestra de columna, una clave de cifrado de columnas y el cifrado de columnas seleccionadas.

En Explorador de objetos, expanda Bases de datos>ContosoHR>Tablas.
Haga clic con el botón derecho en la tabla Empleados y seleccione Cifrar columnas para abrir el asistente para Always Encrypted.
Seleccione Siguiente en la página Introducción del asistente.
En la página Selección de columnas .
1. Seleccione las columnas SSN y Salary . Elija el cifrado determinista para la columna SSN y el cifrado aleatorio para la columna Salario . El cifrado determinista admite consultas, como búsquedas de búsqueda de puntos que implican comparaciones de igualdad en columnas cifradas. El cifrado aleatorio no admite cálculos en columnas cifradas.
2. Deje CEK-Auto1 (Nuevo) como clave de cifrado de columna para ambas columnas. Esta clave aún no existe y la generará el asistente.
3. Seleccione Next (Siguiente).
En la página Configuración de clave maestra, configure una nueva clave maestra de columna generada por el asistente. En primer lugar, debe seleccionar dónde desea almacenar la clave maestra de columna. El asistente admite dos tipos de almacén de claves:
- Azure Key Vault: se recomienda si la base de datos está en Azure
- Almacén de certificados de Windows
En general, Azure Key Vault es la opción recomendada, especialmente si la base de datos está en Azure.
- Para usar Azure Key Vault:
  1. Seleccione Azure Key Vault.
  2. Seleccione Iniciar sesión y completar el inicio de sesión en Azure.
  3. Una vez que haya iniciado sesión, la página mostrará la lista de suscripciones y almacenes de claves a los que tiene acceso. Seleccione una suscripción de Azure que contenga el almacén de claves que quiera usar.
  4. Seleccione el almacén de claves.
  5. Seleccione Next (Siguiente).
- Para usar el almacén de certificados de Windows:
  1. Seleccione Almacén de certificados de Windows.
  2. Deje la selección predeterminada del usuario actual : esto indicará al asistente que genere un certificado (la nueva clave maestra de columna) en el almacén usuario actual .
  3. Seleccione Next (Siguiente).
En la página Configuración de cifrado local, no se requiere ninguna configuración adicional porque la base de datos no tiene habilitado un enclave. Seleccione Next (Siguiente).
En la página Configuración de ejecución, se le preguntará si desea continuar con el cifrado o generar un script de PowerShell para que se ejecute más adelante. Deje la configuración predeterminada y seleccione Siguiente.
En la página Resumen , el asistente le informa sobre las acciones que se ejecutarán. Compruebe que toda la información es correcta y seleccione Finalizar.
En la página Resultados , puede supervisar el progreso de las operaciones del asistente. Espere hasta que todas las operaciones se completen correctamente y seleccione Cerrar.
(Opcional) Explore los cambios realizados por el asistente en la base de datos.
1. ExpandaSeguridad>de ContosoHR>Always Encrypted Claves para explorar los objetos de metadatos de la clave maestra de columna y el cifrado de columnas que creó el asistente.
2. También puede ejecutar las consultas siguientes en las vistas de catálogo del sistema que contienen metadatos clave.
```
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
```
3. En Explorador de objetos, haga clic con el botón derecho en la tabla Employees (Empleados) y seleccione Script Table (Crear tabla) comoCREATE ToNew Editor de Power Query Window (Crear a >> nueva ventana de Editor de Power Query). Se abrirá una nueva ventana de consulta con la instrucción CREATE TABLE para la tabla Employees . Observe la cláusula ENCRYPTED WITH que aparece en las definiciones de las columnas SSN y Salary .
4. También puede ejecutar la consulta siguiente en sys.columns para recuperar metadatos de cifrado de nivel de columna para las dos columnas cifradas.
```
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
```

Cree una clave maestra de columna en el almacén de claves.

Si usa Azure Key Vault, ejecute los siguientes comandos para crear una clave asimétrica en el almacén de claves. Asegúrese de proporcionar el identificador correcto de la suscripción, el nombre del grupo de recursos que contiene el almacén de claves y el nombre del almacén de claves.

Import-Module "Az"
Connect-AzAccount
$subscriptionId = "<your Azure subscription ID"
$resourceGroup = "your resource group name containing your key vault"
$keyVaultName = "your vault name"
$keyVaultKeyName = "your key name"

# Switch to your subscription.
Set-AzConteXt -SubscriptionId $subscriptionId

# To validate the above key vault settings, get the key vault properties.
Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 

# Create a key in the key vault.
$keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
$keyVaultKey

Si usa el almacén de certificados de Windows, ejecute los siguientes comandos para crear un certificado en el almacén usuario actual.

$cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

Conéctese a la base de datos mediante el módulo sqlServer de PowerShell. Asegúrese de proporcionar una cadena de conexión válida para la base de datos.
```
$database = Get-SqlDatabase -ConnectionString $connectionString
$database
```

Aprovisione un objeto de metadatos de clave maestra de columna (que haga referencia a la clave maestra de columna física que ha creado en el almacén de claves) en la base de datos.

Si usa Azure Key Vault, ejecute los siguientes comandos.

# Sign in to Azure for the SqlServer PowerShell module
Add-SqlAzureAuthenticationContext -Interactive

# Create a SqlColumnMasterKeySettings in-memory object referencing the key you've created in your key vault. 
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid

# Create column master key metadata object (referencing your certificate), named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

Si usa el almacén de certificados de Windows, ejecute los siguientes comandos.

# Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint

# Create column master key metadata object, named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

Genere una clave de cifrado de columna, cifre con la clave maestra de columna que ha creado y cree un objeto de metadatos de clave de cifrado de columnas en la base de datos.
```
$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
```

Cifre las columnas SSN y Salario en la tabla Employees . Elija el cifrado determinista para la columna SSN y el cifrado aleatorio para la columna Salario . El cifrado determinista admite consultas, como búsquedas de búsqueda de puntos que implican comparaciones de igualdad en columnas cifradas. El cifrado aleatorio no admite cálculos en columnas cifradas.

# Encrypt the SSN and Salary columns 
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

(Opcional) Explore los cambios realizados en la base de datos.

Ejecute los comandos siguientes para consultar las vistas de catálogo del sistema que contienen metadatos sobre la clave maestra de columna y la clave de cifrado de columnas que ha creado.

$query = @'
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Ejecute los siguientes comandos para consultar sys.columns para recuperar metadatos de cifrado de nivel de columna para las dos columnas cifradas.

$query = @'
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Conéctese a la base de datos con Always Encrypted deshabilitado para la conexión.
1. Abra una nueva ventana de consulta.
2. Haga clic con el botón derecho en cualquier parte de la ventana de consulta y seleccione ConnectionChange Connection (Cambiar conexión).> Se abrirá el cuadro de diálogo Conectar al motor de base de datos .
3. Seleccione Opciones <<. Esto mostrará pestañas adicionales en el cuadro de diálogo Conectar al motor de base de datos .
4. Seleccione la pestaña Always Encrypted.
5. Asegúrese de que la opción Habilitar Always Encrypted (cifrado de columnas) no está seleccionada.
6. Seleccione Conectar.
Pegue y ejecute la siguiente consulta. La consulta debe devolver datos cifrados binarios.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```
Conéctese a la base de datos con Always Encrypted habilitada para la conexión.
1. Haga clic con el botón derecho en cualquier parte de la ventana de consulta y seleccione ConnectionChange Connection (Cambiar conexión).> Se abrirá el cuadro de diálogo Conectar al motor de base de datos .
2. Seleccione Opciones <<. Esto mostrará pestañas adicionales en el cuadro de diálogo Conectar al motor de base de datos .
3. Seleccione la pestaña Always Encrypted.
4. Seleccione Habilitar Always Encrypted (cifrado de columnas).
5. Seleccione Conectar.
Vuelva a ejecutar la misma consulta. Dado que está conectado con Always Encrypted habilitado para la conexión de base de datos, el controlador cliente de SSMS intentará descifrar los datos almacenados en ambas columnas cifradas. Si usa Azure Key Vault, es posible que se le pida que inicie sesión en Azure.
Habilite la parametrización para Always Encrypted. Esta característica permite ejecutar consultas que filtran los datos por columnas cifradas (o insertan datos en columnas cifradas).
1. Seleccione Consulta en el menú principal de SSMS.
2. Seleccione Opciones de consulta… .
3. Vaya a Ejecución>Avanzadas.
4. Asegúrese de que la opción Habilitar parametrización para Always Encrypted esté activada.
5. Seleccione Aceptar.
Pegue y ejecute la consulta siguiente, que filtra los datos por la columna de SSN cifrada. La consulta debe devolver una fila que contenga valores de texto no cifrado.
```
DECLARE @SSN [char](11) = '795-73-9838'
SELECT [SSN], [Salary] FROM [HR].[Employees]
WHERE [SSN] = @SSN
```
Opcionalmente, si usa Azure Key Vault configurado con el modelo de permisos de directiva de acceso, siga estos pasos para ver lo que sucede cuando un usuario intenta recuperar datos de texto no cifrado de columnas cifradas sin tener acceso a la clave maestra de columna que protege los datos.
1. Quite el permiso de clave unwrap para usted mismo en la directiva de acceso del almacén de claves. Para más información, consulte Asignación de una directiva de acceso de Key Vault.
2. Dado que el controlador de cliente de SSMS almacena en caché las claves de cifrado de columna adquiridas desde un almacén de claves durante 2 horas, cierre SSMS y vuelva a abrirla. Esto garantizará que la caché de claves esté vacía.
3. Conéctese a la base de datos con Always Encrypted habilitada para la conexión.
4. Pegue y ejecute la siguiente consulta. Se debe producir un error en la consulta con el mensaje de error que indica que falta el permiso necesario unwrap .
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```

Conéctese a la base de datos con Always Encrypted deshabilitado y ejecute una consulta para leer datos de columnas cifradas. La consulta debe devolver datos cifrados como matrices binarias.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString $connectionString -Query $query
```
Conéctese a la base de datos con Always Encrypted habilitado y ejecute una consulta para leer datos de columnas cifradas. Dado que tiene acceso a la clave maestra de columna que protege las columnas cifradas, la consulta debe devolver datos de texto no cifrado.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
```

Nota

Invoke-SqlCmd no admite consultas que pueden filtrar por o insertar datos en columnas cifradas. Estas consultas deben parametrizarse y Invoke-SqlCmd no admite consultas parametrizadas.

Pasos siguientes

Desarrollo de aplicaciones con Always Encrypted