SSIS La propiedad ProtectionLevel

SSIS La propiedad ProtectionLevel

¡Cuantos quebraderos de cabeza da esta propiedad cuando comienzas con SSIS!   protectionlevel

Vamos a intentar comprender como funciona y que significa esta propiedad. Para ello primero vamos a pensar en cual es su necesidad, porque habríamos de poder encriptar algo de un paquete de SSIS y cuales son las opciones para hacerlo.

¿que es lo que hay que encriptar? Es muy común que tengas conexiones a bases de datos, servidores FTP, en resumen artefactos que pueden necesitar un usuario y un password. ¿os imagináis lo fácil que sería hackear un sistema si esa información se guarda en texto plano? Efectivamente sería trivial, tanto como abrir el fichero de texto y usar esa información.

Así pues, lo que hay que encritar es toda la información que resulte sensible. Ahora bien, ya puestos, podemos también encriptar todo el código fuente, ¿Qué mas dá?.

En realidad lo que digo es una ironía, encriptar esta bien y es divertido, pero luego … si quieres que sirva para algo hay que saber desencriptar. Si has encriptado la información sensitiva y olvidas el password o pierdes el user, simplemente te toca volver a escribir las cadenas de conexión, si has encriptado todo, …lo que sucede es que pierdes el código fuente.

Diferencia entre EncryptAll y EncryptSensitive. Pues la intuís ya… uno encripta solo la información sensible, el otro todo el paquete, todo el código fuente. y ya os hemos contado las consecuencias.

Diferencias entre WithUserKey y WithPassword. Cuando hacemos login en una máquina windows, estamos bajo un usuario, ese usuario tiene una clave privada de encriptación. Si encriptamos con Userkey, lo que sucede es que se usa esa clave privada de encriptación para encriptar. Por tanto solo nosotros podemos desencriptar esa información. Si por el contrario usamos un password, para desencriptar hay que proporcionar ese password.

¿Porqué da problemas WithUserKey  al llevar los paquetes a producción? La pregunta hay que hacérsela de la siguiente forma. Una vez que nosotros agendamos un paquete con el SQL Server Agent ¿Qué usuario es el que abre el paquete para ejecutarlo? ¿Es nuestro usuario? la respuesta normalmente será … no, no es nuestro usuario, será el usuario que levanta el agente de SQL Server, un usuario creado en un proxy especial para poder ejecutar los paquetes… en definitiva, no debería ser nuestro usuario. Si quien abre el paquete es otro usuario, y la información sensible está encriptada con nuestra clave privada ¿Cuál es la consecuencia?. Evidentemente que el otro usuario no puede desencriptar la información sensible, y por lo tanto no puede conectarse a los sistemas que usen usuario y password para conectarse.

Ya conocemos el problema ¿ahora como lo solucionamos?

Una opción obviamente es encriptar con password pero… cada vez que agendamos un paquete, entramos a modificar cualquier cosa nos va a solicitar ese password. Ese password dentro de X años… es probable que se haya olvidado, que caiga en demasiadas manos.. no se.. mil circunstancias por lo que quizá no sea tan buena idea que usemos esta opción.

¡Configuraciones al rescate!

conusuarioypass

Primero de todo vamos a crear dos conexiones  a SQL Server, una con seguridad integrada y otra con usuario y contraseña. La diferencia entre una y otra es que una no contiene información sensible en absoluto y la otra si.

Como la opción por defecto es Encrypt with user Key el resultado será que en mi paquete, esa conexión estará encriptada con la clave de usuario.  Si buscamos en el código fuente del paquete vamos a comprobar si realmente está o no encriptada.

 

 

 

 

<DTS:ObjectData><DTS:ConnectionManager
<DTS:ConnectionString=“Data Source=.;User ID=Director;Initial Catalog=Demo;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;”>
<DTS:Password
DTS:Name=“Password”
Sensitive=“1”
Encrypted=“1”>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAA1ExtqbOEykaKgYfZcbHyvwAAAAAIAAAARABUAFMAAAAQZgAAAAEAACAAAACt77fw7mL8BnyUnPjl7eWBe7ChzQVurAWXXNkGinYGjgAAAAAOgAAAAAIAACAAAAAEaDPVow0+qAwdDFMv7PXnrMz5p5S5/JVvg4XUo0Ib+SAAAAAhwvvLMsLBhrVxiBoCJJJkaL4YuYMf1ASRnjW5SdfSn0AAAAClouZA/0hxrfdHaYpDHp8Co/21C5s6y886f6JZ8y/v0OS26hU4cwTAWAXSckqfWk+Zx3v7RtcGbFH8G7CmhYv+</DTS:Password>
</DTS:ConnectionManager>
</DTS:ObjectData>

El caso es que … efectivamente la información de password se ha guardado encriptada, y el método de encriptación ha sido el de UserKey.

Ahora voy a añadir al paquete dos comandos sql muy simples, simplemente que fuercen la apertura de la conexión. El paquete quedará así.

ssis demo 1Ahora simplemente vamos a intentar agendar el paquete.

Hay dos escenarios distintos. dependiendo de quien sea el usuario que levante la cuenta del agente. Vamos a comenzar con una instalación por defecto… bastante sucia en la que se deja la cuenta como “localSystem”.

Si ejecutamos el paquete en el entorno de desarrollo y lo ejecutamos el resultado va a ser en profiler algo así

ejecucionssispack01

 

 

El mensaje importante aquí es el usuario que ha ejecutado cada una de las instrucciones. En la conexión que he configurado como integrada he sido yo mismo, mi propio usuario, con el que he entrado al entorno de visual studio el que ha ejecutado el query. En la segunda conexión es el usuario “director” que es el usuario que yo mismo he configurado con usuario y contraseña.

En resumen, desde el entorno funciona, o dicho de un modo mas programador “en mi máquina funciona”. Vi un cartel en la sede de Microsoft impreso en una camiseta que me encantó, decía algo así “No me importa si funciona en tu máquina.. no voy a enviar tu máquina” :). Eso está ya en mis learned lessons, que lo hayamos visto ejecutarse no quiere decir que el trabajo está terminado. Vamos ahora a agendarlo en el agente (me salgo los pasos necesarios para crear el job, si algún lector los necesita, que escriba un comentario y vemos de escribir otro artículo).

Cuando ejecutamos el job, el resultado es el siguiente

ejecucionssispack03Aquí vemos que aunque yo puse “guardar password” realmente no ha sido capaz de volver a encontrarlo. El motivo es que si miramos justo en la segunda línea, debajo de Director, la cuenta que ha ejecutado mi paquete ha sido NT SERVICE\SQLSERVERAGENT, que no es la cuenta con la que creé el paquete y que por lo tanto no puede descodificar esa contraseña.

Sin embargo la que tiene seguridad integrada si ha funcionado, ¿por qué? porque el usuario NT Service\sqlServerAgent si tiene permisos en SQL Server para ejecutar esa consulta, no es el mismo con el que yo había probado pero si tiene los privilegios necesarios, además ninguna información sensible está guardada ya que la seguridad es que el servicio se levantó. Así pues…. funciona.

¿Qué pasaría si en lugar de acceder a SQL Server accedieramos al sistema de archivos? Nuestro usuario de windows EGEAPORTATIL\miguel si tiene permisos en una carpeta determinada, pero el usuario NT Service\sqlServerAgent… no los tiene, así pues deberíamos darle los permisos necesarios al usuario que ejecuta el paquete, o cambiar el usuario que arranca el servicio del SQL Server Agent a un usuario con privilegios para ambas cosas. Si no hay contraseñas para acceder de por medio, simplemente funcionaría.

¿Cómo lo arreglamos? Esto que voy a escribir es mala idea, pero es una posibilidad así que os la comento. Al final en el agendado del paquete puedo cambiar el usuario, pero esa contraseña ahí a fuego… no es buena idea, de todas formas que veáis que funciona.ejecucionssispack04 Primero cambiamos en el datasource:

Luego comprobamos la ejecución en el profiler.

ejecucionssispack05¿Tenemos ya identificado el problema? Realmente hay mas de un problema como en todo asunto que es realmente complicado, realmente interesante.

  • De una parte está  el problema de los datos sensibles y como guardarlos
  • De otra está el tener claro que cuenta ejecuta el paquete y que privilegios tiene.
  • Por si eso no fuera suficiente, además es posible crear proxies para que los paquetes se ejecuten bajo otras credenciales. (Igual que mencionaba antes, si algún lector tiene interés en esto, que deje un comentario y cuando pueda escribiré sobre el asunto).
Empecemos por el final. Si hay una cuenta proxy influirá en el usuario que ejecuta los paquetes y por lo tanto en las credenciales que acaban teniendo influencia cuando se ejecuta el paquete  y cuando se desencripta la información si está encriptada mediante userkey.
Lo mismo sucederá si es la cuenta que levanta los servicios del SQL Server.  Así que esos dos puntos resueltos.
Las cuentas proxy, si alguien se está preguntando su utilidad, tienen mucho sentido, pueden otorgarse permisos específicos a proyectos o paquetes concretos, así pues podemos crear una especie de “sandbox” de forma que quede asegurado que un conjunto de paquetes tiene los permisos exactos que necesita y ninguno mas. El ejemplo sería el siguiente, imaginemos que una aplicación necesita permisos para leer una carpeta compartida “salarios”, donde están los salarios de toda la compañía. Si lo agendamos usando el Agente de SQL , la cuenta que levanta el servicio sql necesita permisos sobre esa carpeta. Tres meses después otra aplicación instala un paquete, ese paquete, sin necesitarlo también tendría permisos en esa carpeta, pudiendo hacer uso de ella sin que realmente ese fuese el sabor deseado.
SSIS Catalog, parametrización e información sensible
Si hay campos que tienen información sensible, en SSIS se encriptaran con el método que hayamos elegido. Ahora viene la siguiente parte… que hacemos con los valores sensibles. En este caso SSIS tiene una funcionalidad que se llama parametrización. Veamos como funciona. Primero de todo en la aplicación podemos indicar que propiedades son parametrizables.ejecucionssispack06
Para hacer eso, simplemente pulsamos botón derecho parametrizar. Esta opción de menú está presente en muchos puntos de SSIS, todos aquellos que tiene sentido parametrizar.
Una vez que hemos elegido el menú nos aparecerá esta pantalla. En ella se puede decidir que valor vamos a generar como parámetro, también si se utiliza un parámetro que ya existía, apuntando el valor a él, o bien si vamos a crear uno nuevo. También se especifica el alcance (scope), obviamente hay nivel de paquete y de proyecto, y además se puede especificar si es información sensible o no y si es obligatoria o no.  En el ejemplo que estamos usando, veréis que  estamos paremtrizando el password, y que estamos marcándolo como sensitivo y como requerido a nivel de paquete.
ejecucionssispack07

 

Esta pantalla obviamente está diciendo que eso ha de ser pametrizado y su forma pero no está implementando esa parametrización. Veamos en que momento se hace esa parametrización.

Lo primero que vamos a hacer es desplegar en el catálogo.

Si no tiene creado el Catalogo, simplemente sobre Integration Services Catalogs botón derecho crear catálogo y seguir el wizard.

La forma de informar el parámetro simplemente es ir a parámetros y rellenarlo, como podemos ver acá.

ejecucionssispack08

Después desde visual studio podemos desplegar el proyecto, y es ahí donde debemos crear una configuración para informar el campo.

Desplegamos el proyecto, botón derecho, deploy, nos pregunta primero por el source (que será el mismo proyecto), luego por el destino, (servidor, carpeta),  y con eso se despliega. (O bien proporcionamos el fichero de compilación *.ispac) que ejecutará el mismo wizard.

Una vez desplegado el proyecto si no nos preocupamos de nada pero intentamos ejecutarlo. Al ser un valor requerido el que hemos configurado en el ejemplo, no nos permitirá ejecutar el paquete en tanto en cuanto no se informe ese valor.

ejecucionssispack09

En la imagen de arriba puede verse el error que marca al pulsar botón derecho -> execute sobre “Package.dtsx”. Una vez informado el valor, la ejecución funcionará perfecta.

Sin embargo… no vamos a estar escribiendo siempre este valor. Lo suyo es que se pueda fijar, encriptado para el agente de forma que el dato se informe y no esté expuesto.

Para esto existen los Enviroments.

Vamos a crear un enviroment, y en él vamos a guardar ese valor y luego asociaremos el enviroment a la ejecución.

Esto además ofrece muchas ventajas, si os fijáis el código fuente es el mismo entre todos los entornos típicos (desarrollo, integración,producción), sin embargo solamente se creará ese enviroment una vez, (se puede generar un script para crear los ambientes, pero no viene al caso en este artículo). Luego simplemente cada entorno ejecuta los paquetes en base a su configuración, y nos quitamos muchos dolores de cabeza.

ejecucionssispack10

Para asociar esos parámetros se hace de la siguiente forma.

Primero sobre el proyecto ejecutamos botón derecho ->Configurar. Después en el menú References, añadimos una referencia al enviroment que acabamos de crear. y después añadimos la relación entre el parámetro y la variable.

Se que suena complicado, por eso, en breve completaré este artículo con un video de demostración de la creación y configuración de todo esto.

ejecucionssispack12

ejecucionssispack11

Por último al ejecutar (o agendar con SSIS) nos permite usar el entorno (enviroment), de esa forma ya no hemos de preocuparnos por las credenciales. Se hará de la siguiente forma.
 
En resumen, las configuraciones nos ayudan a asegurar las aplicaciónes, mejorar su seguridad sin penalizar su funcionalidad. Es cierto que son muchas piezas que hay que entender y juntar, pero lo cierto es que el resultado es bastante brillante.
ejecucionssispack14
Luego simplemente asociamos el enviroment, y al ejecutarse, funcionará apareciendo este report.

 Espero que este artículo ayude a despejar dudas.

Saludos

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *