Encriptación de datos en SQL Server – Parte 3

Hola, parece increible que ya estoy en la tercera parte de esta serie de post dedicados a la encriptación de datos en SQL Server, espero que este siendo de ayuda para poder tomar decisiones sobre la encriptación de los datos de una manera mas informada. Si gustan pueden visitar la Parte 2 or la Parte 1 de los post relacionados con este tema. Ahora procederemos a explicar la encriptación basada en llaves simetricas.

Symmetric Key: La encriptación a partir de llaves simétricas tiene como principio que para  encriptar y desencriptar la información se necesita la misma llave. Es decir que si nuestra llave llega a ser pública y accesible para todos los usuarios, estos podrían ver la información sensible que tenemos encriptada en la base de datos. Pero no hay que alarmarse tanto, ya este tipo de encriptación es la más común, y es la que la mayoría de ejemplos en la red usan para explicar el tema de la encriptación de datos en SQL Server. Para crear una llave simétrica, esta debe ser encriptada a partir de un certificado, de una llave asimétrica o de otra llave simétrica, lo cual nos brinda mayor seguridad porque el usuario deberá pasar por encima de todos estos métodos de encriptación para poder acceder a la llave que le permitirá encriptar o desencriptar la información. A continuación veremos un ejemplo simple de encriptación de datos.

-- limpiamos el ambiente
IF (DATABASEPROPERTY('SecureDB','version') > 0)
BEGIN
USE MASTER
ALTER DATABASE
SecureDB SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE
SecureDB
END

— Creamos la base de datos de prueba
USE MASTER
GO
CREATE DATABASE SecureDB
GO

—  Usamos nuestra base de datos de demo
USE SecureDB
GO

— Creamos una tabla cualquiera con una columna DocNum
— de tipo varbinary para que contenga la informacion encriptada
CREATE TABLE dbo.SymetricKeyEncription
(Nombres VARCHAR(100)
,
DocNum VARBINARY(128))
GO

— Creamos una Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MiClaveSegura’
GO

— Creamos el certificado
CREATE CERTIFICATE MiPrimerCertificado WITH SUBJECT=‘DBAMemories Certificate’,
EXPIRY_DATE = ’12/31/2012′
GO

— Creamos la llave simetrica
CREATE SYMMETRIC KEY MiLlaveSimetrica
WITH
KEY_SOURCE = ‘MyKeySource’,
IDENTITY_VALUE = ‘MyIdentityValue’,
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MiPrimerCertificado;
GO

— Hacemos una consulta para visualizar las llaves simetricas de la base de datos
— en este caso podemos observar 2 las cuales son la DMK y la llave simetrica creada
— a partir del certificado.
SELECT name, algorithm_desc, create_date
FROM sys.symmetric_keys
/*
name                     algorithm_desc create_date
———————— ————– ———————–
##MS_DatabaseMasterKey## TRIPLE_DES     2011-10-12 21:34:50.147
MiLlaveSimetrica         AES_256        2011-10-12 21:34:57.500
*/

— Ahora procederemos a ingresar valores a nuestra tabla
— Para eso debemos abrir la llave simetrica
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;

— insertamos un valor
INSERT INTO dbo.SymetricKeyEncription (Nombres, DocNum)
VALUES (‘Juan Perez’, ENCRYPTBYKEY(KEY_GUID(‘MiLlaveSimetrica’),‘12345678’))
GO

— Una vez que se termino de encriptar los datos, se cierra la llave simetrica
CLOSE SYMMETRIC KEY MiLlaveSimetrica

— Intentamos hacer un select convencional
SELECT Nombres, DocNum
FROM dbo.SymetricKeyEncription
/*
Nombres    DocNum
———- ————–
Juan Perez 0x0015A4ACF…
*/

— Ahora para poder ver los datos en claro, debemos desencriptarlos
— con la llave simetrica, y para hacerlo debemos primero abrirla
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;

— Ahora seleccionamos los datos pero antes los desencriptamos con la llave simetrica
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Y ahora volvemos a cerrar la llave simetrica
CLOSE SYMMETRIC KEY MiLlaveSimetrica

— Ahora para probar la seguridad de la llave simetrica
— Creamos un usuario de prueba
CREATE LOGIN [Usuario1] WITH PASSWORD = N’123′
GO
CREATE USER [Usuario1] FOR LOGIN [Usuario1]
GO
GRANT SELECT ON dbo.SymetricKeyEncription TO Usuario1
GO

— Probamos seleccionar la informacion con la llave simetrica
EXECUTE AS USER = ‘Usuario1’
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription;
REVERT

— Como se puede ver nos aparece un error porque el usuario no tiene permisos sobre la llave simetrica

— Para poder hacer uso de la llave simetrica debemos tener el permiso
— de control sobre el certificado que la encripta y sobre la misma llave simetrica.
— Ahora asignaremos ese permiso al Usuario1
GRANT CONTROL ON CERTIFICATE::[MiPrimerCertificado] TO [Usuario1]
GO
GRANT CONTROL ON SYMMETRIC KEY::MiLlaveSimetrica TO Usuario1
GO

— Probamos seleccionar la informacion con la llave simetrica
EXECUTE AS USER = ‘Usuario1’
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;
SELECT Nombress, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription;
CLOSE SYMMETRIC KEY MiLlaveSimetrica
REVERT
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Como vemos ahora la informacion si aparece.

Al igual que los certificados, las llaves simetricas 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 simetricas no hay ninguna que sirva para sacar copias de seguridad, entonces que podemos hacer para poder crear una llave simetrica que pueda recuperarse, bueno hay dos atributos especiales que fueron especificados en la creación de la llave simetrica en el ejemplo anterior, estos dos atributos son:

  • 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 simetrica, entonces esta podrá ser recreada siempre y cuando se tengan copias de seguridad de los objetos usados para generarla, en este caso del certificado usado. Si alguno de estos requisitos no es completado, entonces la información estará en peligro ya que si se llegan a perder alguno de los objetos de encriptación la información puede perderse para siempre. A continuación veremos un ejemplo de cómo sacar copia de seguridad a estos objetos y luego simularemos su perdida y su posterior recuperación.

USE SecureDB
GO

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

-- Sacaremos una copia de seguridad del certificado
BACKUP CERTIFICATE MiPrimerCertificado TO FILE = 'D:\DBAMemories\MiPrimerCertificado.cert'
WITH PRIVATE KEY ( FILE = 'D:\DBAMemories\MiPrimerCertificado.key' ,
ENCRYPTION BY PASSWORD = 'pass' );
GO

-- Ahora perderemos la llave simetrica
DROP SYMMETRIC KEY MiLlaveSimetrica
GO

-- Ahora perderemos el certificado.
DROP CERTIFICATE MiPrimerCertificado
GO

-- Ahora perderemos la DMK
DROP  MASTER KEY
GO

-- Listo ahora si tenemos un desastre completo
-- al no tener la llave simetrica la informacion es inaccesible
-- Ahora para recrear nuestro ambiente
-- Primero restauraremos la DMK
RESTORE MASTER KEY
FROM FILE =
'D:\DBAMemories\DMK.dat'
DECRYPTION BY PASSWORD = 'MiPasswordSeguro'
ENCRYPTION BY PASSWORD = 'MiClaveSegura'
GO

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

-- Ahora restauramos el certificado
CREATE CERTIFICATE MiPrimerCertificado
FROM FILE = 'D:\DBAMemories\MiPrimerCertificado.cert'
WITH PRIVATE KEY (FILE = 'D:\DBAMemories\MiPrimerCertificado.key',
DECRYPTION BY PASSWORD = 'pass');
GO

-- Ahora recreamos la llave simetrica
CREATE SYMMETRIC KEY MiLlaveSimetrica
WITH
KEY_SOURCE = 'MyKeySource',
IDENTITY_VALUE = 'MyIdentityValue',
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MiPrimerCertificado
GO

-- Ahora si, como tenemos todo restaurado abrimos la llave simetrica
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;

-- Consultamos la informacion encriptada
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription
/*
Nombres    DocNum
---------- --------------
Juan Perez 12345678
*/

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

-- Como se puede observar la informacion volvio a estar disponible

Anuncios

Limpieza del Historial de Backups

Hola, queria compartir con todos una experiencia que tuve el día de hoy y es que mientras estaba sacando un backup a una base de datos relativamente pequeña, y el proceso estaba demorando mucho tiempo y el causante de esa demora es algo que normalmente uno no toma en cuenta, y es el historial de backups de las bases de datos de nuestros servidores SQL Server. Muchas veces tenemos servidores que se encuentran corriendo ya varios años e incluso algunos configurados con log shipping lo cual aumente considerablemente nuestro historial de backups, y con el pasar del tiempo los procesos de backup y restore van haciendose mas lentos y es debido a que cada vez que se saca una backup a una base de datos, algunos registros son ingresados dentro de la base de datos de sistema MSDB, y con el pasar del tiempo esta base de datos puede llegar a ser bastante grande si es que no se hace un trabajo de depuracion regularmente.

Entonces ya habia encontrado el problema, ahora tenia que buscar una solucion al mismo y buscando en internet encontre este post de Brent Ozar, donde explica que el problema se puede solucionar haciendo uso del procedimiento almacenado sp_delete_backuphistory, el cual justamente se encarga de limpiar el historial de los backups.

Bueno para no tener este problema mas decidí crear un job que hiciera este trabajo regularmente para mantener activos solo hasta tres meses de historial de los backups. El código que use en el job es el siguiente:


USE msdb;
GO
DECLARE @v_MaxDateHistory DATETIME
SET
@v_MaxDateHistory = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(MONTH,-3,GETDATE()),112))
EXEC sp_delete_backuphistory @v_MaxDateHistory;
GO

Bueno, espero que les haya sido de utilidad.

Encriptación de datos en SQL Server – Parte 2

Hola, continuando con esta serie de post relacionados con la encriptación de datos dentro de la base de datos SQL Server y utilizando las mismas herramientas que el motor de base de datos, en este articulo cubriremos la encriptacion en base a un certificado. Si desea se puede visitar el anterior post haciendo clic aqui.

Certificate: SQL Server permite la encriptación de la información a través de certificados digitales los cuales pueden ser adquiridos en alguna de las entidades que los expiden. Adicionalmente SQL Server tambien permite la creación de certificados “self-signed” los cuales permiten al usuario crear un certificado propio con una sentencia simple de T-SQL. Los certificados en SQL Server pueden estar encriptados por un password, o por la Database Master Key, la cual es la primera llave que se debe crear en la base de datos para que a partir de ésta se encripten los demás objetos. A través de los certificados se puede tener un poco mas de seguridad ya que para poder usar un certificado es necesario tener permiso al mismo para poder utilizarlo. A continuación hare un ejemplo completo de la creación de un certificado “self-signed” encriptado a partir de la Database Master Key además se mostrara la encriptación y desencriptación de la información con el mismo. Además se mostrara como es que se debe dar permisos a un usuario para que este pueda ver la información encriptada por el certificado.

-- limpiamos el ambiente
IF (DATABASEPROPERTY('SecureDB','version') > 0)
BEGIN
USE MASTER
ALTER DATABASE
SecureDB SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE
SecureDB
END

— Creamos la base de datos de prueba
USE MASTER
GO
CREATE DATABASE SecureDB
GO

—  Usamos nuestra base de datos de demo
USE SecureDB
GO

— Creamos una tabla cualquiera con una columna DocNum
— de tipo varbinary para que contenga la informacion encriptada
CREATE TABLE dbo.CertificateEncription
(Nombres VARCHAR(100)
,
DocNum VARBINARY(128))
GO

— Creamos una Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MiClaveSegura’
GO

— Creamos el certificado
CREATE CERTIFICATE MiPrimerCertificado WITH SUBJECT=‘DBAMemories Certificate’,
EXPIRY_DATE = ’12/31/2012′
GO

— Revisamos los certificados que tengo creados en la db
SELECT name, pvt_key_encryption_type_desc, subject, expiry_date
FROM sys.certificates
/*
name                pvt_key_encryption_type_desc subject                 expiry_date
——————- —————————- ———————– ———————–
MiPrimerCertificado ENCRYPTED_BY_MASTER_KEY      DBAMemories Certificate 2012-12-31 00:00:00.000
*/

— insertamos un valor
INSERT INTO dbo.CertificateEncription (Nombres, DocNum)
VALUES (‘Juan Perez’, ENCRYPTBYCERT(CERT_ID(‘MiPrimerCertificado’),‘12345678’))
GO

— Intentamos hacer un select convencional
SELECT Nombres, DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
———- ————–
Juan Perez 0x9E363FDB9…
*/

— Ahora seleccionamos los datos pero antes los desencriptamos con el certificado
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID(‘MiPrimerCertificado’),DocNum)) AS DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Creamos un usuario de prueba
CREATE LOGIN [Usuario1] WITH PASSWORD = N’123′
GO
CREATE USER [Usuario1] FOR LOGIN [Usuario1]
GO
GRANT SELECT ON dbo.CertificateEncription TO Usuario1
GO

— Probamos seleccionar la informacion con el certificado
EXECUTE AS USER = ‘Usuario1’
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID(‘MiPrimerCertificado’),DocNum)) AS DocNum
FROM dbo.CertificateEncription;
REVERT
/*
Nombres    DocNum
———- ————–
Juan Perez NULL
*/

— Como se puede ver la informacion sale como NULL debido a que no tenemos
— permiso para usuar el certificado como el Usuario1

— Para poder hacer uso del certificado debemos tener el permiso
— de control sobre el certificado. Ahora asignaremos ese permiso al Usuario1
GRANT CONTROL ON CERTIFICATE::[MiPrimerCertificado] TO [Usuario1]
GO

— Probamos nuevamente seleccionar la informacion con el certificado
EXECUTE AS USER = ‘Usuario1’
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID(‘MiPrimerCertificado’),DocNum)) AS DocNum
FROM dbo.CertificateEncription;
REVERT
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Como vemos ahora la informacion si aparece.

Es muy importante mencionar que 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ó. Ahora veremos un ejemplo de cómo sacarle una copia de seguridad al certificado y simularemos la pérdida del mismo y los distintos escenarios para la recuperación de la información.

USE SecureDB
GO

-- Sacaremos una copia de seguridad del certificado
BACKUP CERTIFICATE MiPrimerCertificado TO FILE = 'D:\DBAMemories\MiPrimerCertificado.cert'
WITH PRIVATE KEY ( FILE = 'D:\DBAMemories\MiPrimerCertificado.key' ,
ENCRYPTION BY PASSWORD = 'pass' );
GO

-- Ahora perderemos el certificado.
DROP CERTIFICATE MiPrimerCertificado
GO

-- Podemos confirmar que nuestro certificado no esta y no podemos desencriptar nuestra informacion
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID('MiPrimerCertificado'),DocNum)) AS DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
---------- --------------
Juan Perez NULL
*/

-- Ahora que se puede hacer, bueno se puede pensar que
-- si se crea nuevamente el certificado, todo funcionara nuevamente. Veamos
-- Creamos el certificado
CREATE CERTIFICATE MiPrimerCertificado WITH SUBJECT='DBAMemories Certificate',
EXPIRY_DATE = '12/31/2012'
GO

-- Probamos desencriptar la informacion
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID('MiPrimerCertificado'),DocNum)) AS DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
---------- --------------
Juan Perez NULL
*/

-- Que paso? Por que no funciono?
-- Debido a que el certificado que se uso no es el mismo
-- que encripto la información, a pesar de que se creo
-- el certificado con la misma sentencia

-- Volvemos a eliminar el certificado
DROP CERTIFICATE MiPrimerCertificado
GO

-- Ahora restauramos el certificado desde nuestra copia de seguridad
CREATE CERTIFICATE MiPrimerCertificado
FROM FILE = 'D:\DBAMemories\MiPrimerCertificado.cert'
WITH PRIVATE KEY (FILE = 'D:\DBAMemories\MiPrimerCertificado.key',
DECRYPTION BY PASSWORD = 'pass');
GO

-- Finalmente volvemos a consultar la informacion
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID('MiPrimerCertificado'),DocNum)) AS DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
---------- --------------
Juan Perez 12345678
*/

-- Y como podemos observar nuestra informacion volvio a estar disponible.