SQL Server 2016 - Cómo cifrar tus datos usando Always Encrypted

¡Hola, chicos!
¿Listo para otro artículo?

Introducción

Con la llegada de RGPD, la preocupación de las empresas por la seguridad de los datos es cada vez mayor y un área que antes a veces se dejaba de lado ahora está más que nunca en el punto de mira. Como resultado, los profesionales de TI, especialmente los DBA, han estado buscando formas de reducir los riesgos de exposición de los datos y una de las formas de hacerlo es cifrando los datos para evitar el acceso no autorizado por parte de terceros.

Mi idea en este artículo es demostrar una solución de SQL Server que le permite cifrar datos, que es lo que Siempre cifrado, disponible a partir de SQL Server 2016 en las ediciones Express, Standard, Enterprise y Developer (Express y Standard a partir de 2016 SP1).

No olvides revisar mi publicación. SQL Server 2008: cómo cifrar sus datos utilizando el cifrado de datos transparente (TDE), otra solución de cifrado de datos de SQL Server.

Para los ejemplos siguientes, utilizaré el siguiente script para generar la base:

IF (OBJECT_ID('dbo.Pessoa') IS NOT NULL) DROP TABLE dbo.Pessoa
CREATE TABLE dbo.Pessoa
(
    [Name] NVARCHAR(101),
    [BirthDate] DATE,
    [EmailAddress] NVARCHAR(50),
    [Phone] NVARCHAR(20)
)

INSERT INTO dbo.Pessoa
VALUES
( N'Jon Yang', N'1971-10-06T00:00:00', N'jon24@adventure-works.com', N'1 (11) 500 555-0162' ), 
( N'Eugene Huang', N'1976-05-10T00:00:00', N'eugene10@adventure-works.com', N'1 (11) 500 555-0110' ), 
( N'Ruben Torres', N'1971-02-09T00:00:00', N'ruben35@adventure-works.com', N'1 (11) 500 555-0184' ), 
( N'Christy Zhu', N'1973-08-14T00:00:00', N'christy12@adventure-works.com', N'1 (11) 500 555-0162' ), 
( N'Elizabeth Johnson', N'1979-08-05T00:00:00', N'elizabeth5@adventure-works.com', N'1 (11) 500 555-0131' ), 
( N'Julio Ruiz', N'1976-08-01T00:00:00', N'julio1@adventure-works.com', N'1 (11) 500 555-0151' ), 
( N'Janet Alvarez', N'1976-12-02T00:00:00', N'janet9@adventure-works.com', N'1 (11) 500 555-0184' ), 
( N'Marco Mehta', N'1969-11-06T00:00:00', N'marco14@adventure-works.com', N'1 (11) 500 555-0126' ), 
( N'Rob Verhoff', N'1975-07-04T00:00:00', N'rob4@adventure-works.com', N'1 (11) 500 555-0164' ), 
( N'Shannon Carlson', N'1969-09-29T00:00:00', N'shannon38@adventure-works.com', N'1 (11) 500 555-0110' ), 
( N'Jacquelyn Suarez', N'1969-08-05T00:00:00', N'jacquelyn20@adventure-works.com', N'1 (11) 500 555-0169' ), 
( N'Curtis Lu', N'1969-05-03T00:00:00', N'curtis9@adventure-works.com', N'1 (11) 500 555-0117' ), 
( N'Lauren Walker', N'1979-01-14T00:00:00', N'lauren41@adventure-works.com', N'717-555-0164' ), 
( N'Ian Jenkins', N'1979-08-03T00:00:00', N'ian47@adventure-works.com', N'817-555-0185' ), 
( N'Sydney Bennett', N'1973-11-06T00:00:00', N'sydney23@adventure-works.com', N'431-555-0156' ), 
( N'Chloe Young', N'1984-08-26T00:00:00', N'chloe23@adventure-works.com', N'208-555-0142' ), 
( N'Wyatt Hill', N'1984-10-25T00:00:00', N'wyatt32@adventure-works.com', N'135-555-0171' ), 
( N'Shannon Wang', N'1949-12-24T00:00:00', N'shannon1@adventure-works.com', N'1 (11) 500 555-0195' ), 
( N'Clarence Rai', N'1955-10-06T00:00:00', N'clarence32@adventure-works.com', N'1 (11) 500 555-0137' ), 
( N'Luke Lal', N'1983-09-04T00:00:00', N'luke18@adventure-works.com', N'262-555-0112' )

