COPIA DE SEGURIDAD DE CLAVE SIMÉTRICA EN SQL SERVER

Las llaves simétricas deberían tener una copia de seguridad, pero lamentablemente esto no es posible debido a que dentro de las sentencias T-SQL para manejar las llaves simétricas no hay ninguna que sirva para sacar copias de seguridad, sin embargo hay dos atributos especiales que fueron especificados en la creación de la llave simétrica:
IDENTITY_VALUE: el cual SQL Server usa para generar in valor GUID para la llave.
KEY_SOURCE: el cual SQL Server usa como material para poder generar la llave en sí.
Si estos dos atributos fueron especificados en la creación de la llave simétrica, entonces esta podrá ser recreada siempre y cuando se tengan copias de seguridad de los objetos usados para generarla: el certificado usado.

Ejemplo de cómo sacar la copia de seguridad de la Master Key >y del Certificado los cuáles serán usados para recrear la creación de la llave simétrica.

USE FinancieraDemoBD
GO

-- sacaremos una copia de seguridad de la DMK
BACKUP MASTER KEY TO FILE = 'D:\BACKUP\PERSONAL\DMK.dat'
ENCRYPTION BY PASSWORD = 'TopSecret2020MK';
GO

-- Sacaremos una copia de seguridad del certificado
BACKUP CERTIFICATE FinancieraCertificado TO FILE = 'D:\BACKUP\PERSONAL\FinancieraCertificadoCS.cer'
WITH PRIVATE KEY ( FILE = 'D:\BACKUP\PERSONAL\FinancieraCertificadoCS.key' ,
ENCRYPTION BY PASSWORD = 'TopSecret2020C' );
GO

Ahora borraremos la llave simetrica, el certificado y la master key

DROP SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC
GO

-- Ahora perderemos el certificado.
DROP CERTIFICATE FinancieraCertificado
GO

-- Ahora perderemos la DMK
DROP  MASTER KEY
GO

Al no tener la llave simetrica la informacion es inaccesible. Entonces, para recrear nuestra configuración, primero restauraremos la DMK, luego se abre la DMK para poder restaurar el certificado, luego se restaura el certificado y después se recrea la creación de la llave simétrica con el mismo valor de KEY_SOURCE y IDENTITY_VALUE .

RESTORE MASTER KEY
FROM FILE = 'D:\BACKUP\PERSONAL\DMK.dat'
DECRYPTION BY PASSWORD = 'TopSecret2020MK'
ENCRYPTION BY PASSWORD = 'TopSecret2020MKN'
GO

-- Ahora abrimos la DMK sino no podremos restaurar el certificado
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'TopSecret2020MKN'
GO

-- Ahora restauramos el certificado
CREATE CERTIFICATE FinancieraCertificado
FROM FILE = 'D:\BACKUP\PERSONAL\FinancieraCertificadoCS.cert'
WITH PRIVATE KEY (FILE = 'D:\BACKUP\PERSONAL\FinancieraCertificadoCS.key',
DECRYPTION BY PASSWORD = 'TopSecret2020C');
GO

-- Ahora recreamos la llave simetrica
CREATE SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC
WITH
KEY_SOURCE = 'ClaveSimetrica2020',
IDENTITY_VALUE = 'MiValorIdentidad',
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE FinancieraCertificado
GO

A continuación se vuelve a consultar los datos usando el Certificado y la desencriptación por clave y volvemos a obtener la información desencriptada

OPEN SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC DECRYPTION BY CERTIFICATE FinancieraCertificado;

-- Ahora seleccionamos los datos pero antes los desencriptamos con la llave simetrica
SELECT MATRICULACOLABORADOR, CONVERT(VARCHAR(100),DECRYPTBYKEY(NOMBRES)) AS NOMBRES,
CONVERT(VARCHAR(100),DECRYPTBYKEY(APELLIDOS)) AS APELLIDOS
FROM dbo.COLABORADOR
/*
MATRICULACOLABORADOR	NOMBRES	APELLIDOS
CAR001	CESAR ANTONIO	AVILA ROBLES
*/

-- Y ahora volvemos a cerrar la llave simetrica
CLOSE SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC

10/09/2020 04:25:00 p. m. Share: