Consulta de columnas mediante Always Encrypted con SQL Server Management Studio

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

En este artículo se describe cómo consultar columnas, cifradas con Always Encrypted mediante SQL Server Management Studio (SSMS). Con SSMS, puede:

• Recuperar los valores de texto cifrado almacenados en columnas cifradas.
• Recuperar los valores de texto no cifrado almacenados en columnas cifradas.
• Enviar valores de texto no cifrado dirigidos a columnas cifradas (por ejemplo, en instrucciones INSERT o UPDATE y como parámetros de búsqueda de las cláusulas WHERE en las instrucciones SELECT).

Nota:

El uso de claves maestras de columna almacenadas en un HSM administrado en Azure Key Vault requiere SSMS 18.9 o versiones posteriores.

Recuperación de los valores de texto cifrado almacenados en columnas cifradas

La ejecución de consultas SELECT que recuperan texto cifrado de los datos almacenados en las columnas cifradas (sin descifrar los datos) no requiere tener acceso a las claves maestras de columna que protegen los datos. Para recuperar valores desde una columna cifrada como texto cifrado en SSMS:

1. Asegúrese de que tiene acceso a los metadatos sobre las claves que protegen las columnas en las que está ejecutando la consulta. Aunque no es necesario tener acceso a las claves maestras de columna reales, sí se necesitan permisos de nivel de base de datos para ver los objetos de metadatos de clave maestra de columna y de clave de cifrado de columna en la base de datos. Para obtener más información, consulte Permisos para consultar columnas cifradas más abajo.
2. Asegúrese de que Always Encrypted esté deshabilitado para la conexión de base de datos de la ventana del Editor de consultas, desde donde ejecuta una consulta SELECT que recupere los valores del texto cifrado. Consulte Habilitación y deshabilitación de Always Encrypted para una conexión de base de datos más adelante.
3. Ejecute la consulta SELECT. Todos los datos que se recuperen de las columnas cifradas se devolverán como valores binarios (cifrados).

Ejemplo de recuperación de texto cifrado

Suponiendo que SSN es una columna cifrada de la tabla Patients , la consulta que aparece a continuación recuperará los valores binarios de texto cifrado, si Always Encrypted está deshabilitado para la conexión de base de datos.

Recuperación de los valores de texto no cifrado almacenados en columnas cifradas

Para recuperar valores desde una columna cifrada como texto no cifrado (para descifrar los valores):

1. Asegúrese de que tiene acceso a las claves maestras de columna y a los metadatos sobre las claves que protegen las columnas en las que está ejecutando la consulta. Para obtener más información, consulte Permisos para consultar columnas cifradas más abajo.
2. Asegúrese de que Always Encrypted esté habilitado para la conexión de base de datos de la ventana del Editor de consultas, desde donde ejecuta una consulta SELECT que recupere y descifre los datos. Esto indicará al proveedor de datos .NET Framework para SQL Server (que SSMS usa) que descifre las columnas cifradas en el conjunto de resultados de consulta. Consulte Habilitación y deshabilitación de Always Encrypted para una conexión de base de datos más adelante.
3. Ejecute la consulta SELECT. Los datos que se recuperen de las columnas cifradas se devolverán como valores de texto no cifrado de los tipos de datos originales.

Ejemplo de la recuperación de texto sin formato

Suponiendo que SSN es una columna char(11) cifrada de la tabla Patients, la consulta que se muestra a continuación devolverá valores de texto no cifrado, si Always Encrypted está habilitado para la conexión de base de datos y si tiene acceso a la clave maestra de columna configurada para la columna SSN.

Envío de valores de texto no cifrado con columnas cifradas como destino

Para ejecutar una consulta que envía un valor con una columna cifrada como destino, por ejemplo, una consulta que inserta o actualiza un valor almacenado, o filtra según él, en una columna cifrada, haga lo siguiente:

1. Asegúrese de que tiene acceso a las claves maestras de columna y a los metadatos para las claves que protegen las columnas en las que se ejecuta la consulta. Para obtener más información, consulte Permisos para consultar columnas cifradas más abajo.

