Home > Articulos & Tutoriales, Desarrollo, SQL Server > Encripcion de datos con SQL Server 2005

Encripcion de datos con SQL Server 2005


Que tal, este pequeño articulo lo escribo debido a las multiples dudas que surgen con respecto a este tema, espero que sea de gran ayuda y cualquier comentario bienvenido sea. Empecemos.

Niveles de Seguridad 

Una de las novedades de SQL Server 2005 es la infraestructura que viene junto al tema de encripcion de datos, SQL Server 2005 trabaja con una infraestructura jerárquica permitiendo un nuevo nivel de seguridad que no existía en versiones anteriores de SQL Server.

La encripcion de datos es un tema importante especialmente para cierto tipo de datos como datos financieros o datos confidenciales de clientes o usuarios. En una aplicación la encriptación y/o de encriptación puede afectar su rendimiento debido al excesivo uso de estos, así que es conveniente el buen uso de estos métodos de encripcion.

Escenario: cuando se desea que los usuarios solo puedan acceder a la información que ellos mismos han ingresado, de esta forma ellos no pueden visualizar no manipular la información de otros usuarios sin autorización previa.

Niveles de Jerarquias de Cifrado

Configurando Jerarquías de encripcion

Con SQL Server 2005 viene un sistema de encripcion jerárquico basado en una clave maestra de servicio (Service Master Key), esta clave es generada automáticamente cuando se instala SQL Server 2005. El motor de base de datos utiliza la clave maestra de servicio para encriptar los siguientes objetos.

Passwords de Servidores Vinculados (Linked Server Passwords)

Cadenas de Conexión (Connection Strings)

Credenciales de Cuentas (Account Credentials)

Todas las Claves maestras de la base de datos

A la clave maestra de servicio se le debe sacar un backup y almacenarla en un sitio seguro y fuera de línea. Esto para poder administrar más fácilmente ya sea hacer bakcups o restaurar la clave maestra de servicio en caso de que sea necesario.

El siguiente nivel en la jerarquía de encripcion es el nivel de base de datos acá se crea la clave maestra de base de datos (Database master key), esta clave es opcional y es utilizada para encriptar certificados y claves en la base de datos.

SQL Server almacena una copia de la clave maestra de base de datos en la base de datos master y a su vez es encriptada con la clave maestra de servicio. Otra copia es almacenada en la base de datos encriptada con un password.

SQL Server 2005 cifra los datos con una infraestructura de cifrado jerárquico y administración de claves. Cada capa cifra la capa inferior utilizando una combinación de certificados, claves asimétricas y claves simétricas.

Creando y Configurando llaves Simétricas, Asimétricas y Certificados

El siguiente nivel en la jerarquía de encripcion es el nivel de datos, el cual nos ofrece dos opciones entre llaves de encripcion: Simétrica y Asimétrica.

Claves simétricas: Una clave simétrica es una clave que se utiliza para el cifrado y el descifrado. El cifrado y el descifrado con una clave simétrica son más rápidos y adecuados para usarlos de forma rutinaria con datos confidenciales de una base de datos.

Una llave simétrica es el mecanismo mas rápido de encripcion para encriptar y des encriptar datos, es ideal para encriptar datos que son frecuentemente accedidos.

Creación de llave simétrica:

CREATE SYMMETRIC KEY key_name WITH ALGORITHM = AES_256 ENCRYPTION BGY PASSWORD=’password

Para encriptar y des encriptar datos, se utiliza las funciones EncryptByKey y DecryptByKey.

Claves asimétricas: Una clave asimétrica se compone de una clave privada y su correspondiente clave pública. Cada clave puede descifrar los datos que cifra la otra. El cifrado y descifrado asimétrico consumen una cantidad de recursos relativamente elevada, pero proporcionan un nivel de seguridad superior al del cifrado simétrico. Una clave asimétrica se puede utilizar para cifrar una clave simétrica para almacenar en una base de datos.

Una llave asimétrica es una combinación de una llave privada y su correspondiente llave publica. Una llave asimétrica es más fuerte que una llave simétrica.

