Encriptación de datos en MSSQL Server 2016

El escenario es el siguiente: Se requiere encriptar los valores de una columna de una tabla, la base de datos que usaré es MSSQL Server 2016 con SP1. Puedes descargar desde este link la versión 2016 express advance services de 64 bits:

https://download.microsoft.com/download/9/0/7/907AD35F-9F9C-43A5-9789-52470555DB90/ENU/SQLEXPRADV_x64_ENU.exe

Este ejercicio lo realicé basado en este link en inglés:

https://social.technet.microsoft.com/wiki/contents/articles/37513.sql-server-2016-implement-column-level-encryptiondecryption.aspx

Puedes ir directamente a él, pero si están interesados en un ejemplo en español, entonces sigan leyendo.

Bien, en este ejercicio encriptaremos una columna de una tabla, para realizar esto se ejecutarán los siguiente pasos:

• Creación de una tabla mediante script SQL e inserción de un registro
• Creación de Master Key encription en MSSQL Server 2016 con SP1.
• Creación de SelfSigned Certificate.
• Creación de llave simétrica para encripción y desencripción de una columna de la tabla.
• Agregar columna varbinary para alojar valores encriptados.
• Encriptación de columna.
• Desencripción de columna.

Creación de una tabla mediante script SQL e inserción de un registro

Lo siguiente que haremos será abrir el SQL Management Studio:

y abrimos una ventana tipo query:

Luego mediante el siguiente script creamos la tabla TarjetaCredito.

Aqui abajo le dejo el script en texto:

CREATE TABLE [dbo].[TarjetasCredito](
	[id] [int] NOT NULL,
	[numero_tarjeta] [varchar](50) NOT NULL,
	[nombre] [varchar](50) NOT NULL,
	[apellido] [varchar](50) NOT NULL,
	[cvc] [varchar](3) NOT NULL,
	[EncryptedCVC] [varbinary](max) NULL,
 CONSTRAINT [PK_TarjetasCredito] PRIMARY KEY CLUSTERED 
 (
	[id] ASC
 )
)

Una vez creada la tabla debe aparecer en el listado de la izquierda:

Vamos insertar un registro para fines del ejercicio:

INSERT INTO [dbo].[TarjetasCredito]
           ([id]
           ,[numero_tarjeta]
           ,[nombre]
           ,[apellido]
           ,[cvc])
     VALUES
           ('3',
           '4444 4444 4444 4444',
           'Daddy',
           'Yankee',
           '777')

Consultamos la tabla para verificar que el registro esté insertado correctamente:

SELECT * FROM [pubs].[dbo].[TarjetasCredito]

Tenia 2 registros ingresados previamente, el nuevo registro lo he insertado en la 3ra posición.

Creación de Master Key encription en MSSQL Server 2016 con SP1

Antes de pasar a la encripción de la columna se debe realizar la encripción del Master Key. Esto lo realizamos con el siguiente script:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

Debemos poner una contraseña, en mi caso he puesto Pa$$w0rd.

Creación de SelfSigned Certificate

Lo siguiente es crear el SelfSigned Certificate, lo hacemo con la siguiente sentencia:

CREATE CERTIFICATE SelfSignedCertificate WITH SUBJECT = 'CVC Encryption'

Debemos poner un asunto, puede ser cualquier otro texto, yo he puesto CVC Encryption.

Creación de llave simétrica para encripción y desencripción de una columna de la tabla

Para crear la llave simétrica usaremos la siguiente sentencia:

CREATE SYMMETRIC KEY SQLSymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE SelfSignedCertificate

Esta sentencia creará una llave simétrica utilizando un algortirmo de 256 bits y se encriptará haciendo uso del certificado SelfSigned que creamos en el paso anterior.

Agregar columna varbinary para alojar valores encriptados

Para alojar los valores encriptados la tabla debe contener una columna de tipo varbinary. Hacemos esto con la siguiente sentencia:

ALTER TABLE UserDetails 
ADD EncryptedCVC varbinary(MAX )NULL 

Las columnas de la tabla quedan de la siguiente forma:

Encriptación de columna

Para poder encriptar los valores de una columna se debe abrir primero la llave simétrica, esto se realiza con la siguiente sentencia:

OPEN SYMMETRIC KEY SQLSymmetricKey DECRYPTION BY CERTIFICATE SelfSignedCertificate

Esta sentencia debe realizarse una sola vez para la actual conexión a la base de datos, si cerramos la conexión debemos abrir nuevamente la llave. Si no la ejecutamos en intenamos realizar la encripción simplemente vamos a obtener valores en null.

Finalmente para encriptar los valores de la columna cvc de nuestra trabla ejecutamos el siguiente script:

UPDATE TarjetasCredito 
SET EncryptedCVC = EncryptByKey(Key_GUID('SQLSymmetricKey'), cvc)

Los valores originales de la columna cvc serán encriptados y almacenados en la columna EncryptedCVC, recordemos que esta última columna es de tipo varbinary.

Si consultamos nuestra tabla veremos los valores encriptados:

Desencripción de columna

Bien, idealmente no tendriamos que dejar en la tabla la columna cvc porque expone información sensible. Pueden borrar la columna si lo consideran necesario.

Para ver los valores en texto plano nuevamente, es decir desencriptados, ejecutamos la siguiente sentencia:

SELECT *,
CONVERT(varchar, DecryptByKey(EncryptedCVC)) AS 'DecryptedCVC' 
FROM TarjetasCredito

Esto nos muestra el siguiente resultado:

Como apreciamos la última columna: DecryptedCVC, nos muestra exactamente los mismos valores que la columna: cvc.

Finalmente debemos ejecutar el cierre de la llave simétrica que abrimos previamente:

CLOSE SYMMETRIC KEY SQLSymmetricKey

Bien, estos han sido los pasos para realizar una encriptación/desencripción de los valores de una columna en MSSQL Server 2016, espero les haya sido de útilidad.

Éxitos!.