Encriptar una columna en MS-SQL Server con clave simétrica

Siguiendo con la racha de entradas sobre SQL, vamos a ver cómo encriptar una columna de una tabla SQL-Server usando una clave simétrica.

Bueno, lo primero, tras haber seleccionado la base de datos que queremos usar, es crear una clave maestra si no existe ya, con un password seguro (largo, con mayúsculas, minúsculas, números y símbolos), luego crear un certificado y finalmente una clave simétrica eligiendo el algoritmo a usar (en este caso AES256, robusto y seguro):

IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = 'PoNGo@1#pa55w0rd!fuEr73@para½q@no$me()lo_tAngUen'
GO

CREATE CERTIFICATE NombreDelCertificado
   WITH SUBJECT = 'Certificado Para Ejemplo';
GO

CREATE SYMMETRIC KEY SSN_Clave_Simétrica_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE NombreDelCertificado;
GO

Ok, con esto ya tenemos nuestra clave creada, con su contraseña. Un dato, si sois usuarios de Windows XP o de Windows Server 2000 no podéis usar AES, esos sistemas operativos no lo soportan, tenéis que usar DES (menos robusto y seguro, pero es lo que hay por usar sistemas sin actualizar). Ahora para introducir datos cifrados tienes que hacer lo siguiente:

-- Primero abre la clave simétrica.
OPEN SYMMETRIC KEY SSN_Clave_Simétrica_01
   DECRYPTION BY CERTIFICATE NombreDelCertificado;

--Luego introduce los datos
UPDATE BDEjemplos.Ejemplo
SET ColumnaDatosEncriptados = EncryptByKey(Key_GUID('SSN_Clave_Simétrica_01'), 'Una cadena cualquiera');
GO

En este caso usé un UPDATE pero podría haberlo hecho dentro de un INSERT. En todo caso, la función EncryptByKey() que recibe el identificador de la clave como primer parámetro y el dato a encriptar (String, Real, Float…) como segundo, es la clave de la operación.

Y para acceder a los datos cifados la cosa tampoco se torna muy compleja, es má o menos lo mismo, primero abrir y luego usar una función para desencriptar en la SELECT.En el ejemplo sacaríamos en una columna los datos encriptados y al lado el mismo dato desencriptado.

OPEN SYMMETRIC KEY SSN_Clave_Simétrica_01
   DECRYPTION BY CERTIFICATE NombreDelCertificado;
GO

SELECT ColumnaDatosEncriptados 
    AS 'Dato Encriptado',
    CONVERT(nvarchar, DecryptByKey(ColumnaDatosEncriptados)) 
    AS 'Dato Desencriptado'
    FROM BDEjemplos.Ejemplo;
GO

Existe la posibilidad de hacer esto también con un autenticador para fortalecer la seguridad. Si os interesa podéis mirarlo en la web de ayuda de Microsoft o presionarme para que publique aquí como hacerlo.

Anuncios

2 comentarios en “Encriptar una columna en MS-SQL Server con clave simétrica

  1. Pingback: Encriptar una columna en MS-SQL Server con clave simétrica « DbRunas – Noticias y Recursos sobre Bases de Datos

  2. Marcelo

    Seguí todos los pasos pero al hacer un update o insert me en el campo que estoy encriptando no guarda nada, queda en blanco y cuando le aplico el ultimo paso para ver el dato encriptado y desencriptado del lado encriptado esta vacio y del lado desencriptado quedan unas letras en algun idioma medio raro chino o algo por el estilo.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s