Tutorial: Introducción a Always Encrypted

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.
• Cómo consultar columnas cifradas.

Nota:

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

• Introducción al uso de Always Encrypted con enclaves seguros
• Tutorial: Introducción al uso de Always Encrypted con enclaves seguros en SQL Server

Requisitos previos

En este tutorial, necesitará:

• Una base de datos vacía en SQL Server, Azure SQL Database o 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: Creación y rellenado del esquema de la base de datos

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

SSMS

1. 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 a una instancia de Azure SQL Database o Azure SQL Managed Instance y realización de consultas con 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).

2. Abra una nueva ventana de consulta para la base de datos ContosoHR.

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

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

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

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

PowerShell

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 sean 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: Cifrado de columnas

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

SSMS

SSMS proporciona un asistente para ayudar a configurar Always Encrypted fácilmente, que configura automáticamente la clave maestra de columna, la clave de cifrado de columna y cifra las columnas seleccionadas.

1. En el Explorador de objetos, expanda Bases de datos>ContosoHR>Tablas.

2. Haga clic con el botón derecho en la tabla Empleados y seleccione Cifrar columnas para abrir el asistente de Always Encrypted.

   

3. En la página Introducción del asistente, seleccione Siguiente.

4. En la página de selección de columnas.

   1. Seleccione las columnas SSN y Salario. 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 Siguiente.

   

5. En la página Configuración de clave maestra, configure una nueva clave maestra de columna que el asistente generará. 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 la suscripción de Azure que contiene el almacén de claves que quiera usar.
     4. Selecciona el almacén de claves.
     5. Seleccione 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 le indicará al asistente que genere un certificado (la nueva clave maestra de columna) en el almacén del usuario actual.

        

     3. Seleccione Siguiente.

6. 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 Siguiente.

7. En la página Configuración de ejecución, se le preguntará si desea continuar con el cifrado o generar un script de PowerShell que se ejecutará más adelante. Deje la configuración predeterminada y seleccione Siguiente.

8. 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.

9. 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.

   

10. (Opcional) Explore los cambios realizados por el asistente en la base de datos.

    1. Expanda ContosoHR>Seguridad>Claves Always Encrypted 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 siguientes consultas en las vistas de catálogo del sistema que contienen metadatos de clave.

       SELECT * FROM sys.column_master_keys;
       SELECT * FROM sys.column_encryption_keys
       SELECT * FROM sys.column_encryption_key_values
       

    3. En el Explorador de objetos, haga clic con el botón derecho en la tabla Empleados y seleccione Incluir tabla como>CREATE TO>Nueva ventana del editor de consultas. Se abrirá una nueva ventana de consulta con la instrucción CREATE TABLE para la tabla Empleados. Observe la cláusula ENCRYPTED WITH que aparece en las definiciones de las columnas SSN y Salario.

    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;
       

PowerShell

1. 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
     

2. 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
   

3. 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) de 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
     

4. Genere una clave de cifrado de columna, cífrela con la clave maestra de columna que ha creado y cree un objeto de metadatos de clave de cifrado de columna en la base de datos.

   $cekName = "CEK1"
   New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
   

5. Cifre las columnas SSN y Salario en la tabla Empleados. 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 .
   
   

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

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

     $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
     

Paso 3: Consulta de columnas cifradas

SSMS

1. 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 la ventana de consulta y seleccione Conexión>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 casilla Habilitar Always Encrypted (cifrado de columna) no esté seleccionada.
   6. Seleccione Conectar.

   

2. Pegue y ejecute la siguiente consulta. La consulta debe devolver datos cifrados binarios.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

   

3. Conéctese a la base de datos con Always Encrypted habilitado para la conexión.

   1. Haga clic con el botón derecho en la ventana de consulta y seleccione Conexión>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 columna).
   5. Seleccione Conectar.

   

4. 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.

   

5. 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 Habilitar parametrización de Always Encrypted esté habilitada.
   5. Seleccione Aceptar.

   

6. 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
   

7. Opcionalmente, si usa Azure Key Vault configurado con el modelo de permisos de directiva de acceso, siga estos pasos para ver qué 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. Quítese el permiso de clave unwrap 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 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 abrirlo. Esto garantizará que la caché de claves esté vacía.
   3. Conéctese a la base de datos con Always Encrypted habilitado para la conexión.
   4. Pegue y ejecute la siguiente consulta. La consulta debe producir un error con el mensaje de error que indica que falta el permiso necesario unwrap.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

PowerShell

1. 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
   

2. 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 o insertar datos en columnas cifradas. Estas consultas deben parametrizarse e Invoke-SqlCmd no admite consultas con parámetros.

Pasos siguientes

• Desarrollo de aplicaciones con Always Encrypted

Consulte también

• Documentación de Always Encrypted
• Documentación de Always Encrypted con enclaves seguros
• Aprovisionamiento de claves de Always Encrypted mediante SQL Server Management Studio
• Configurar Always Encrypted con PowerShell
• Asistente de Always Encrypted
• Consulta de columnas mediante Always Encrypted con SQL Server Management Studio