Creación de llave asimétrica:

CREATE ASYMETRIC KEY key_name WITH ALGORITHM = RSA_2048 ENCRYPTION NY PASSWORD = ‘password

Para encriptar y des encriptar datos, se utiliza las funciones EncryptByAsmKey y DecryptByAsmKey.

Certificados: Un certificado de clave pública, normalmente denominado sólo certificado, es una instrucción firmada digitalmente que enlaza el valor de una clave pública con la identidad de la persona. Las entidades emisoras de certificados (CA) son las encargadas de emitir y firmar los certificados. La entidad que recibe un certificado de una CA es el sujeto de ese certificado. Por lo general, los certificados contienen la siguiente información.

La clave pública del sujeto.

La información que identifica al sujeto, como el nombre y la dirección de correo electrónico.

El periodo de validez. Es decir, el periodo de tiempo durante el que el certificado se considera válido.

o Un certificado sólo es válido durante el periodo de tiempo que se especifica en el mismo; todos los certificados contienen una fecha Válido desde y otra Válido hasta. Estas fechas establecen los límites del periodo de validez. Cuando el periodo de validez de un certificado ha transcurrido, es necesario que el sujeto del certificado caducado solicite uno nuevo.

Información de identificador del emisor.

La firma digital del emisor.

Configurando Certificados

Un certificado de clave pública, normalmente denominado sólo certificado, es una instrucción firmada digitalmente que enlaza el valor de una clave pública con la identidad de la persona, dispositivo o servicio que tiene la clave privada correspondiente.

Creación del certificado:

CREATE CERTIFICARE certifícate_name WITH SUBJECT = ‘certificate_subject’

Para encriptar y des encriptar datos, se utiliza las funciones EncryptByCert y DecryptByCert.

Pasos para la creación de llaves

Creación de la Master Key

Creación de los certificados

Creación de las llaves Simétricas

Pasos para la manipulación de llaves y certificados

Apertura de llaves simétricas

Inserción de datos

Cierre de llaves

Ejemplo: Encripcion de datos

En este ejemplo vamos a crear una nueva base de datos “AccountsDB” con una tabla “AccountsTable” a esta base de datos le vamos a asignar 2 usuarios específicos (Sophie y Allison), a cada uno de estos usuarios se le asignara una llave con la cual podrán insertar datos encriptados en la tabla y de igual forma consultarlos.

Creación de llaves y certificados

1. Creación de usuarios, base de datos, tabla AccountsTable y asignación de permisos

2. Creación De la Master Key

3. Creación de certificados

4. Creación de las claves simétricas que los protegerán

Manipulación de claves

1. Abrir las claves para la sesión de usuario

2. Inserción de datos encriptados

3. Cierre de claves simétricas

Aplicación

1. Creación de usuarios, base de datos, tabla AccountsTable y asignación de permisos

Vamos a crear los usuarios con sus logins (Sophie y Alison), la base de datos llamada “AccountsDB” y una tabla llamada “AccountsTable”.

USE Master

CREATE LOGIN Sophie WITH Password = ‘pass@word1’

CREATE LOGIN Alison WITH Password = ‘pass@word2’

CREATE DATABASE AccountsDB

USE AccountsDB

CREATE USER Sophie FOR LOGIN Sophie

CREATE USER Alison FOR LOGIN Alison

CREATE TABLE AccountsTable (Id Int, Name NvarChar(30),

Dept VarChar(20), Pay_Num VarBinary(60))

2. Prueba de permisos

Verificamos los permisos para uno de lo usuarios y le otorgamos permisos específicos.

EXECUTE AS USER = ‘sophie’

SELECT * FROM AccountsTable

REVERT

GRANT SELECT, INSERT ON AccountsTable TO Sophie

GRANT SELECT, INSERT ON AccountsTable TO Alison

3. Creación De la Master Key

Creamos la llave maestra se la base de datos y la encriptamos con un password. Posteriormente la consultamos la existencia de la misma.

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = ‘Passw0rd’

