COPIA DE SEGURIDAD (BACKUP) DE CERTIFICADO EN SQL SERVER Y RESTAURACIÓN DEL CERTIFICADO

Los certificados deben tener una copia de seguridad como la base de datos misma debido a que en el caso de que perdamos el certificado solo esa copia de seguridad nos podrá asegurar la recuperación de toda la data encriptada con ese certificado, caso contrario, la información permanecerá perdida debido a que ya no se tiene el mismo certificado que la encriptó.
Breve Ejemplo de creación de certificado y registro de datos encriptados.

IF (DATABASEPROPERTY('FinancieraDemoBD','version') > 0)
BEGIN
USE MASTER
ALTER DATABASE FinancieraDemoBD SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE FinancieraDemoBD
END
 
 
-- Creamos la Base de Datos de prueba
use master 
GO
 
create database FinancieraDemoBD
GO
 
-- Usamos la Base de Datos de Prueba
use FinancieraDemoBD
GO
 
-- Creamos una tabla cliente con las columnas Nombre y NombreCorto
-- de tipo varbinary para que contenga la informacion encriptada
CREATE TABLE [dbo].[CLIENTE](
	[IDCLIENTE] [int] IDENTITY(1,1) NOT NULL,
	[FECING] [datetime] NULL,
	[USUING] [varchar](6) NULL,	
	[FECACT] [datetime] NULL,
	[USUACT] [varchar](6) NULL,
	[NOMBRE] [varbinary](max) NOT NULL,
	[NOMBRECORTO] [varbinary](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[IDCLIENTE] ASC
)
) ON [PRIMARY]
 
GO 
 
 
 
-- Crear una Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ClaveSecretaINFO2020'
GO
 
-- Crear el Certificado
CREATE CERTIFICATE FinancieraCertificado WITH SUBJECT='INFOTACTICAS Certificate',
EXPIRY_DATE = '20211231'
GO
 
 
-- Consultar los certificados creados en la base de datos
SELECT name, pvt_key_encryption_type_desc, subject, expiry_date
FROM sys.certificates
/*
name			pvt_key_encryption_type_desc	subject				expiry_date
FinancieraCertificado	ENCRYPTED_BY_MASTER_KEY		INFOTACTICAS Certificate	2021-12-31 00:00:00.000
*/
 
 
-- Insertar un valor
INSERT INTO dbo.CLIENTE (FECING, USUING, NOMBRE, NOMBRECORTO)
VALUES (getdate(), 'IRE001', ENCRYPTBYCERT(CERT_ID('FinancieraCertificado'),'CARLOS FLORES RIOS'), ENCRYPTBYCERT(CERT_ID('FinancieraCertificado'),'CARLOS FLORES'))
GO
-- Select convencional
SELECT IDCLIENTE, NOMBRE, NOMBRECORTO, FECING
FROM dbo.CLIENTE
/*
IDCLIENTE	NOMBRE			NOMBRECORTO		FECING
1		0xC118457BFAA...	0xEF308BFC78424F717...	2020-10-08 23:47:39.520
*/
 
 
-- Select desencriptando los datos con el certificado correcto
SELECT IDCLIENTE, CONVERT(VARCHAR(100), DECRYPTBYCERT(CERT_ID('FinancieraCertificado'),NOMBRE)) as 'NOMBRE',
CONVERT(VARCHAR(70), DECRYPTBYCERT(CERT_ID('FinancieraCertificado'),NOMBRECORTO)) as 'NOMBRECORTO',
FECING
FROM dbo.CLIENTE
/*
IDCLIENTE	NOMBRE			NOMBRECORTO	FECING
1		CARLOS FLORES RIOS	CARLOS FLORES	2020-10-08 23:47:39.520
*/

Ejemplo de cómo sacarle una copia de seguridad al certificado.

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

Se borrará el certificado para simular su pérdida en escenarios donde se hace un backup de la base de datos y se restaurá en otro servidor y dicho certificado no se guardó con el backup.

-- Eliminar el certificado.
DROP CERTIFICATE FinancieraCertificado
GO
 
 
-- Podemos confirmar que nuestro certificado no esta y no podemos desencriptar nuestra informacion
SELECT name, pvt_key_encryption_type_desc, subject, expiry_date
FROM sys.certificates
 
SELECT IDCLIENTE, CONVERT(VARCHAR(100), DECRYPTBYCERT(CERT_ID('FinancieraCertificado'),NOMBRE)) as 'NOMBRE',
CONVERT(VARCHAR(70), DECRYPTBYCERT(CERT_ID('FinancieraCertificado'),NOMBRECORTO)) as 'NOMBRECORTO',
FECING
FROM dbo.CLIENTE
 
/*
IDCLIENTE	NOMBRE	NOMBRECORTO	FECING
1		NULL	NULL		2020-10-08 23:47:39.520
*/

Probaremos a crear nuevamente el certificado a ver si todo funcionara nuevamente.

-- Creamos el certificado
CREATE CERTIFICATE FinancieraCertificado WITH SUBJECT='INFOTACTICAS Certificate',
EXPIRY_DATE = '20211231'
GO
 
-- Probamos desencriptar la informacion
SELECT IDCLIENTE, CONVERT(VARCHAR(100), DECRYPTBYCERT(CERT_ID('FinancieraCertificado'),NOMBRE)) as 'NOMBRE',
CONVERT(VARCHAR(70), DECRYPTBYCERT(CERT_ID('FinancieraCertificado'),NOMBRECORTO)) as 'NOMBRECORTO',
FECING
FROM dbo.CLIENTE
/*
IDCLIENTE	NOMBRE	NOMBRECORTO	FECING
1		NULL	NULL		2020-10-08 23:47:39.520
*/

Sin embargo, no funcionó debido a que el certificado que se usó no es el mismo que encripto la información, a pesar de que se creó el certificado con la misma instrucción. Volveremos a eliminar el certificado y restaurarlo desde el backup.

-- Volvemos a eliminar el certificado
DROP CERTIFICATE FinancieraCertificado
GO
 
-- Ahora restauramos el certificado desde nuestra copia de seguridad
CREATE CERTIFICATE FinancieraCertificado
FROM FILE = 'D:\BACKUPS\PERSONAL\FinancieraCertificado.cer'
WITH PRIVATE KEY (FILE = 'D:\BACKUPS\PERSONAL\FinancieraCertificado.key',
DECRYPTION BY PASSWORD = 'TopSecretINFO2020');
GO
 
-- Finalmente volvemos a consultar la informacion
SELECT IDCLIENTE, CONVERT(VARCHAR(100), DECRYPTBYCERT(CERT_ID('FinancieraCertificado'),NOMBRE)) as 'NOMBRE',
CONVERT(VARCHAR(70), DECRYPTBYCERT(CERT_ID('FinancieraCertificado'),NOMBRECORTO)) as 'NOMBRECORTO',
FECING
FROM dbo.CLIENTE

La clave especificada en: DECRYPTION BY PASSWORD = 'TopSecretINFO2020' tiene que ser la misma que la especificada en: ENCRYPTION BY PASSWORD = 'TopSecretINFO2020' y no tiene que se la misma que se usó al crear la MASTER KEY en: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ClaveSecretaINFO2020'

10/09/2020 10:52:00 a. m. Share: