Encriptación de Datos en SQL SERVER

En los sistemas de información muchas veces se maneja data sensible que se requiere proteger para evitar que caigan en manos maliciosas, por ejemplo: nombre de un cliente, número de documento de identidad, número de cuenta de ahorros, número de tarjetas de crédito, dirección de correo, número de teléfono, etc. Para esto es conveniente tener la información encriptada dentro de la base de datos. SQL Server, a partir de la versión 2005, nos ofrece una variedad de formas para encriptar la información en la base de datos, entre las cuales tenemos:

Passphrase
Certificate
Symmetric Key
Asymmetric Key

Passphrase:
Esta es la forma más simple y básica de encriptación de datos que se puede tener en SQL Server. Lo único que se necesita para encriptar los datos es una frase o contraseña “segura”.
Ejemplo:

--
--
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].[PROVEEDOR](
	[IDPROVEEDOR] [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
PRIMARY KEY CLUSTERED 
(
	[IDPROVEEDOR] ASC
)
) ON [PRIMARY]
GO 
 
 
 
-- Insertar un valor
INSERT INTO dbo.PROVEEDOR (FECING, USUING, NOMBRE)
VALUES (getdate(), 'IRE001', ENCRYPTBYPASSPHRASE('ClaveSecretaIRE','ANDRES LARA FABIAN'))
GO
-- Select convencional
SELECT IDPROVEEDOR, NOMBRE, FECING
FROM dbo.PROVEEDOR
/*
IDPROVEEDOR		NOMBRE				FECING
1			0x01000000DC8...	2020-10-08 23:36:54.097
*/
 
-- Select con una frase incorrecta
SELECT IDPROVEEDOR, CONVERT(VARCHAR(100), DECRYPTBYPASSPHRASE('ClaveSecreta',NOMBRE)) as 'NOMBRE',
FECING
FROM dbo.PROVEEDOR
/*
IDPROVEEDOR	NOMBRE	FECING
1		NULL	2020-10-08 23:14:56.043
*/
 
-- Select con la frase correcta
SELECT IDPROVEEDOR, CONVERT(VARCHAR(100), DECRYPTBYPASSPHRASE('ClaveSecretaIRE',NOMBRE)) as 'NOMBRE',
FECING
FROM dbo.PROVEEDOR
/*
IDPROVEEDOR	NOMBRE				FECING
1		ANDRES LARA FABIAN		2020-10-08 23:14:56.043
*/

Certificate: Otra forma de encriptación de datos dentro de la base de datos SQL Server a través de certificados digitales los cuales pueden ser adquiridos en alguna de las entidades que los expiden. SQL Server también 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 más de seguridad ya que para poder usar un certificado es necesario tener permiso al mismo para poder utilizarlo.

Ejemplo completo de la creación de un certificado “self-signed” encriptado a partir de la Database Master Key y se mostrará la encriptación y desencriptación de la información con dicho certificado.

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 que 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
*/
 
-- Select desencriptando los datos con el certificado incorrecto 
SELECT IDCLIENTE, CONVERT(VARCHAR(100), DECRYPTBYCERT(CERT_ID('FinancieraCertificadoOTRO'),NOMBRE)) as 'NOMBRE',
CONVERT(VARCHAR(70), DECRYPTBYCERT(CERT_ID('FinancieraCertificado'),NOMBRECORTO)) as 'NOMBRECORTO',
FECING
FROM dbo.CLIENTE
/*
IDCLIENTE	NOMBRE		NOMBRECORTO		FECING
1		NULL		CARLOS FLORES		2020-10-08 23:14:56.043
*/

También se mostrará como es que se debe dar permisos a un usuario para que este pueda ver la información encriptada por el certificado.
Creamos un Usuario de pruebas para seleccionar la información de la tabla desencriptándola con el certificado pero ejecutándolo domo el Usuario creado, en este caso la información sale como NULL debido a que no se le ha dado permisos al usuario creado para usar el certificado, por ello para hacer uso del certificado se debe tener el permiso de CONTROL sobre el certificado (GRANT CONTROL ON CERTIFICATE :: [FinancieraCertificado] TO [USUARIO_FINANCIERA]

--
-- Crear un usuario de prueba
CREATE LOGIN [UsuarioFinanciera] WITH PASSWORD = N'Financiera2020'
GO
CREATE USER [UsuarioFinanciera] FOR LOGIN [UsuarioFinanciera]
GO
GRANT SELECT ON dbo.CLIENTE TO [UsuarioFinanciera]
GO
 
-- Seleccionar la informacion con el certificado
EXECUTE AS USER = 'UsuarioFinanciera'
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;
REVERT
/*
IDCLIENTE	NOMBRE	NOMBRECORTO		FECING
1	NULL	NULL			2020-10-08 23:47:39.520
*/
 
-- La informacion sale como NULL debido a que no se tiene 
-- permiso para usuar el certificado como el UsuarioFinanciera
 
-- Para hacer uso del certificado se debe tener el permiso
-- de control sobre el certificado. Ahora asignaremos ese permiso al UsuarioFinanciera
GRANT CONTROL ON CERTIFICATE::[FinancieraCertificado] TO [UsuarioFinanciera]
GO
 
-- Seleccionar de nuevo la informacion con el certificado
EXECUTE AS USER = 'UsuarioFinanciera'
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;
REVERT
/*
IDCLIENTE	NOMBRE			NOMBRECORTO	FECING
1		CARLOS FLORES RIOS	CARLOS FLORES	2020-10-08 23:47:39.520
*/

Para conocer como crear copia de seguridad (BACKUP) de un certificado en SQL SERVER Y restaurarlo, visitar la siguiente entrada de mi blog:
http://infotacticassoluciones.blogspot.com/2020/10/copia-de-seguridad-backup-de.html

Simmetric Key (Clave Simétrica):
El principio de la encriptación a partir de llaves simétricas es que para encriptar y desencriptar la información se necesita la misma llave. Este tipo de encriptación es la más común. 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, un ejemplo simple de encriptación de datos con clave simétrica.

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 COLABORADOR con las columnas Nombres y Apellidos
-- de tipo varbinary para que contenga la informacion encriptada
CREATE TABLE [dbo].[COLABORADOR](
	[IDCOLABORADOR] [int] IDENTITY(1,1) NOT NULL,
	[MATRICULACOLABORADOR] [varchar](6) NOT NULL,
	[FECING] [datetime] NULL,
	[USUING] [varchar](6) NULL,	
	[FECACT] [datetime] NULL,
	[USUACT] [varchar](6) NULL,
	[NOMBRES] [varbinary](max) NOT NULL,
	[APELLIDOS] [varbinary](max) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[IDCOLABORADOR] 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 que 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
*/

Para crear la llave simétrica

-- Creamos la llave simetrica
CREATE SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC
WITH
KEY_SOURCE = 'ClaveSimetrica2020',
IDENTITY_VALUE = 'MiValorIdentidad',
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE FinancieraCertificado;
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##	AES_256	2020-10-08 23:45:00.460
FINANCIERA_KEY_SYMMETRIC	AES_256	2020-10-09 16:40:52.293
 
*/

Ahora procederemos a ingresar valores a nuestra tabla. Para esto debemos abrir la llave simetrica, insertar un valor y Una vez que se termino de encriptar los datos, se cierra la llave simetrica.

-- Abrir la llave simétrica
OPEN SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC DECRYPTION BY CERTIFICATE FinancieraCertificado;
 
-- insertamos un valor
INSERT INTO dbo.COLABORADOR (MATRICULACOLABORADOR, FECING, USUING, NOMBRES, APELLIDOS)
VALUES ('CAR001', getdate(), 'IRE001', ENCRYPTBYKEY(KEY_GUID('FINANCIERA_KEY_SYMMETRIC'),'CESAR ANTONIO'), ENCRYPTBYKEY(KEY_GUID('FINANCIERA_KEY_SYMMETRIC'),'AVILA ROBLES'))
GO
 
-- Una vez que se termino de encriptar los datos, se cierra la llave simetrica
CLOSE SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC
 
-- Intentamos hacer un select convencional
SELECT MATRICULACOLABORADOR, NOMBRES, APELLIDOS
FROM dbo.COLABORADOR
/*
MATRICULACOLABORADOR	NOMBRES			APELLIDOS
CAR001			0x0034CE8C5...		0x0034CE8C5716...
*/

Para poder ver los datos debemos desencriptarlos con la llave simetrica, y para hacerlo debemos primero abrirla.

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

Para probar la seguridad de la llave simetrica, creamos un usuario de prueba: UsuarioCAFinanciera y probamos seleccionar la informacion con la llave simetrica.

 
CREATE LOGIN [UsuarioCAFinanciera] WITH PASSWORD = N'Protect2020'
GO
CREATE USER [UsuarioCAFinanciera] FOR LOGIN [UsuarioCAFinanciera]
GO
GRANT SELECT ON dbo.COLABORADOR TO UsuarioCAFinanciera
GO
 
-- Probamos seleccionar la informacion con la llave simetrica
EXECUTE AS USER = 'UsuarioCAFinanciera'
OPEN SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC DECRYPTION BY CERTIFICATE FinancieraCertificado;
SELECT MATRICULACOLABORADOR, CONVERT(VARCHAR(100),DECRYPTBYKEY(NOMBRES)) AS NOMBRES,
CONVERT(VARCHAR(100),DECRYPTBYKEY(APELLIDOS)) AS APELLIDOS
FROM dbo.COLABORADOR
REVERT

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 UsuarioCAFinanciera
GRANT CONTROL ON CERTIFICATE::[FinancieraCertificado] TO [UsuarioCAFinanciera]
GO
GRANT CONTROL ON SYMMETRIC KEY::FINANCIERA_KEY_SYMMETRIC TO UsuarioCAFinanciera
GO
 
-- Probamos seleccionar la informacion con la llave simetrica
EXECUTE AS USER = 'UsuarioCAFinanciera'
OPEN SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC DECRYPTION BY CERTIFICATE FinancieraCertificado;
SELECT MATRICULACOLABORADOR, CONVERT(VARCHAR(100),DECRYPTBYKEY(NOMBRES)) AS NOMBRES,
CONVERT(VARCHAR(100),DECRYPTBYKEY(APELLIDOS)) AS APELLIDOS
FROM dbo.COLABORADOR
CLOSE SYMMETRIC KEY FINANCIERA_KEY_SYMMETRIC
REVERT
/*
MATRICULACOLABORADOR	NOMBRES	APELLIDOS
CAR001	CESAR ANTONIO	AVILA ROBLES
*/