Lo que siempre está cifrado

Always Encrypted es una característica diseñada para proteger datos confidenciales, disponible a partir de SQL Server 2016, como números de tarjetas de crédito o números de identificación nacional (por ejemplo, números de Seguro Social de EE. UU.), almacenados en bases de datos de Azure SQL Database o SQL Server. Always Encrypted permite a los clientes cifrar datos confidenciales en aplicaciones cliente y nunca revelar las claves de cifrado al motor de base de datos. Como resultado, Always Encrypted proporciona una separación entre quienes poseen los datos (y pueden verlos) y quienes los administran (pero no deberían tener acceso).

Una de las mayores ventajas de Always Encrypted es que sólo los usuarios y aplicaciones que tienen la clave de cifrado maestra tienen acceso a los datos originales. Ni siquiera los administradores de bases de datos y otros usuarios administradores de sistemas pueden ver los datos originales. Esto garantiza la seguridad de los datos y la información a un nivel rara vez visto en otras soluciones. Otra gran ventaja de esta solución es que los datos quedan cifrados así como los logs, backups y datos transmitidos a través de la red, garantizando total seguridad en todos los medios de comunicación, incluso si alguien intercepta paquetes durante su transmisión. Ante esto, la pérdida de la clave maestra puede ser fatal para tus datos, pues su recuperación ya no es posible, ya que una copia de seguridad realizada a una base de datos con Always Encrypted sólo puede restaurarse en otra instancia si primero se restaura la clave maestra.

Always Encrypted hace que el cifrado sea casi transparente para las aplicaciones. Un controlador habilitado para Always Encrypted instalado en la computadora cliente logra esto cifrando y descifrando automáticamente datos confidenciales en la aplicación cliente. El controlador cifra las columnas de datos confidenciales antes de pasar los datos al motor de base de datos y reconfigura automáticamente las consultas para conservar la semántica de la aplicación. Del mismo modo, el controlador descifra de forma transparente los datos almacenados en columnas de bases de datos cifradas que están contenidas en los resultados de la consulta.

Sin embargo, aunque esta característica garantiza un excelente nivel de seguridad, tenga en cuenta posibles problemas de rendimiento al utilizarla y un mayor consumo de espacio:

Cifrado de datos siempre cifrado frente a transparente (TDE)

A continuación, enumeraré algunas similitudes y diferencias entre estas dos soluciones de cifrado de SQL Server:

Cifrado determinista o aleatorio

Al utilizar Always Encrypted, verá que hay dos formas de cifrar columnas en esta solución:

• Determinista: El cifrado determinista siempre genera el mismo valor cifrado para el mismo texto. El uso de cifrado determinista proporciona búsquedas, uniones, agrupaciones e indexaciones en columnas cifradas. Sin embargo, también puede permitir a usuarios no autorizados estimar información sobre valores cifrados examinando patrones en la columna cifrada, especialmente si hay un pequeño conjunto de posibles valores cifrados, como Verdadero/Falso o Región Norte/Sur/Este/Oeste.
• Aleatorizado: El cifrado aleatorio utiliza un método que cifra los datos de una forma menos predecible, es decir, para un mismo texto el valor cifrado es diferente. El cifrado aleatorio es más seguro, pero impide el uso de búsquedas, agrupaciones, indexaciones y uniones en columnas cifradas.

Cómo instalar y configurar Always Encrypted en SQL Server

Ahora que ya he explicado un poco sobre la teoría de Always Encrypted, pasemos a la parte práctica.

La forma más práctica de cifrar una columna es utilizando SQL Server Management Studio (SSMS):

Y también se pueden cambiar las tablas existentes usando PowerShell:

Import-Module SqlServer
$sqlConnectionString = "Data Source=sqlserver\sql2017;Initial Catalog=AdventureWorksDW;User ID=dirceu.resende;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Packet Size=4096;Application Name=`"Microsoft SQL Server Management Studio`""
$smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString

# If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:
#   * Prompt for a username and password:
#Add-SqlAzureAuthenticationContext -Interactive

#   * Enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'

$encryptionChanges = @()

# Add changes for table [dbo].[Pessoa]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Pessoa.Name -EncryptionType Randomized -EncryptionKey "CEK_Auto1"
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Pessoa.BirthDate -EncryptionType Deterministic -EncryptionKey "CEK_Auto1"
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Pessoa.EmailAddress -EncryptionType Randomized -EncryptionKey "CEK_Auto1"
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Pessoa.Phone -EncryptionType Randomized -EncryptionKey "CEK_Auto1"

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

Sin embargo, no es posible cifrar columnas de tablas existentes usando Transact-SQL, simplemente cree una nueva tabla cifrada (puede crear una nueva tabla cifrada y migrar los datos de la tabla anterior), que tendría esta sintaxis:

USE [dirceuresende]
GO

CREATE COLUMN MASTER KEY [CMK_Auto1]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/my/D8CDCC7BC22069DEC1FCF32A5C349FCD33424FAA'
)
GO

CREATE COLUMN ENCRYPTION KEY [CEK_Auto1]
WITH VALUES
(
    COLUMN_MASTER_KEY = [CMK_Auto1],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006400380063006400630063003700620063003200320030003600390064006500630031006600630066003300320061003500630033003400390066006300640033003300340032003400660061006100435052EEFF25542A375E23CC9E0BE2B79116313A2A14F3F3AE362B4075368EADB9EB3AB7BC19B14972204CD1E092B6DF0AC9C6BB0067F96AF7EED7C6360273B947EDAE5643932262E43D3C412B1F680929C263C9DD1839D86EDEBCCC6F2F307910CBD123ADB761BFD167F44C77BF6355114955DF371332D429A4DD79C9C51E02A781A977987947CAEF8FED3A30B4B3E9C8EE400F2F416289FE76DCC24B7D509D8ABA35C34959678CF31A704CD4E4C5105CA23CA09F4DD05AEABF413113E093FF7F398C859CA90C05F42DA5C43871DD0DDD95C555B524E342E6125DEA069C6ABF80F7A741B1ADCC73408B3A98C08CAEAC9325079F66256AC8835574FA5E38BCD634ED0BC71DE05EF61E1FEB4ACC1E62DEDDFFC5E86D2E6B3CBE1BEE22588FE29BA3AEB94FA46AF77C22AD7421F606836EC4BC1A3353019106E9B55E20A7DB72A06C63ADB4F438C191DCBA891FEF0F874AAF569C42730EF18E7E84754640D0EC37F48BA8E0455569E59F497849F9295962725D71769FE806B15556A7716341B7560B5E28323FB00846D4EEC0DF2C50339693EF29D83667EE88781FEB079460CB6A2FA49CD5418226D8465957DD5B95EECAF12B0AD436B40FE2F70F7E96A506E3ECACC94C4394A6D103532546541950D58D0057185BB024A98204006697BCB30CE8077CD2A39C7BAED5D76391294A35914B132C798173ED5AE4EDC7551D97F7C5B6
)
GO

CREATE TABLE [dbo].[Pessoa](
    [Name] [NVARCHAR](101) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [BirthDate] [DATE] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [EmailAddress] [NVARCHAR](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [Phone] [NVARCHAR](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
)
GO

Visualización de datos siempre cifrados en SQL Server

Ahora que hemos cifrado las columnas de la tabla dbo.Pessoa, intentemos ver los datos originales, iniciando sesión con un usuario administrador de sistemas:

Para ver los datos originales en SSMS nuevamente, deberá ingresar el parámetro configuración de cifrado de columnas = habilitado en la cadena de conexión:

Después de cambiar esta configuración, podrá volver a ver los datos originales.

Vale la pena recordar que el usuario solo podrá ver los datos originales si tiene los permisos VER CUALQUIER DEFINICIÓN DE CLAVE MAESTRA DE COLUMNA y VER CUALQUIER DEFINICIÓN DE CLAVE DE CIFRADO DE COLUMNA o si está almacenando la Clave de cifrado maestra y la Clave de cifrado de columna en el servidor de base de datos (Tienda de certificaciones de Windows) y el certificado está almacenado en su máquina o su usuario. Si desea utilizar una forma más segura, opte por Azure Key Vault (AKV).

Para ayudarlo a configurar Always Encrypted en sus aplicaciones, he separado dos artículos más que pueden ayudar a los DEV a configurar esta característica con Azure Key Vault (AKV):

• https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault
• https://blogs.msdn.microsoft.com/sqlsecurity/2015/11/10/using-the-azure-key-vault-key-store-provider-for-always-encrypted/

Cómo identificar qué columnas están cifradas con Always Encrypted

Para identificar qué columnas están cifradas con Always Encrypted y qué algoritmo de cifrado se utiliza, simplemente utilice la siguiente consulta:

SELECT
    DB_NAME() AS [database],
    t.[name] AS [table],
    c.[name] AS [column],
    c.encryption_algorithm_name,
    c.[encryption_type],
    c.encryption_type_desc
FROM
    sys.columns c
    JOIN sys.tables t ON t.[object_id] = c.[object_id]
WHERE
    c.encryption_algorithm_name IS NOT NULL

Resultado:

Restricciones siempre cifradas

Always Encrypted no se admite para columnas con las siguientes características (por ejemplo, la cláusula Encrypted FROM no se puede usar en CREATE TABLE/ALTER TABLE de una columna si una de las siguientes condiciones se aplica a la columna):

• Columnas que utilizan uno de los siguientes tipos de datos: xml, marca de tiempo/versión de fila, imagen, ntext, texto, sql_variant, jerarquíaid, geografía, geometría, alias y tipos definidos por el usuario.
• Columnas FILESTREAM
• Columnas con la propiedad IDENTIDAD
• Columnas con la propiedad ROWGUIDCOL.
• Columnas de cadena (varchar, char, etc.) con intercalaciones que no sean bin2
• Columnas que son claves para índices no agrupados que utilizan una columna cifrada aleatoriamente como columna clave (se permiten columnas cifradas deterministamente)
• Columnas que son claves para índices agrupados que utilizan una columna cifrada aleatoriamente como columna clave (se permiten columnas cifradas deterministamente)
• Columnas que son claves para índices de texto completo que contienen columnas cifradas, aleatorias y deterministas.
• Columnas a las que hacen referencia las columnas calculadas (cuando la expresión realiza operaciones no admitidas por Always Encrypted)
• Conjunto de columnas dispersas
• Columnas a las que hacen referencia las estadísticas
• Columnas que utilizan el tipo de alias
• Columnas de partición
• Columnas con restricción predeterminada
• Columnas a las que se hace referencia mediante restricciones únicas cuando se utiliza cifrado aleatorio (se admite el cifrado determinista)
• Columnas de clave principal cuando se utiliza cifrado aleatorio (se admite el cifrado determinista)
• Hacer referencia a columnas en restricciones de clave externa cuando se usa cifrado aleatorio o cuando se usa cifrado determinista, si las columnas referenciadas y de referencia usan algoritmos o claves diferentes
• Columnas a las que hace referencia la restricción de verificación
• Columnas en tablas que utilizan Change Data Capture (CDC)
• Columnas de clave principal en tablas con seguimiento de cambios
• Columnas enmascaradas (usando enmascaramiento de datos dinámico)
• Columnas en tablas de Stretch Database (las tablas con columnas cifradas con Always Encrypted pueden estar habilitadas para Stretch).
• Columnas en tablas externas (PolyBase) (Nota: Se admite el uso de tablas externas y tablas con columnas cifradas en la misma consulta)
• No se admiten parámetros con valores de tabla destinados a columnas cifradas.

Las siguientes cláusulas no se pueden utilizar para columnas cifradas:

• PARA XML
• PARA LA RUTA JSON

Las siguientes funciones no funcionan en columnas cifradas:

• Replicación transaccional o replicación de fusión
• Consultas distribuidas (servidores vinculados)

Enmascaramiento de datos dinámicos

Después de demostrar el uso de esta solución de cifrado, es posible que se pregunte: "¿Qué pasa con el enmascaramiento dinámico de datos (DDM)???". Bueno, para empezar, DDM no es una solución de cifrado de datos sino más bien una solución de enmascaramiento de datos.

Si bien el cifrado de datos evita eficazmente que sus datos se restauren (incluso a partir de copias de seguridad) y se acceda a ellos de manera incorrecta, el enmascaramiento de datos con el enmascaramiento dinámico de datos solo restringe el contenido que se muestra al final de un comando SELECT, es decir, si tiene acceso a un archivo de respaldo, puede restaurarlo en una instancia en la que sea administrador de sistemas y tendrá acceso gratuito a los datos previamente enmascarados.

Para obtener más información sobre el enmascaramiento dinámico de datos, consulte el artículo. SQL Server 2016: enmascaramiento de datos con enmascaramiento de datos dinámico (DDM), donde demuestro cómo usarlo, restricciones e incluso cómo “romper” el enmascaramiento y acceder a los datos originales (incluso sin tener permiso para hacerlo).

Bueno chicos, espero que hayan disfrutado este artículo y ahora comiencen a proteger mejor sus datos.
¡Un abrazo grande y hasta la próxima!