Requerimiento: Queremos encriptar la información sensible de una tabla en nuestra base de datos para que no sea legible a los usuarios que tienen acceso a ella.

Solución: La encriptación a nivel de columna es una de las funcionalidades que nos permite asegurar nuestros datos. De esta manera, si un usuario llega a obtener acceso a la tabla, esta es inútil sin una correspondiente clave de desencriptación o password. Esta característica está disponible desde SQL Server 2008 en su versión Standard.

En este ejemplo, encriptaremos la data de la columna ‘nrotarjeta’ de la tabla ‘dbo.clientes’ . Echamos un vistazo a la tabla clientes:

Primero, creamos una Database Master Key, la cual protegerá el certificado que crearemos en el siguiente paso.

--Script #1
USE BitacoraDBA;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Bitac@2020';

Segundo, creamos un certificado, el cual protegerá la clave simétrica que crearemos en el paso 3.

--Script #2
USE BitacoraDBA;
GO
CREATE CERTIFICATE Certificado_prueba WITH SUBJECT = 'Encripta una columna';
GO

Tercero, creamos una clave simétrica usando el certificado creado en el paso anterior.

--Script #3
CREATE SYMMETRIC KEY LlaveSimetrica_Prueba 
WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificado_prueba;

Cuarto, añadimos una nueva columna a la tabla, que almacenará la data ya encriptada. Esta nueva columna debe ser de tipo VARBINARY.

--Script #4
ALTER TABLE dbo.clientes ADD nrotarjeta_encriptado varbinary(MAX)

Por último, encriptamos la data usando la clave simétrica y el comando ENCRYPTBYKEY:

--Script #5
OPEN SYMMETRIC KEY LlaveSimetrica_prueba 
DECRYPTION BY CERTIFICATE Certificado_prueba;

--Almacenamos la data encriptada en la nueva columna 
UPDATE dbo.clientes
SET nrotarjeta_encriptado = ENCRYPTBYKEY(Key_GUID('LlaveSimetrica_prueba'), nrotarjeta)
FROM dbo.clientes;
GO

--Cerramos la llave simétrica después de haberla usado
CLOSE SYMMETRIC KEY LlaveSimetrica_prueba;
GO

Si echamos un vistazo a la tabla dbo.clientes veremos la data encriptada en la nueva columna.

Una vez comprobemos el cambio, podemos proceder a eliminar la columna con la data no encriptada.

Si tienes alguna duda, comentarios o sugerencias, me puedes enviar un correo a bitácoradeundba@gmail.com.