Cifrando datos con SQL Server 2005

  • Artículo

Por Maximiliano D. Accotto

Contenido

 1. Introducción
 2. Opciones en SQL Server 2005
 3. Passphrase
 4. Claves Asimétricas (Asymmetric Key)
 5. Claves Simétricas (Symmetric Key)
 6. Certificados
 7. Conclusión

1. Introducción

Una de las features más solicitadas por los desarrolladores y DBA es la posibilidad de que el motor de base de datos pueda cifrar datos y así no tener que disponer de herramientas de terceros o codificar los métodos de cifrado. Esta nueva versión de SQL Server incluye esta nueva característica. A lo largo de este artículo analizaremos las diferentes alternativas que nos ofrece este producto.

En muchas aplicaciones es necesario cifrar datos, pero ¿Qué buscamos, básicamente, cuando deseamos implementar una solución de éste tipo? El cifrado de datos tiene por objetivo poder mantener los datos (no deben ser todos los datos) asegurados, y no permitir la lectura directa de los mismos, por ejemplo: si existe una tabla donde se almacenan las tarjetas de crédito, la información de dicha tabla se podría considerar confidencial, con lo cual no sería nada bueno que algún usuario pueda acceder a dicha información de forma directa; el cifrado nos garantizará que por más que se tenga permisos para leer los datos, éstos no serán legibles si no se tiene la manera de descifrarlos. La determinación de qué datos se deben cifrar dependerá del negocio en sí; no recomiendo cifrar todo sino solamente lo necesario, ya que las operaciones de cifrado/descifrado perjudican el rendimiento global, con lo cual se debería poner a un lado de la balanza la seguridad, y la performance al otro.

 

2. Opciones en SQL Server 2005

SQL Server 2005 ofrece 4 alternativas para realizar el cifrado de datos:

  • Passphrase

  • Asymmetric Key

  • Symmetric Key

  • Certificate Key

El motor de base de datos tiene una infraestructura de cifrado jerárquica y administración de claves. Cada capa se encarga de cifrar a las capas inferiores utilizando una combinación de certificados, claves simétricas y asimétricas. El diagrama que se muestra en la Figura 1 ilustra lo mencionado anteriormente:

Bb972194.art300-img01-471x425(es-es,MSDN.10).gif
Figura 1. Volver al texto.

 

3. Passphrase

Este método es el más rápido y simple de todos, pero a su vez el menos seguro. Para realizar el cifrado sólo es necesario pasar una contraseña que también se utilizará para descifrar los datos. Los comandos TSQL para cifrar y descifrar los datos son EncryptByPassPhrase y DecryptByPassPhrase, respectivamente.

Sintaxis

EncryptByPassPhrase( { 'passphrase' | @passphrase } ,
                     { 'cleartext' | @cleartext }
                     [ , { add_authenticator | @add_authenticator }
                       , { authenticator | @authenticator } ]
                   )

Ejemplo
Desde el Management Studio (New Query) realizaremos los distintos ejemplos de esta nota. Para acceder al mismo debes ingresar por Start | Programs | Microsoft SQL Server 2005 | SQL server Management Studio.

CREATE DATABASE CIFRADO
GO
 
USE CIFRADO
GO
 
-- CREAMOS LA TABLA
 
IF OBJECT_ID('TARJETAS') IS NOT NULL
   DROP TABLE TARJETAS
GO
 
CREATE TABLE TARJETAS (NUMERO VARBINARY(8000), FECHA_VENCIMIENTO DATETIME)
GO

El siguiente paso es insertar un registro cifrado; para ello ejecutaremos lo siguiente:

INSERT INTO TARJETAS
VALUES (ENCRYPTBYPASSPHRASE('$$PEPE$$','123'),GETDATE())
GO

Ahora disponemos de un registro (123) que ha quedado cifrado en nuestra tabla; para comprobarlo, ejecutaremos el siguiente código:

SELECT * FROM TARJETAS
GO

El resultado que se obtiene es el siguiente:

NUMERO                                                      FECHA_VENCIMIENTO
-----------------------------------------------------------------------------
0x010000001311570EC93EE568CB153D15BEDA340BFE55C44C6C2833E1         2006-04-02

Como puedes observar, el valor retornado en NUMERO no es legible ya que el mismo ha sido cifrado previamente. El paso siguiente es leer dicho valor y hacerlo legible; para ello se utilizará DecryptByPassPhrase.

SELECT CONVERT(VARCHAR(300),
       DECRYPTBYPASSPHRASE('$$PEPE$$',NUMERO))
      ,FECHA_VENCIMIENTO
FROM TARJETAS

 

4. Claves Asimétricas (Asymmetric Key)

Las claves asimétricas son entidades que pueden protegerse a nivel de la base de datos. Básicamente, contienen una clave privada y otra publica. Por defecto, la clave privada es protegida por la Master key; de no existir esta última, es necesario una contraseña. La clave privada puede ser de 512, 1024 ó 2048 Bits. Esta metodología consume una cantidad grande de recursos pero a su vez proporciona un nivel de seguridad superior al resto de las opciones.

Para crear claves asimétricas usamos el nuevo comando TSQL CREATE ASYMMETRIC KEY; su sintaxis es la siguiente:

CREATE ASYMMETRIC KEY Asym_Key_Name 
   [ AUTHORIZATION database_principal_name ]
   {
      FROM <Asym_Key_Source>
      |
      WITH ALGORITHM = { RSA_512 | RSA_1024 | RSA_2048 }
   }
   ENCRYPTION BY PASSWORD = 'password'
 
<Asym_Key_Source>::= 
   FILE = 'path_to_strong-name_file'
   |
   EXECUTABLE FILE = 'path_to_executable_file'
   |
 
ASSEMBLY Assembly_Name

Luego, para cifrar y descifrar, utilizamos EncryptByAsymKey / DecryptByAsymKey; sus respectivas sintaxis son las siguientes:

EncryptByAsymKey (Asym_Key_ID , { 'cleartext' | @cleartext } )
 
DecryptByAsymKey ( 
                                  Asym_Key_ID , 
                 { 'ciphertext' | @ciphertext } 
                 [ , 'Asym_Key_Password' ]
 
)

Ejemplo
Lo primero que se debe realizar es la creación de una Clave Asimétrica en nuestra base de datos (en este caso, Cifrado):

USE CIFRADO
GO
CREATE ASYMMETRIC KEY ClaveAsym
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD ='$$PEPE$$'
GO

También es posible crear una clave asimétrica utilizando un archivo externo, tal como se muestra en el siguiente ejemplo:

CREATE ASYMMETRIC KEY ASYM2
FROM FILE ='C:\LLAVE.KEY'
ENCRYPTION BY PASSWORD = 'JUAN'

El siguiente paso es insertar en nuestra tabla un valor cifrado con la clave asimétrica, tal como se muestra en el siguiente paso:

DECLARE @KEYID INT
SET @KEYID = AsymKey_ID('ClaveAsym')
 
INSERT INTO TARJETAS (NUMERO)
VALUES (EncryptByAsymKey(@keyid,'4545'))

Ahora disponemos del registro cifrado; para poder comprobarlo lo que haremos es ejecutar la suguiente sentencia y ver que el resultado del campo NUMERO no sea legible.

SELECT NUMERO FROM TARJETAS

En el siguiente paso leeremos el dato de forma totalmente legible:

DECLARE @KEYID INT
SET @KEYID = AsymKey_ID('ClaveAsym')
 
 
SELECT CAST(DecryptByAsymKey(@keyid,numero,N'$$PEPE$$') AS VARCHAR(100))
FROM TARJETAS

Como habrás podido observar, tanto para el cifrado como para el descifrado es necesario pasar como parámetro el keyid; para ello se utiliza la función AsymKey_id, que retorna el Id de una clave asimétrica. En otro escenario, podríamos por ejemplo necesitar alterar la clave de cifrado de nuestra Asymmetric Key; entonces podríamos hacer algo como lo que sigue:

ALTER ASYMMETRIC KEY ClaveAsym
 
WITH PRIVATE KEY
(ENCRYPTION BY PASSWORD='$$PEPE1$$', DECRYPTION BY PASSWORD ='$$PEPE$$')

Si ahora se ejecutara la instrucción que se ha utilizado antes para descifrar, el motor de base de datos nos devolvería el siguiente mensaje de error:

Msg 15466, Level 16, State 1, Line 4
An error occurred during decryption.

Esto ocurre porque ha fallado el descifrado de la información; entonces, ¿Qué ha sucedido con nuestro registro cifrado? Pues nada; para poder hacerlo legiblemente, solo debemos cambiar la sentencia que usamos anteriormente por la que se muestra a continuación:

DECLARE @KEYID INT
SET @KEYID = AsymKey_ID('ClaveAsym')
 
 
SELECT CAST(DecryptByAsymKey(@keyid,numero,N'$$PEPE1$$') AS VARCHAR(100))
FROM TARJETAS

 

