Ejemplo de encriptación de campos de una base de datos de SQL Server 2008 con Clave

Todos las empresas quieren proteger sus datos de terceros, e incluso de sus propios trabajadores. Lo que muchos "jefes" no tienen tan claro es que siempre hay dos niveles de protección... uno físico y uno lógico. De nada sirve poner un sistema de seguridad por software de ultima generación y "super seguro" si cualquiera tiene acceso al disco físico donde se almacenan los datos. Como respuesta a la pregunta, si alguien se lleva mi base de datos, ¿puede acceder a los datos?. La respuesta siempre es SI. Lo único que podemos hacer es ponerlo más difícil.

Por tanto, desde mi punto de vista, lo más importante es tener un sitio seguro donde almacenar los datos y que físicamente  tan sólo personal autorizado puede acceder a ellos. Además hay que complementar esto con una buena seguridad por software que nos evite el máximo número de ataques posible. ¿y si todo esto falla? la solución pasa por encriptar parte o la totalidad de nuestra base de datos, que si bien no es infalible, nuevamente complicamos un poco mas las cosas a "los malos".

Hay varias formas de encriptar una base de datos de SQL Server 2008, una de las más sencillas es la siguiente. Encriptar los campos de las tablas que no queramos que nadie conozca con una clave. Está claro que si perdemos la clave... nos resultará un poco complicado obtener nosotros mismos esos datos, pero como siempre, por "fuerza bruta" se pueden obtener. Por esto decimos, que lo único que conseguiremos es complicar un poco mas las cosas a "los malos". En próximas entradas hablaré de otros métodos de encriptación de las bases de datos de SQL Server 2008. Es importante saber también  que este método de encriptación esta disponible en la versión Express.

Un ejemplo: 

01 create database DBConClave 
02 
03 GO 
04 
05 use DBConClave 
06 
07 GO 
08 
09 create table Clientes 
10 ( 
11 id integer identity primary key, 
12 nombre varchar(100), 
13 apellidos varchar(200), 
14 cif varchar(20), 
15 ccc VARBINARY(8000) -- este es el campo que vamos a cifrar. 
16 ) 
17 
18 GO 
19 
20 -- insertamos un registro con clave 
21 -- es la menor protección pero también la que requiere menos recursos 
22 INSERT INTO Clientes (nombre, apellidos, cif, ccc) 
23 VALUES ('Sandra', 'Matos', '1231231',ENCRYPTBYPASSPHRASE('mipassword','123132131321')) --mispassword es la clave de cifrado 
24 
25 GO 
26 
27 --si hacemos un select normal no podemos obtener el ccc 
28 SELECT * FROM CLientes 
29 
30 
31 --para poder obtener el ccc del cliente deberíamos pasarle también la clave. 
32 SELECT nombre, apellidos, cif, CONVERT(VARCHAR(300), 
33 DECRYPTBYPASSPHRASE('mispassword',ccc)) as ccc 
34 FROM Clientes

Exprés: Crear Base de Datos SQL Server 2008 en cualquier directorio

En SQL Server 2008 las bases de datos se crean por defecto en el directorio que viene configurado, si bien, es posible cambiarlo. Pero, ¿y si queremos almacenar nuestras bases de datos en otra ubicación ?. El script que deberemos utilizar es el siguiente:

01 create database prueba2 
02 on (name = prueba2_dat, filename = 'D:\Databases\prueba2_dat.mdf') 
03 log on (name = prueba2_log, filename = 'D:\Databases\prueba2_log.ldf') 
04 collate modern_spanish_ci_As

Incluso podemos decirle que el directorio de destino sea una carpeta de red (de otro equipo), pero en este caso, deberemos asignar permisos de Control Total en dicha carpeta para el usuario de Windows que inicia el servicio de SQL Server. Esta opción no es recomendable, porque dejamos en manos de la red el tiempo de respuesta que pueda tener nuestra base de datos, con lo que ello conlleva.

Vinculación de servidores con SQL Server 2008

Hay veces que necesitamos obtener en una sola consulta datos de tablas de diferentes bases de datos. Si estas bases de datos, están en el mismo servidor, no tenemos ningún problema, haciéndolo de la manera siguiente, lo tendríamos solucionado. Por ejemplo:

01 SELECT * FROM BD1.esquema.Tabla
02 UNION ALL
03 SELECT * FROM bd2.esquema.Tabla

En este caso estamos haciendo una simple unión (recordar que las campos que se devuelvan en cada subconsulta deben ser del mismo tipo y en el mismo orden). Pero que pasa si las bases de datos están en servidores diferentes, SQL Server 2008 nos proporciona las características necesarias para solventar este problema.

Lo primero que tendremos que hacer es agregar el servidor remoto al nuestro (al que estamos conectado) de la siguiente forma:

01 EXEC sp_addlinkedserver 'SERVIDORREMOTO\INSTANCIA', N'SQL Server';

Ahora,  de una forma similar a como lo haciamos con diferentes bases de datos pero en el mismo servidor, lo haremos pero anteponiendo  el servidor remoto.

01 SELECT * FROM [SERVIDORREMOTO\INSTANCIA].[BD1].[esquema].[Tabla]
02 UNION ALL
03 SELECT * FROM [SERVIDORLOCAL\INSTANCIA].[bd2].[esquema].[Tabla]

Para definir el Inicio de Sesión y la clave con la que queremos acceder al servidor, usaremos el procedimiento :sp_addlinkedsrvlogin, por ejemplo: exec sp_addlinkedsrvlogin 'h5sefsomzv.database.windows.net,1433', 'FALSE', NULL, 'iniciodesesion', 'claveiniciodesesion';

Informes con ReportViewer sobre modelo de Entity Framework

Creamos un nuevo proyecto Windows Forms con el Framework 4.0. Agregamos, como siempre, el nuestro modelo de datos de Entity Framework, usando esta vez el enfoque "Model first". Para ello pulsamos botón derecho sobre el proyecto, y Agregar/Nuevo elemento. Buscamos "ADO.NET Entity Data Model", le damos un nombre y creamos nuestro modelo. En mi caso:

Cuando tengamos el modelo creado, hacemos botón derecho sobre el, y pulsamos en "Generar Base de datos desde el modelo", seleccionamos el origen de datos deseado así como la base de datos y generámos las instrucciones DDL de nuestra base de datos. Lo ejecutamos para crear el metadata y listo para trabajar.

A nuestro formulario principal vamos a añadir un componente ReportViewer donde mostraremos el informe. Un ComboBox para seleccionar la familia de productos y un botón para ejecutar la acción.

Ahora vamos a crear el informe, para ello nuevamente pulsamos botón derecho sobre el proyecto, Agregar / Nuevo elemento y esta vez seleccionamos en la sección de Reporting, Informe (Report), Le damos un nombre y comenzamos a crear el informe.

En este paso vamos a crear los conjuntos de datos que usaremos en el informe, para ello en la barra de tareas "Datos del informe" hacemos botón derecho sobre Conjunto de Datos y le damos a Agregar nuevo conjunto de datos. Le damos un nombre y pulsamos en Nuevo / Objeto y buscamos en nuestro modelo, la entidad que queramos usar, en mi caso "Articulos". Este proceso lo repetimos con todas las entidades que queramos involucrar en nuestro informe.

Ahora solo tenemos que diseñar el informe como queramos, agregando gráficos, tablas, etc. y vinculando los conjuntos de datos a los distintos componentes.

Que pasa si queremos hacer un gráfico con datos de dos conjuntos de datos diferentes, una solución puede ser la siguiente:

Creamos una nueva Clase y la llamamos Vistas, por ejemplo. Dentro vamos a crear metodos que nos devuelve un objeto que satisface la interfaz IQueryable que nos devuelve los datos obtenidos desde el modelo de Entity Framework con consultas LINQ. por ejemplo:

01         public IQueryable<DetalleCompraVenta> getCompraVenta(
02         )
03         {
04             return (from c in this.getTotalVentas()
05                     select new DetalleCompraVenta
06                     {
07                         articulo = c.articulo,
08                         ventas = c.ventas,
09                         compras = 0
10                     }).Union(from d in this.getTotalCompras()
11                              select new DetalleCompraVenta
12                              {
13                                  articulo = d.articulo,
14                                  ventas = 0,
15                                  compras = d.compras
16                              }
17                     );
18         }

Por último, para agregar un conjunto de datos al informe con los datos de estos metodos, lo haremos de la misma forma que hicimos con las entidades del modelo. Ahora solo nos falta vincular este conjunto de datos al gráfico que queramos y listo.

El resultado del ejemplo es algo como esto:

Podéis descargar el ejemplo completo aquí.

Exprés: Consulta Linq sobre modelo Entity Framework con producto cartesiano.

Para realizar una consulta en LINQ con un producto cartesiano, lo haremos de la siguiente manera. Suponemos un contexto con las entidades "articulo" y "almacen" y necesitamos un listado con todos los artículos por almacenes. Seria lo siguiente:

01 IQueryable datos = (from c in contexto.articulos  
02    from d in contexto.almacenes                    
03    select new                    
04    {                        
05     articulo= c.articulo,                        
06     almacen = d.almacen                    
07    });

Suponemos que tenemos un contexto con las entidades "cliente" y "proveedor" cuya clave primaria es código en ambas. Además suponemos que si el código es el mismo, se refiere al mismo individuo, por tanto la consulta quedaría así:

01 IQueryable datos = (from c in contexto.clientes          
02    from d in contexto.proveedores                   
03    where c.codigo == d.codigoq                    
04    select new                    
05    {                        
06     codigo = c.articulo,                        
07     ventas = c.ventas,                        
08     compras = d.compras                    
09    });

También podría hacerse con un join:

01 IQueryable datos = (from c in contexto.clientes         
02    join d in contexto.proveedores 
03     on c.codigo equals d.codigo                    
04    select new                    
05    {                        
06     codigo = c.articulo,                        
07     ventas = c.ventas,                        
08     compras = d.compras                    
09    });

Hasta aquí el artículo exprés.