2. Asegúrese de que Always Encrypted esté habilitado para la conexión de base de datos de la ventana del Editor de consultas, desde donde ejecuta una consulta SELECT que recupere y descifre los datos. Esto indicará al proveedor de datos .NET Framework para SQL Server (que SSMS usa) que descifre las columnas cifradas en el conjunto de resultados de consulta. Consulte Habilitación y deshabilitación de Always Encrypted para una conexión de base de datos más adelante.

3. Asegúrese de que la parametrización de Always Encrypted esté habilitada para la ventana del Editor de consultas. (Requiere al menos la versión 17.0 de SSMS) Declare una variable Transact-SQL e inicialícela con un valor que desea enviar (insertar, actualizar o según el cual filtrar) a la base de datos. Consulte Parametrización de Always Encrypted que aparece más adelante para detalles.

4. Ejecute la consulta; para ello, envíe el valor de la variable Transact-SQL a la base de datos. SSMS convertirá la variable en un parámetro de consulta y cifrará su valor antes de enviarlo a la base de datos.

Ejemplo

Suponiendo que SSN es una columna char(11) cifrada de la tabla Patients , el script a continuación intentará encontrar una fila que contenga '795-73-9838' en la columna SSN y devolverá el valor de la columna LastName , siempre que Always Encrypted esté habilitado para la conexión de base de datos, que Parametrización para Always Encrypted esté habilitado para la ventana del Editor de consultas y que usted tenga acceso a la clave maestra de columna configurada para la columna SSN .

Permisos para consultar columnas cifradas

Para ejecutar cualquier consulta contra las columnas cifradas, incluidas las consultas que recuperan datos en texto cifrado, necesita los permisos VIEW ANY COLUMN MASTER KEY DEFINITION y VIEW ANY COLUMN ENCRYPTION KEY DEFINITION pen la base de datos.

Además de los permisos anteriores, para descifrar cualquier resultado de consulta o para cifrar cualquier parámetro de consulta (generados por la parametrización de las variables de Transact-SQL), también necesita permisos de almacén de claves para acceder a la clave maestra de columna que protege las columnas de destino y usarla. Para obtener información detallada sobre los permisos de almacén de claves, vaya a Creación y almacenamiento de claves maestras de columna para Always Encrypted y busque la sección correspondiente a su almacén de claves.

Habilitación y deshabilitación de Always Encrypted para una conexión de base de datos

Cuando se conecta a una base de datos en SSMS, puede habilitar o deshabilitar Always Encrypted para la conexión de base de datos. De manera predeterminada, Always Encrypted está deshabilitado.

Habilitar Always Encrypted para una conexión de base de datos indica al proveedor de datos .NET Framework para SQL Server, que SQL Server Management Studio usa, que intente realizar lo siguiente de manera transparente:

• Descifrar los valores que se recuperan de las columnas cifradas y se devuelven en los resultados de la consulta.
• Cifrar los valores de las variables Transact-SQL parametrizadas que tienen como destino las columnas de base de datos cifradas.

Si no habilita Always Encrypted para una conexión, el proveedor de datos de .NET Framework para SQL Server, que SSMS utiliza, no intentará cifrar los parámetros de consulta ni descifrar los resultados.

Puede habilitar o deshabilitar Always Encrypted cuando cree una conexión nueva o cambie una conexión existente mediante el cuadro de diálogo Conectar a servidor.

Para habilitar (deshabilitar) Always Encrypted:

1. Abra el cuadro de diálogo Conectar a servidor (consulte Conectarse a una instancia de SQL Server para obtener más información).
2. Seleccione Opciones.
3. Seleccione la pestaña Always Encrypted. Para habilitar Always Encrypted, seleccione Habilitar Always Encrypted (cifrado de columna). Para deshabilitar Always Encrypted, asegúrese de que Habilitar Always Encrypted (cifrado de columna) no esté seleccionado.
4. Seleccione Conectar.

Sugerencia

Para alternar entre Always Encrypted habilitado y deshabilitado para una ventana existente del Editor de consultas, haga lo siguiente:

1. Haga clic con el botón derecho en cualquier parte de la ventana del Editor de consultas.
2. Seleccione Conexión>Cambiar conexión.... Se abrirá el cuadro de diálogo Conectar a servidor para la conexión actual de la ventana del editor de consultas.
3. Habilite o deshabilite Always Encrypted siguiendo los pasos anteriores y haga clic en Conectar.

Nota:

Para ejecutar instrucciones que aprovechan un enclave seguro del lado servidor cuando se usa Always Encrypted con enclaves seguros, consulte Ejecución de instrucciones Transact-SQL mediante enclaves seguros.

Parametrización de Always Encrypted

Parametrización de Always Encrypted es una característica de SQL Server Management Studio que convierte automáticamente las variables Transact-SQL en parámetros de consulta (instancias de SqlParameter Class). (Requiere al menos la versión 17.0 de SSMS) Esto permite que el proveedor de datos .NET Framework para SQL Server subyacente detecte datos con columnas cifradas como destino y cifre dichos datos antes de enviarlos a la base de datos.

Sin la parametrización, el proveedor de datos .NET Framework pasa cada instrucción que usted crea en el Editor de consultas como una consulta no parametrizada. Si la consulta contiene literales o variables Transact-SQL con columnas cifradas como destino, el proveedor de datos .NET Framework para SQL Server no podrá detectarlas ni cifrarlas antes de enviar la consulta a la base de datos. Como resultado, la consulta presentará un error debido a un error de coincidencia de tipos (entre la variable Transact-SQL literal de texto no cifrado y la columna cifrada). Por ejemplo, la consulta siguiente presentará un error sin parametrización, suponiendo que la columna SSN está cifrada.

DECLARE @SSN NCHAR(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN

Habilitación y deshabilitación de parametrización de Always Encrypted

Parametrización de Always Encrypted está deshabilitada de manera predeterminada.

Para habilitar/deshabilitar parametrización de Always Encrypted para la ventana actual del Editor de consultas, haga lo siguiente:

1. Seleccione Consulta en el menú principal.
2. Seleccione Opciones de consulta….
3. Vaya a Ejecución>Avanzadas.
4. Seleccione o anule la selección de Habilitar parametrización de Always Encrypted.
5. Seleccione Aceptar.

Para habilitar/deshabilitar parametrización de Always Encrypted para ventanas futuras del Editor de consultas, haga lo siguiente:

1. Seleccione Herramientas en el menú principal.
2. Seleccione Opciones... .
3. Vaya a Ejecución de consulta>SQL Server>Avanzadas.
4. Seleccione o anule la selección de Habilitar parametrización de Always Encrypted.
5. Seleccione Aceptar.

Si ejecuta una consulta en una ventana del Editor de consultas que usa una conexión de base de datos con Always Encrypted habilitado, pero la parametrización no está habilitada para la ventana del Editor de consultas, se le pedirá que la habilite.

Nota:

Parametrización de Always Encrypted funciona solo en las ventanas del Editor de consultas que usan conexiones de base de datos con Always Encrypted habilitadas (consulte Habilitación y deshabilitación de parametrización de Always Encrypted). Ninguna variable Transact-SQL será parametrizada si la ventana del Editor de consultas usa una conexión de base de datos sin tener habilitado Always Encrypted.

Funcionamiento de parametrización de Always Encrypted

Si la parametrización para Always Encrypted y el comportamiento de Always Encrypted en la conexión de base de datos están habilitados para una ventana del Editor de consultas, SQL Server Management Studio intentará parametrizar las variables Transact-SQL que cumplen con las condiciones de requisitos previos siguientes:

• Se declaran e inicializan en la misma instrucción (inicialización alineada). Las variables declaradas que usan instrucciones SET independientes no se parametrizarán.
• Se inicializan con un solo literal. Las variables que se inicializan con expresiones que incluyen cualquier operador o función no se parametrizarán.

A continuación, aparecen ejemplos de variables que SQL Server Management Studio parametrizará.

DECLARE @SSN char(11) = '795-73-9838';
   
DECLARE @BirthDate date = '19990104';
DECLARE @Salary money = $30000;

Y, a continuación, algunos ejemplos de variables que SQL Server Management Studio no intentará parametrizar:

DECLARE @Name nvarchar(50); --Initialization separate from declaration
SET @Name = 'Abel';

DECLARE @StartDate date = GETDATE(); -- a function used instead of a literal

DECLARE @NewSalary money = @Salary * 1.1; -- an expression used instead of a literal

Para que un intento de parametrización se realice correctamente:

• El tipo de literal que se use para la inicialización de la variable que se va a parametrizar debe coincidir con el tipo de la declaración de variable.
• Si el tipo declarado de la variable es un tipo de fecha o de hora, la variable se debe inicializar mediante una cadena con uno de los siguientes formatos compatibles con ISO 8601.

Estos son ejemplos de declaraciones de variable Transact-SQL que generarán errores de parametrización:

DECLARE @BirthDate date = '01/04/1999' -- unsupported date format   
   
DECLARE @Number int = 1.1 -- the type of the literal does not match the type of the variable   

SQL Server Management Studio usa IntelliSense para informarle las variables que se pueden parametrizar correctamente y cuáles son los intentos de parametrización que presentan error (y por qué).

Una declaración de una variable que se puede parametrizar correctamente está marcada con un carácter de subrayado de advertencia en el Editor de consultas. Si mantiene el puntero sobre una instrucción de declaración que se marcó con un carácter de subrayado de advertencia, verá los resultados del proceso de parametrización, incluidos los valores de las propiedades clave del objeto SqlParameter resultante (al que se asigna la variable): SqlDbType, Size, Precision, Scale, SqlValue. También puede ver la lista completa de todas las variables que se parametrizaron correctamente en la pestaña Advertencia de la vista Lista de errores . Para abrir la vista Lista de errores , seleccione Vista en el menú principal y, luego, seleccione Lista de errores.

Si SQL Server Management Studio intentó parametrizar una variable, pero la parametrización presentó un error, la declaración de la variable se marcará con un carácter de subrayado de error. Si mantiene el puntero sobre una instrucción de declaración que se marcó con un carácter de subrayado de error, recibirá los resultados del error. También podrá ver la lista completa de errores de parametrización de todas las variables en la pestaña Error de la vista Lista de errores . Para abrir la vista Lista de errores , seleccione Vista en el menú principal y, luego, seleccione Lista de errores.

La captura de pantalla siguiente muestra un ejemplo de 6 declaraciones de variable. SQL Server Management Studio parametrizó correctamente las 3 primeras variables. Las últimas tres variables no cumplieron las condiciones requeridas para la parametrización y, por tanto, SQL Server Management Studio no intentó parametrizarlas (sus declaraciones no están marcadas de ninguna manera).

Otro ejemplo que aparece a continuación muestra 2 variables que cumplen con las condiciones requeridas para la parametrización, pero el intento de parametrización presenta un error porque las variables se inicializaron de manera incorrecta.

Nota:

Como Always Encrypted admite un subconjunto limitado de conversiones de tipo, en muchos casos se requiere que ese tipo de datos de una variable Transact-SQL sea el mismo tipo de la columna de base de datos de destino. Por ejemplo, suponiendo que el tipo de la columna SSN de la tabla Patients sea char(11), la consulta siguiente presentará un error, debido a que el tipo de la variable @SSN , que es nchar(11), no coincide con el tipo de la columna.

DECLARE @SSN nchar(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN;

Msg 402, Level 16, State 2, Line 5   
The data types char(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') collation_name = 'Latin1_General_BIN2' 
and nchar(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') are incompatible in the equal to operator.

Nota:

Sin parametrización, la columna completa, incluidas las conversiones de tipo, se procesan dentro de SQL Server/Azure SQL Database. Con la parametrización habilitada, .NET Framework ejecuta algunas conversiones de tipo dentro de SQL Server Management Studio. Debido a las diferencias entre el sistema de tipo de .NET Framework y el sistema de tipo de SQL Server (por ejemplo, una precisión distinta de algunos tipos, como float), una consulta que se ejecuta con parametrización habilitada puede generar resultados distintos a los de la consulta ejecutada sin la parametrización habilitada.

Pasos siguientes

• Desarrollo de aplicaciones con Always Encrypted

Consulte también

• Always Encrypted