5. Claves Simétricas (Symmetric Key)

Las claves simétricas son un método eficiente para el cifrado y descifrado de datos. Este método, a diferencia de los certificados y las claves asimétricas, utiliza una key tanto para el cifrado como para el descifrado de datos. Al crear una clave simétrica, ésta debe ser cifrada utilizando una de 4 opciones: certificado, password, clave simétrica o clave asimétrica. Esta clave puede tener más de un cifrado de cada tipo.

Si utilizas password para cifrar la clave simétrica, entonces en lugar de utilizar la clave publica de la base de datos (Master Key) debes utilizar el algoritmo TRIPLE_DES. Para la creación de las claves simétricas debes utilizar el comando Create Simmetric Key y su sintaxis es la siguiente:

CREATE SYMMETRIC KEY key_name [ AUTHORIZATION owner_name ]
    WITH <key_options> [ , ... n ]
    ENCRYPTION BY <encrypting_mechanism> [ , ... n ]
 
<encrypting_mechanism> ::=
    CERTIFICATE certificate_name
    |
    PASSWORD = 'password'
    |
    SYMMETRIC KEY symmetric_key_name
    |
    ASYMMETRIC KEY asym_key_name
 
<key_options> ::=
    KEY_SOURCE = 'pass_phrase'
    |
    ALGORITHM = <algorithm>
    |
    IDENTITY_VALUE = 'identity_phrase' <algorithm> ::=
    DES | TRIPLE_DES | RC2 | RC4 | DESX
 
| AES_128 | AES_192 | AES_256

Para el cifrado y descifrado de datos utilizaremos EncryptByKey / DecryptByKey . La sintaxis de estas 2 funciones es la siguiente:

EncryptByKey(key_GUID , { 'cleartext' | @cleartext }
      [ 
         , { add_authenticator | @add_authenticator }
         , { authenticator | @authenticator }
      ]
   )
DecryptByKey( { 'ciphertext' | @ciphertext } 
      [ , add_authenticator 
         , { authenticator | @authenticator } 
      ]
   )

Ejemplo

El primer paso es crear una clave simétrica; para ello haremos lo siguiente:

USE CIFRADO
GO
 
CREATE SYMMETRIC KEY SYM_KEY
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD ='##JUAN##'

Como segundo paso, se inserta un registro cifrado con la clave simétrica; para ello se ejecutará lo siguiente:

DECLARE @KEYID UNIQUEIDENTIFIER
SET @KEYID = KEY_GUID('SYM_KEY')
 
OPEN SYMMETRIC KEY SYM_KEY
DECRYPTION BY PASSWORD='##JUAN##'
 
INSERT INTO TARJETAS(NUMERO) VALUES
(ENCRYPTBYKEY(@KEYID,'1020'))
 
CLOSE SYMMETRIC KEY SYM_KEY

Para comprobar que se ha cifrado la información, solamente se debe ejecutar el siguiente paso y se comprobará que la información no es legible:

SELECT NUMERO FROM TARJETAS

Como siguiente paso, se debe poder leer la información de forma legible; para ello se necesita realizar el descifrado del dato, para lo que utilizaremos el siguiente código:

OPEN SYMMETRIC KEY SYM_KEY
DECRYPTION BY PASSWORD ='##JUAN##'
 
SELECT CAST(DECRYPTBYKEY(NUMERO) AS VARCHAR(200))
FROM TARJETAS
 
CLOSE SYMMETRIC KEY SYM_KEY

 

6. Certificados

El uso de certificados es la cuarta opción, dentro de SQL Server 2005, que analizaremos en este artículo. Básicamente, un certificado es un elemento que puede ser protegido a nivel de la base de datos utilizando el estándar X.509, admitiendo los campos V1 de dicho estándar. Un certificado (también denominado certificado de clave publica) es una sentencia firmada en forma digital que nos permite unir la clave pública con la identidad de la persona, dispositivo o servicio que dispone la clave privada.

Una de las mayores ventajas en el uso de estos certificados es la liberación de los host de la tarea que establece un password para sujetos de forma individual. En lugar de ello, el host establece una relación de confianza con el emisor de certificados. Los certificados contienen básicamente la siguiente información:

  • Una clave pública.

  • Información que identifica al sujeto, como por ejemplo el e-mail y el nombre.

  • El período de validez; es decir la fecha hasta la que este certificado se podría utilizar.

  • La firma digital del emisor.

  • Cierta información para la identificación del emisor.

Dentro de las posibilidades de los certificados, se puede cargar desde un ensamblado o bien desde un archivo que contenga el certificado, como así también crear uno desde SQL 2005. Para la creación de estos certificados SQL dispones de la sentencia TSQL CREATE CERTIFICATE y su sintaxis es la siguiente:

CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ] 
    { FROM <existing_keys> | <generate_new_keys> }
    [ ACTIVE FOR BEGIN_DIALOG =  { ON | OFF } ]
        
<existing_keys> ::= 
    ASSEMBLY assembly_name
    | { 
        [ EXECUTABLE ] FILE = 'path_to_file'
        [ WITH PRIVATE KEY ( <private_key_options> ) ] 
      }
        
<generate_new_keys> ::= 
    [ ENCRYPTION BY PASSWORD = 'password'] 
    WITH SUBJECT = 'certificate_subject_name' 
    [ , <date_options> [ ,...n ] ]
 
<private_key_options> ::= 
    FILE = 'path_to_private_key'
    [ , DECRYPTION BY PASSWORD = 'password' ]
    [ , ENCRYPTION BY PASSWORD = 'password' ] 
 
<date_options> ::= 
START_DATE = 'mm/dd/yyyy' | EXPIRY_DATE = 'mm/dd/yyyy'

Luego, para poder cifrar y descifrar los datos se deben utilizar las sentencias EncryptByCert y DecryptByCert, y su sintaxis es la siguiente:

EncryptByCert ( certificate_ID , { 'cleartext' | @cleartext } )
DecryptByCert (
       certificate_ID ,
    { 'ciphertext' | @ciphertext }
    [ , { 'cert_password' | @cert_password } ]
              )

Ejemplo
El primer paso a realizar es la creación del certificado, para lo cual ejecutaremos la siguiente sentencia:

USE CIFRADO
GO
 
CREATE CERTIFICATE CERTIFICADO1
ENCRYPTION BY PASSWORD ='COMPLEJO'
WITH START_DATE ='20060101',
EXPIRY_DATE ='20070101',
SUBJECT = 'www.code.com'

En este ejemplo se ha creado un certificado con una password y una fecha de inicio, así como también de expiración. Si hubiéramos tenido un archivo con el certificado se podría haber hecho lo siguiente:

CREATE CERTIFICATE CERTIFICADO1
    FROM FILE = 'c:\Cert\certificado.cer'
    WITH PRIVATE KEY (FILE = 'c:\Cert\certificado.pvk',
    DECRYPTION BY PASSWORD = '6gs%53#v00');
GO

El siguiente paso es insertar un registro usando el certificado que se ha generado anteriormente, para ello se ejecutara lo siguiente:

DECLARE @CERTID INT
SET @CERTID = CERT_ID('CERTIFICADO1')
 
INSERT INTO TARJETAS (NUMERO)
VALUES (ENCRYPTBYCERT(@CERTID,'1977'))
GO

Para comprobar el cifrado del registro insertado, se ejecutará lo siguiente:

SELECT NUMERO FROM TARJETAS

Como siguiente paso, se leerá el dato insertado anteriormente pero haciéndolo legible; para ello se ejecutará lo siguiente:

DECLARE @CERTID INT
SET @CERTID = CERT_ID('CERTIFICADO1')
 
SELECT CAST(DECRYPTBYCERT(@CERTID,NUMERO,CAST('COMPLEJO' AS NVARCHAR)) AS VARCHAR(200))
FROM TARJETAS

Nota: Es muy importante realizar un backup de los certificados, como así también de las Master Key; para ello, SQL 2005 dispone de las sentencias BACKUP CERTIFICATE, BACKUP MASTER KEY.

 

7. Conclusión

Ahora, SQL 2005 dispone de métodos para el cifrado de datos; es una opción más que antes, si se deseaba implementar, se debía hacer de forma externa. De los métodos que hemos analizado en este artículo hay algunos complejos y otros simples, lo cual permite tener un abanico de opciones para cada ambiente. Se deberá estudiar cual de todos estos métodos es más aplicable a nuestro negocio.

Maximiliano D. Accotto trabaja en tecnología desde el año 1992 y se ha especializado en el área de bases de datos y desarrollo de aplicaciones. Ha utilizado Visual Basic desde la versión 3.0, así como también SQL Server desde la v6.5. Es MVP de SQL Server y es orador frecuente en eventos de Microsoft, MUG Argentina, Ineta y Culminis.