Una vez desplegado el paquete SSIS en el catálogo de sistema SQL Server…¿quién ejecuta finalmente el paquete una vez lo programamos? ¿la cuenta de servicio SSIS? ¿El usuario que conecta a SQL Server? ¿El agente de SQL Server?
Si lo piensas realmente, es una pregunta realmente interesante cuando lidiamos con seguridad en un servicio que tiene como objetivo leer-escribir información de múltiples fuentes de datos, no solo SQL Server, sino datalakes.
La respuesta corta es:
- Si la ejecución no se realiza a través de un SQL Server Proxy (la recomendación oficial es usar proxy), y se realiza directamente ejecutando el paquete, se va a utilizar las credenciales de cuenta contra la que se haya conectado a la instancia SQL Server. A partir de ahí, se va a realizar todo el trasvase de credenciales hacia el resto de orígenes/destinos utilizados en nuestro ETL.
IMPORTANTE: Cuidado porque EXECUTE AS no va a servirte en este caso
Veamos un ejemplo sencillo para ilustrar el comportamiento:
- Vamos a cargar un fichero .csv sobre una tabla en SQL Server
- Vamos a crear un usuario local de Windows donde tenemos desplegado SQL Server y SSIS
- Dejaremos totalmente sin permisos a dicho usuario sobre el propio fichero.
- Le daremos sysadmin sobre SQL Server.
Crear usuario local para nuestra prueba
Será un usuario normal y corriente, sin permisos especiales:
Le vamos a negar explícitamente acceso al fichero .csv
Y le vamos a dar permisos SYSADMIN sobre SQL Server para que no quede lugar a dudas a la hora de ver cómo impersona.
ETL de carga de datos
Vamos a crear un paquete de carga muy sencillito que simplemente lea de disco el fichero .csv y lo meta en una tabla SQL Server.
Como veis, nada del otro mundo.
Lo interesante ocurre cuando tratemos de ejecutar el paquete 😊
Ejecución de Package.dtsx
Una vez desplegado el paquete como de costumbre sobre SSIS, vamos a proceder a ejecutarlo manualmente para probar el traspaso de credenciales con este código:
Si lanzamos el paquete conectado con un usuario que tenga permisos en el dispositivo local (Administrator, por ir a lo fácil)
Vemos que efectivamente todo ha ido bien.
Ejecutar paquete con el usuario sin privilegios
Para ejecutar el paquete con el usuario WS2019_SQL\user_no_permissions, lo que vamos a hacer es logarnos con ese usuario y lanzar la ejecución del paquete. Lo mas fácil para este ejemplo es abrir un SSMS con el dialogbox de cambio de login y lanzar el código anterior
Y ahora lanzar el código anterior:
Y tal y como hemos comentado anteriormente, pese a ser sysadmin en SQL Server, el proceso de sqlservr.exe correctamente impersona nuestro usuario al acceder a recursos externos de la instancia y se produce el error esperado de no poder leer el fichero (que nosotros deliberadamente hemos forzado si recuerdas)
Y finalmente si vemos el error, es obvio que ha funcionado puesto que ha fallado tal y como queríamos nosotros.
Cuidado con EXECUTE AS
Si has llegado hasta aquí, tal como avanzába al principio de este post tienes que saber que EXECUTE AS no funciona como esperaríamos, ya que solo afecta a threads manejados por el scheduler de SQL Server y cuando debe salir a por recursos externos, el proceso SQL Server heredará la cuenta con la que se conectó originalmente a la instancia
La prueba es muy sencilla nuevamente y consiste en logarse con el usuario que tiene permisos para leer el fichero (Administrator) y luego cambiar el contexto de ejecución por código mediante EXECUTE AS:
Lo que vamos a ver ahora no es lo esperado puesto que pese a decirnos que ha sido ejecutado con el usuario sin permisos. ¿Ha funcionado?
Lo cual es imposible como ya sabes….pero como decía al inicio del post, hay que tener en cuenta que en este caso al acceder a recursos EXTERNOS a SQL Server, el contexto de ejecución va a ser el del usuario con el que nos hayamos logado, saltándose los permisos del usuario indicado por EXECUTE AS 😊
¡Has llegado al final! Parece que te ha gustado nuestro post sobre SSIS
¿Necesitas sacar más partido a tus datos para impulsar tu proyecto? Acelera tus procesos de Business Analytics. Toma mejores decisiones, optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica con nuestra consultoría en Business Intelligence.
Voy a Echar un vistazo
1 comment
Usar SQL Agent : Usando el procedimiento almacenado «sp_start_job», se puede ejecutar un job el cual puede ejecutar un paquete SSIS. Por lo tanto se debe crear un job y luego llamarlo a traves de T-SQL a traves del procedimiento almacenado «sp_start_job». Esta forma permite la ejecucion remota, pero se debe instalar el agente de SQL Server, ademas el procedimiento «sp_start_job» solamente retorna si fue exitosa la ejecucion del job, no si fue exitosa la ejecucion del paquete SSIS. : El procedimiento almacenado xp_cmdshell permite la ejecucion de comandos de windows en SQL Server. Asi se puede ejecutar la utilidad DTExec.exe, del punto dos, a traves de este procedimiento almacenado. Hay que tener precaucion, ya que la llamada de xp_cmdshell se ejecuta con los mismos privilegios de la cuenta de SQL Server, por lo que puede haber problemas de seguridad.