SELECT * FROM sys.symmetric_keys

4. Creación de certificados

A continuación creamos los certificados y les asignamos los usuarios que las van a utilizar.

CREATE CERTIFICATE AccCert1

AUTHORIZATION Sophie WITH SUBJECT = ‘AccCert1’

GO

CREATE CERTIFICATE AccCert2

AUTHORIZATION Alison WITH SUBJECT = ‘AccCert2’

GO

5. Creación de las llaves simétricas que los protegerán

Una vez creados los certificados creamos las llaves simétricas les asignamos un algoritmos de encripcion, el usuario que las utilizara y el certificado que las cobija.

CREATE SYMMETRIC KEY AccKey1

AUTHORIZATION Sophie

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE AccCert1

GO

CREATE SYMMETRIC KEY AccKey2

AUTHORIZATION Alison

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE AccCert2

GO

6. Consultar llaves simétricas y certificados

Verificamos la existencia de las llaves y los certificados.

SELECT * FROM sys.symmetric_keys

SELECT * FROM sys.certificates

Una vez creados y configurados los 2 niveles de seguridad, vamos iniciar la inserción de datos encriptados en la tabla creada. Para ello utilizaremos las funciones de encriptado de las llaves.

7. Abre las llaves para la sesión (Sophie) e inserta datos

Iniciamos con Sophie la inserción de datos a este usuario se le asigno anteriormente una llave simétrica con su respectivo certificado, primero el usuario abre la llave y utilizando la función de encriptación de la llave ingresa el campo que desea encriptar. Por ultimo al finalizar el proceso cierra la llave.

EXECUTE AS USER = ‘Sophie’

OPEN SYMMETRIC KEY AccKey1 DECRYPTION BY CERTIFICATE AccCert1

I. Inserta datos encriptados

INSERT INTO AccountsTable VALUES (1, ‘Susan’,‘HR’,

encryptByKey(Key_GUID(‘AccKey1’),‘463758’))

INSERT INTO AccountsTable VALUES (2, ‘Richard’,‘Sales’,

encryptByKey(Key_GUID(‘AccKey1’),‘463435’))

INSERT INTO AccountsTable VALUES (3, ‘Leah’,‘Purchasing’,

encryptByKey(Key_GUID(‘AccKey1’),‘488544’))

II. Cierra las claves simétricas

CLOSE ALL SYMMETRIC KEYS

REVERT

8. Abre las claves para la sesión (Alison) e inserta datos

Se realiza el mismo proceso para Allison, este usuario realiza los mismos pasos que el anterior para la inserción de los datos utilizando la llave que se le asigno.

EXECUTE AS USER = ‘Alison’

OPEN SYMMETRIC KEY AccKey2 DECRYPTION BY CERTIFICATE AccCert2

I. Inserta Datos Encriptados

INSERT INTO AccountsTable VALUES (4, ‘Tim’,‘HR’,

encryptByKey(Key_GUID(‘AccKey2’),‘463756’))

INSERT INTO AccountsTable VALUES (5, ‘David’,‘Sales’,

encryptByKey(Key_GUID(‘AccKey2’),‘499435’))

INSERT INTO AccountsTable VALUES (6, ‘Celia’,‘Purchasing’,

encryptByKey(Key_GUID(‘AccKey2’),‘488894’))

II. Cierra las claves simétricas

CLOSE ALL SYMMETRIC KEYS

REVERT

9. Revisamos el contenido de la tabla AccountsTable

Visualizamos el contenido de la tabla.

SELECT * FROM AccountsTable

 

Como pudimos ver en la tabla los datos se encuentran encriptados y solo los usuarios con las llaves correctas pueden acceder a esta información.

10. Desencripcion

Ahora cada uno de los usuarios va a observar la información que ingreso, para ello primero debe abrir su llave y utilizando el método de desencripcion correcto visualizar los datos. Por último cierra la llave.

I. Sophie

EXECUTE AS USER = ‘Sophie’

OPEN SYMMETRIC KEY AccKey1 DECRYPTION BY CERTIFICATE AccCert1

SELECT Id, name, Dept,

CONVERT(VarChar,decryptByKey(Pay_Num)) AS Pay_Num

FROM AccountsTable

CLOSE ALL SYMMETRIC KEYS

REVERT

II. Alison

EXECUTE AS USER = ‘Alison’

OPEN SYMMETRIC KEY AccKey2 DECRYPTION BY CERTIFICATE AccCert2

SELECT Id, name, Dept,

CONVERT(VarChar,decryptByKey(Pay_Num)) AS Pay_Num

FROM AccountsTable

CLOSE ALL SYMMETRIC KEYS

REVERT

11. Ejecución con otro certificado

En el caso de que un usuario desee abrir acceder a la información de otro usuario con la llave del segundo se producirá un error ya que la llave ha sido creada solo para el usuario original y no permitirá desencriptar los datos.

EXECUTE AS USER = ‘Sophie’

OPEN SYMMETRIC KEY AccKey2 DECRYPTION BY CERTIFICATE AccCert2

SELECT Id, name, Dept,

CONVERT(VarChar,decryptByKey(Pay_Num)) AS Pay_Num

FROM AccountsTable

REVERT

 

Pudimos observar los diferentes niveles de seguridad en lo que refiere al tema de encripcion en SQL Server 2005, vimos como funcionan e interactúan cada uno de los objetos relacionados en este tema.

El tema de encripcion de datos es crucial en el almacenamiento de información pero se debe tener cuidado al momento de manejar este tema ya que un mal uso puede llevar a problemas de rendimiento en el motor de bases de datos y posteriormente en las aplicaciones que hacen uso de esta tecnología.

Gerardo Ramos

Microsoft Student Partner

