Como realizar encriptación de los datos en SQL Server : SQL Server Encriptando datos

JuanCastillo 22 diciembre, 201722 diciembre, 2017 0 comentarios

Cómo utilizar el cifrado de SQL Server con claves simétricas

En esta oportunidad hablaremos de de como pueden encriptar datos de forma rápida y segura en SQL Server 2005 o superior, mediante el uso de la funcionalidad nativa de claves simétricas. Los soportes de cifrado de clave simétrica de los algoritmos de cifrado más comunes son Des, Triple Des, RC4 de 128 bits, AES de 128 bits y AES de 256 bits.

Configuran tu base de datos para encriptando datos

Para crear una clave simétrica, primero debemos configurar nuestra base de datos con una clave maestra y un certificado, que actúen como protectores de nuestro almacén de claves simétricas.

Crear una clave maestra de base de datos CREAR ENCRIPTACIÓN DE TECLA PRINCIPAL POR CONTRASEÑA = ‘myStrongPassword’

CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'A label for this certificate'

1 2	CREATE CERTIFICATE MyCertificateName WITH SUBJECT = 'A label for this certificate'

Crea tu clave simétrica

Ahora que hemos configurado nuestra base de datos, podemos agregar la clave simétrica a nuestro certificado. Para garantizar que podamos replicar la clave en otro servidor, o reconstruir la clave si está dañada, debe tener muy en cuenta los parámetros KEY_SOURCE e IDENTITY_VALUE, ya que estos son los que se utilizan para crear la clave.

Create a Symmetric Key

CREATE SYMMETRIC KEY MySymmetricKeyName WITH
IDENTITY_VALUE = 'a fairly secure name',
ALGORITHM = AES_256,
KEY_SOURCE = 'a very secure strong password or phrase'
ENCRYPTION BY CERTIFICATE MyCertificateName;

CREATE SYMMETRIC KEY MySymmetricKeyName WITH

IDENTITY_VALUE = 'a fairly secure name',

ALGORITHM = AES_256,

KEY_SOURCE = 'a very secure strong password or phrase'

ENCRYPTION BY CERTIFICATE MyCertificateName;

El parámetro IDENTITY_VALUE se usa para generar el guid para la clave y KEY_SOURCE se usa para generar la clave real. Esto le permite ejecutar el código anterior en cualquier servidor tantas veces como quiera, con los mismos valores, para generar exactamente la misma clave.

Elegí AES_256, pero puede elegir entre los siguientes algoritmos de cifrado: DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 y AES_256.

Cifrado y descifrado de datos

Finalmente, podemos cifrar y descifrar datos utilizando la clave que acabamos de crear utilizando los siguientes fragmentos de código.

Abre la llave

Antes de que pueda comenzar a cifrar o descifrar datos, primero debe inicializar la clave. Esto se hace con la siguiente pieza de código.

OPEN SYMMETRIC KEY MySymmetricKeyName
DECRYPTION BY CERTIFICATE MyCertificateName

1 2	OPEN SYMMETRIC KEY MySymmetricKeyName DECRYPTION BY CERTIFICATE MyCertificateName

Cifrado de datos

Puede encriptar datos usando la función EncryptByKey, así:

DECLARE @Result varbinary(256) 
SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)

1 2	DECLARE @Result varbinary(256) SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)

Tenga en cuenta que el resultado del cifrado es de tipo varbinary (256) y si desea almacenar el valor en una columna para usar este tipo.

Descifrar datos

Puede descifrar datos utilizando la función DecryptByKey, así:

DECLARE @Result varchar(max)
SET @Result = DecryptByKey(@ValueToDecrypt)

1 2	DECLARE @Result varchar(max) SET @Result = DecryptByKey(@ValueToDecrypt)

Asegúrese de descifrar el mismo tipo que cifró en primer lugar. En mi ejemplo cifré un varchar (max), así que también descifré a varchar (max).

Usar claves simétricas en una función

Debido a que las claves simétricas usan sesiones basadas en el tiempo, no puede abrirlas dentro de una función, sin embargo, puede evitar esto abriendo primero con un Procedimiento almacenado y luego llamando a la función. Aquí hay un ejemplo de la configuración que tengo en marcha.

El procedimiento almacenado OpenKeys

CREATE PROCEDURE OpenKeys
AS
BEGIN
    SET NOCCOUNT ON;

    BEGIN TRY
        OPEN SYMMETRIC KEY MySymmetricKeyName
        DECRYPTION BY CERTIFICATE MyCertificateName
    END TRY
    BEGIN CATCH
        -- Handle non-existant key here
    END CATCH
END

CREATE PROCEDURE OpenKeys

BEGIN

SET NOCCOUNT ON;

BEGIN TRY

OPEN SYMMETRIC KEY MySymmetricKeyName

DECRYPTION BY CERTIFICATE MyCertificateName

END TRY

BEGIN CATCH

-- Handle non-existant key here

END CATCH

END

La funciono para realizar el encriptado

CREATE FUNCTION Encrypt
(
    @ValueToEncrypt varchar(max)
)
RETURNS varbinary(256)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varbinary(256)

    SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)

    -- Return the result of the function
    RETURN @Result
END

CREATE FUNCTION Encrypt

(

@ValueToEncrypt varchar(max)

)

RETURNS varbinary(256)

BEGIN

-- Declare the return variable here

DECLARE @Result varbinary(256)

SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)

-- Return the result of the function

RETURN @Result

END

La funcion para realizar el desencriptado

CREATE FUNCTION Decrypt
(
    @ValueToDecrypt varbinary(256)
)
RETURNS varchar(max)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(max)

    SET @Result = DecryptByKey(@ValueToDecrypt)

    -- Return the result of the function
    RETURN @Result
END

CREATE FUNCTION Decrypt

(

@ValueToDecrypt varbinary(256)

)

RETURNS varchar(max)

BEGIN

-- Declare the return variable here

DECLARE @Result varchar(max)

SET @Result = DecryptByKey(@ValueToDecrypt)

-- Return the result of the function

RETURN @Result

END

Ahora podemos realizar una prueba de como enpriptar y desencriptar los datos

EXEC OpenKeys

-- Encriptando 
SELECT Encrypt(myColumn) FROM myTable

-- Desencriptando
SELECT Decrypt(myColumn) FROM myTable

EXEC OpenKeys

-- Encriptando

SELECT Encrypt(myColumn) FROM myTable

-- Desencriptando

SELECT Decrypt(myColumn) FROM myTable

Cómo utilizar el cifrado de SQL Server con claves simétricas

Configuran tu base de datos para encriptando datos

Crea tu clave simétrica

Cifrado y descifrado de datos

Abre la llave

Cifrado de datos

Descifrar datos

Usar claves simétricas en una función

El procedimiento almacenado OpenKeys

La funciono para realizar el encriptado

La funcion para realizar el desencriptado

También te puede gustar

Explicación de como funcionan la sentencia force orden y utilidad : Sentencia OPTION FORCE ORDER en Transact-SQL

Como utilizar las funciones LAG LEAD en nuestros códigos SQL: Funciones LAG y LEAD en Transact-SQL SQL Server

Cómo Crear una Suma con Acumulado en Otra Columna Usando SUM() en SQL Parte II

Deja una respuesta Cancelar la respuesta