En esta publicación del blog, exploraremos el concepto de cifrar, almacenar y descifrar datos en SQL Server. SQL Server proporciona una infraestructura jerárquica de cifrado y gestión de claves, que cifra datos en diferentes capas utilizando certificados, claves asimétricas y claves simétricas.

Es crucial comprender las diferentes capas de protección, su interacción, la sobrecarga de rendimiento y las mejores prácticas. La jerarquía de cifrado de SQL Server se representa visualmente en la documentación de Books Online.

Echemos un vistazo a un script que demuestra el proceso de configuración de rutinas de cifrado y descifrado en SQL Server:

-- Cambie el nombre de la base de datos a continuación
USE SQLServer365;

-- Cambie la ruta de la copia de seguridad de la clave maestra de la base de datos
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'rZVb3DwZ8Vptc2#vm4wapspB';
BACKUP MASTER KEY TO FILE = 'C:\SQL\Backup\DatabaseMasterKeys\SQLServer365DatabaseMasterKey' ENCRYPTION BY PASSWORD = 'ratr7XgGGSJ5dM4QzAaXc8cj';

-- Cambie la ruta de la copia de seguridad del certificado
BACKUP CERTIFICATE CertBankDetails TO FILE = 'C:\SQL\Backup\Certificates\CertBankDetails';

-- Crear tabla
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.BankDetails') AND [type] = 'U')
    CREATE TABLE dbo.BankDetails (
        BankDetailsID INT IDENTITY(1, 1) NOT NULL CONSTRAINT [PK_BankDetails:BankDetailsID] PRIMARY KEY,
        CustomerID INT NOT NULL,
        SortCode VARBINARY(128) NOT NULL,
        AccountNumber VARBINARY(128) NOT NULL,
        InsertDate DATETIME NOT NULL CONSTRAINT [DF_BankDetails:InsertDate] DEFAULT (GETDATE())
    ) ON [PRIMARY]
ELSE
    PRINT 'Error: La tabla "dbo.BankDetails" ya existe, modifique el script para crear un nombre de tabla que no exista';

-- Crear clave maestra de la base de datos
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'rZVb3DwZ8Vptc2#vm4wapspB'
ELSE
    PRINT 'Error: ¡La clave maestra de la base de datos ya existe!';

-- Copia de seguridad de la clave maestra
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'rZVb3DwZ8Vptc2#vm4wapspB';
BACKUP MASTER KEY TO FILE = 'C:\SQL\Backup\DatabaseMasterKeys\SQLServer365DatabaseMasterKey' ENCRYPTION BY PASSWORD = 'ratr7XgGGSJ5dM4QzAaXc8cj';

-- Crear certificado
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'CertBankDetails')
    CREATE CERTIFICATE CertBankDetails WITH SUBJECT = 'Certificado de Detalles Bancarios', EXPIRY_DATE = '25/02/2014'
ELSE
    PRINT 'Error: El certificado "CertBankDetails" ya existe, modifique el script para crear un certificado que no exista';

-- Copia de seguridad del certificado
BACKUP CERTIFICATE CertBankDetails TO FILE = 'C:\SQL\Backup\Certificates\CertBankDetails';

-- Crear clave simétrica
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = 'SymKeyBankDetails')
    CREATE SYMMETRIC KEY SymKeyBankDetails WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CertBankDetails
ELSE
    PRINT 'Error: La clave simétrica "SymKeyBankDetails" ya existe, modifique el script para crear una clave simétrica que no exista';

-- Crear procedimiento de cifrado
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.spInsertBankDetails') AND [type] IN ('P'))
    DROP PROCEDURE dbo.spInsertBankDetails;
GO

CREATE PROCEDURE dbo.spInsertBankDetails
AS
BEGIN
    -- Abrir clave simétrica
    OPEN SYMMETRIC KEY SymKeyBankDetails DECRYPTION BY CERTIFICATE CertBankDetails;

    -- Insertar un registro
    INSERT INTO SQLServer365.dbo.BankDetails (CustomerID, SortCode, AccountNumber, InsertDate)
    VALUES (1, EncryptByKey(Key_GUID('SymKeyBankDetails'), '01-02-03'), EncryptByKey(Key_GUID('SymKeyBankDetails'), '01234567'), GETDATE());
END
GO

-- Crear procedimiento de descifrado
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.spGetBankDetails') AND [type] IN ('P'))
    DROP PROCEDURE dbo.spGetBankDetails;
GO

CREATE PROCEDURE dbo.spGetBankDetails
AS
BEGIN
    -- Abrir clave simétrica
    OPEN SYMMETRIC KEY SymKeyBankDetails DECRYPTION BY CERTIFICATE CertBankDetails;

    -- Devolver registro descifrado
    SELECT BankDetailsID, CustomerID, CONVERT(VARCHAR, DecryptByKey(SortCode)) AS SortCode, CONVERT(VARCHAR, DecryptByKey(AccountNumber)) AS AccountNumber, InsertDate
    FROM SQLServer365.dbo.BankDetails;
END
GO

-- Insertar un registro cifrado
EXEC SQLServer365.dbo.spInsertBankDetails;
GO

-- Devolver datos cifrados
SELECT BankDetailsID, CustomerID, SortCode, AccountNumber, InsertDate
FROM SQLServer365.dbo.BankDetails;
GO

-- Devolver datos descifrados
EXEC SQLServer365.dbo.spGetBankDetails;
GO

/* Datos sin cifrar para comparación de rendimiento */
-- Establecer contexto de base de datos
USE SQLServer365;
GO

-- Crear tabla
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.BankDetailsNoEncryption') AND [type] = 'U')
    CREATE TABLE dbo.BankDetailsNoEncryption (
        BankDetailsNoEncryptionID INT IDENTITY(1, 1) NOT NULL CONSTRAINT [PK_BankDetailsNoEncryption:BankDetailsNoEncryptionID] PRIMARY KEY,
        CustomerID INT NOT NULL,
        SortCode VARCHAR(50) NOT NULL,
        AccountNumber VARCHAR(50) NOT NULL,
        InsertDate DATETIME NOT NULL CONSTRAINT [DF_BankDetailsNoEncryption:InsertDate] DEFAULT (GETDATE())
    ) ON [PRIMARY]
ELSE
    PRINT 'Error: La tabla "dbo.BankDetailsNoEncryption" ya existe, modifique el script para crear un nombre de tabla que no exista';

-- Crear procedimiento de inserción
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.spInsertBankDetailsNoEncryption') AND [type] IN ('P'))
    DROP PROCEDURE dbo.spInsertBankDetailsNoEncryption;
GO

CREATE PROCEDURE dbo.spInsertBankDetailsNoEncryption
AS
BEGIN
    -- Insertar un registro
    INSERT INTO SQLServer365.dbo.BankDetailsNoEncryption (CustomerID, SortCode, AccountNumber, InsertDate)
    VALUES (1, '01-02-03', '01234567', GETDATE());
END
GO

-- Crear procedimiento de obtención
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.spGetBankDetailsNoEncryption') AND [type] IN ('P'))
    DROP PROCEDURE dbo.spGetBankDetailsNoEncryption;
GO

CREATE PROCEDURE dbo.spGetBankDetailsNoEncryption
AS
BEGIN
    SELECT BankDetailsNoEncryptionID, CustomerID, SortCode, AccountNumber, InsertDate
    FROM SQLServer365.dbo.BankDetailsNoEncryption;
END
GO

-- Insertar un registro sin cifrar
EXEC SQLServer365.dbo.spInsertBankDetailsNoEncryption;
GO

-- Devolver datos
EXEC SQLServer365.dbo.spGetBankDetailsNoEncryption;
GO

Después de ejecutar el script anterior, podemos comparar el rendimiento de las rutinas cifradas y sin cifrar. Utilizando SQLQueryStress de Adam Machanic, ejecutamos las operaciones de inserción y selección 100 veces en 10 hilos. Los resultados son los siguientes:

Como puede ver, hay una sobrecarga significativa al usar el cifrado y descifrado. Por lo tanto, es esencial ser selectivo y solo cifrar los datos que requieren protección. Considere el uso de un Módulo de Seguridad de Hardware (HSM) para mantener las claves de cifrado separadas de los datos cifrados. Esto puede descargar la sobrecarga de cifrado de SQL Server al HSM, mejorando el rendimiento.

Finalmente, aquí hay tres recomendaciones:

1. Haga copias de seguridad de los certificados y claves: Asegúrese de hacer copias de seguridad de todas las claves maestras de la base de datos y los certificados. Guarde las copias de seguridad en una unidad diferente, cinta o matriz diferente. También es recomendable mantener las copias de seguridad fuera del sitio.
2. Tenga en cuenta la fecha de vencimiento de los certificados: Manténgase alerta y monitoree las fechas de vencimiento de sus certificados. Los certificados vencidos pueden causar errores en la aplicación.
3. Los scripts salvan vidas: En lugar de depender de herramientas GUI, use scripts para administrar sus rutinas de cifrado. Los scripts se pueden guardar, hacer copias de seguridad, recuperar y ejecutar de manera más eficiente que los enfoques basados en GUI o asistentes.

Siguiendo estas recomendaciones y comprendiendo las implicaciones de rendimiento, puede implementar y mantener el cifrado de manera efectiva en su entorno de SQL Server.

¡Feliz cifrado!

Chris