MCTS – SQL Server 2005

  1. May 6, 2007 at 1:24 pm

    Gerardo cómo vamos?

    Buen artículo, pero tengo algunas dudas…

    Mira, el tema de encripción de la información a nivel de la base de datos es crucial al momento de almacenar la misma. Pero de todos modos, la información sigue viajando a través de las aplicaciones sin encriptar. Finalmente, lo que estás haciendo es recibir la información sin ningún tipo de codificación, la codificas y la envías a la base de datos, y de igual manera, si requieres de esa información, la desencriptas antes de enviarla, y la envías tal cual el usuario la ingresó…

    Por ejemplo, si es una aplicación web, la información viaja por la red sin codificar, y se encripta sólamente al llegar al servidor.

    Entonces, ¿Cómo puedo prevenir este problema? Es decir, puedo de alguna manera, y sin comprometer la seguridad de mi aplicación, enviar la información a la base de datos encriptada antes de que sea almacenada???

    Alberto Rivera
    Microsoft Student Partner

  2. Gerardo
    May 6, 2007 at 2:17 pm

    Que tal alberto:

    Gracias por tu duda es clara y tiene logica, tu posicion el correcta la info viaja por la red sin codificar es vulnerable, para solucionar este problema tendriamos que meternos directamente con el diseño de seguridad y de transporte de info en la aplicacion donde se este manipulando la info.

    Claro la info podria ser emcriptada antes de ser almacenada, no es mala idea, pero eso depende mucho de los metodos de encripcion que utilices (.NET, Javascript), pero de igual forma es seguro y muy util.

    Gerardo Ramos
    Microsoft Student Partner

    • David Davila
      August 2, 2009 at 8:02 pm

      SERIA BUENA IDEA UTILIZAR SSL EN LAS COMUNICACIONES ENTRE EL SERVER Y EL BROWSER Y ASI SOLUCIONAMOS EL PROBLEMA DE QUE LOS DATOS VIAJEN PUROS?

      • August 2, 2009 at 10:57 pm

        Que tal David
        Es una excelente alternativa, Secure Socket Layer (SSL) lo que permite es cifrar los datos intercambiados entre el servidor y el cliente, permitiendo un nivel mayor de seguridad al momento de transportar los mismos.
        Utilizando SSL combinado con la encripcion por llaves de SQL Server permitiremos que nuestros datos esten seguros desde el transporte por la red hasta su almacenamiento en la base de datos. Para conocer mas de SSL los invito a consultar este link, http://msdn.microsoft.com/es-es/library/aa302411.aspx
        Gerardo Ramos – Microsoft Student Partner

  3. Baltasar Bellon
    November 8, 2007 at 8:49 am

    Hola Gerardo:

    Un amigo programador me ha recomendado tu articulo y lo veo bastante interesante. Quiero exponerte mi caso y que me digas si con el proceso que detallas podre conseguir lo que busco.

    He creado una BD en SQL SERVER EXPRESS, me muevo con bastantes registros y uso frecuentemente procedimientos almacenados por eso he elegido este sistema.

    Los ficheros resultantes MDF van a ser distribuidos en las maquinas del cliente y necesito que no sean accesibles fuera de la aplicacion. No puedo permitir que alguien adjunte los ficheros mdf a su BD y pueda navegar por ellos libremente al tener permisos de administrador.

    Espero que hayas entendido mi problema y puedas aconsejarme.

    Un saludo, muchas gracias y enhorabuena por tu trabajo.

  4. November 8, 2007 at 2:53 pm

    Que tal Baltazar

    Gracias por consultar este articulo y me alegra que le haya interesado, con este proceso de encipcion de datos no importa quien tenga acceso a los archivos fisicos MDF la informacion se va a encontrar cifrada y solo los usuarios autorizados con las llaves y credenciales adecuadas pueden acceder a la informacion, puedes estar tranquilo en colocar los MDF donde mejor le parezca ya que mientras la informacion se encuentre cifrada y las claves no sean publicadas va a tener la informacion segura.

    Si lo que busca es una mayor seguridad en los MDF le recomiendo utilizar usuarios especificos segun la situacion para acceder a la misma y que exista un solo administrador, a pesar de que el procese de encipcion solo va con los datos almacenados existe el peligro de que alguen con privilegios de administrador elimine o modifique objetos de la base de datos. Eso ya es un nivel de seguridad en cuanto a administracion y accesibildad de la misma.

    Espero haber entendido y contestado su pregunta acetadamente.

    Gerardo Ramos
    Microsoft Student Partner

  5. Baltasar Bellon
    November 9, 2007 at 5:44 am

    Hola Gerardo:

    Muchas gracias por tu respuesta, me quedo mas tranquilo pero me surjen las siguientes dudas:

    Imaginemos que un usuario administrador de SQL SERVER EXPRESS adjunta el fichero MDF en cualquier otra maquina. Podra ver los usuarios, los certificados y las keys de la BD. Asi que le bastaria con ejecutar el siguiente codigo para poder acceder a los datos:

    execute as user = ‘sophie’
    open symmetric key acckey1 decryption by certificate acccert1

    select convert(varchar,decryptbykey(pay_num)) as pay_num from accountstable

    En este codigo no enviamos clave ni nada y al ser administrador podria entrar como cualquier usuario y al final acceder a los datos ¿es asi?.
    Nuestra intencion es acceder al MDF desde una aplicacion .Net, por lo que veo nos bastaria con iniciar la sesion de usuario y luego usar el convert… para ver los datos, ¿seria de esta forma?

    Muchas gracias.

  6. November 13, 2007 at 9:30 am

    Que tal Baltazar:

    Efectivamente como administrador puedes acceder a toda la info a partir de los certificados y las llaves, para acceder desde .net lo recomendable es crear un usuario con permisos restringidos e ingresar la info a partir de una llave y un certificado creado solo para ese usuario, de esta forma se asegura que solo el administrador y el ususrio q se loguea al sistema puedan ver la info que se esta almacenando.

    La linea “execute as sophie” vendria a ser reemplazada por el login (Usuario y password) de un usuario autorizado a cuya informacion desee ser cifrada

    Espero a ver respondido tu duda

    Si tienes otra hazmela saber.

    Saludos
    Gerardo Ramos – Microsoft Student Partner

  7. Luis Vela
    May 13, 2008 at 3:43 pm

    Gerardo:

    He leido tu articulo y esta muy interesante, tengo una consulta mira tengo una BD en SQL 2005 que se alimenta atraves de una insercion de archivos de un sistema en VFP, dicha informaicon luego es visulaizada en otro aplicativo en .net para imprimirla. La consulta como puedo encriptra la informacion de tal forma que un usuario cualquiera que pueda ingresar a la BD no puede leer la dat y que al momento de levantar mi aplicativo en .net puede decriptar la data y leerla de forma normal y proceder a imprimirla.

    Gracias de antemano por tu ayuda

    Saludos

    Luis Vela

  8. Javier Guillermo Herrera
    May 21, 2008 at 12:41 pm

    gRACIAS, SON BASES FUERTES PARA LOS NIVELS DE SEGURDIAD…

  9. Luis Vela
    May 23, 2008 at 6:06 pm

    Gerardo:

    Una consulta mira jhe encriptado una informacion en una tabla la cual esta parametrizada con campo tipo CHAr al momento de encriptar todo va bien, es al momento de decriptar que el campo me envia nulo apesra de que la data esta encriptada y se puede visualizar mediante un select a la tabla.
    Para mayor ayuda te envio las opciones de encrip[cion y decripcion asi como la estructura de la tabla

    DECLARE @CERTID INT
    SET @CERTID = CERT_ID(‘INRENA’)
    insert into cGuiaForestal11 (codguia,
    tipemi,
    codattfs,
    preguia,
    numPOA,
    zafra,
    codrecurso,
    numrucemi,
    numrucext)
    values (ENCRYPTBYCERT(@CERTID,’025199475′),
    ‘2’,
    ENCRYPTBYCERT(@CERTID,’PUC’),
    ENCRYPTBYCERT(@CERTID,”),
    ‘3’,
    ENCRYPTBYCERT(@CERTID,’2007′),
    ENCRYPTBYCERT(@CERTID,’1′),
    ENCRYPTBYCERT(@CERTID,”),
    ENCRYPTBYCERT(@CERTID,’20393089831′))

    DECLARE @CERTID INT
    SET @CERTID = CERT_ID(‘INRENA’)

    SELECT CAST(DECRYPTBYCERT(@CERTID,codguia,CAST(‘COMPLEJO’ AS NVARCHAR)) AS VARCHAR(max))
    FROM cGuiaForestal11

    LA estructura de la tabla
    create table cGuiaForestal
    codguia char(9) Unchecked
    tipemi int Unchecked
    codattfs char(25) Unchecked
    preguia char(9) Checked
    numPOA int Checked
    zafra char(4) Checked
    codrecurso char(3) Checked
    numrucemi char(11) Checked
    numrucext char(11) Checked

    Gracias por la ayuda anticipadamente

  10. Jose Edgar Coronel Pinto
    July 8, 2008 at 3:36 pm

    Felicidades por el articulo, me parce muy interesante. Mi pregunta es como debo hacer para poder encriptar toda una base de datos completa con datos ya llenados dentro de la misma(20 millones de datos), cual es el algoritmo recomendable para que la encriptacion y desencriptacion sea rapida y segura.

    Muchas gracias espero su respuesta…..

  11. javier
    April 14, 2009 at 12:24 pm

    Lo encnuentro muy interesante este ejemplo pero en problema con el que me econtrado es que cuando inserto datos con el usuario X a la hora de hacer un select con el usuario Y no lo puede ver (el campo de la clave sale nulo).
    Porfavor ayudenme

  12. Pedro
    January 19, 2010 at 5:45 pm

    Esta excelente la informacion…muchas gracias

  13. aleja
    October 19, 2010 at 10:09 am

    Qué bien explicado !!!
    Estoy empezando con esto de los certificados y la encriptación y me ha sido de infinita ayuda, muuuchas gracias🙂

  1. May 1, 2007 at 8:16 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: