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:
IMPORTANTE: Cuidado porque EXECUTE AS no va a servirte en este caso
Veamos un ejemplo sencillo para ilustrar el comportamiento:
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.
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 😊
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.
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.
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 😊
¿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 vistazoSoy Technical Leader en Verne TECH, MVP de Microsoft y Microsoft Certified Trainer (MCT). Estoy focalizado en motores relacionales de SQL Server y me encanta resolver problemas de rendimiento y escalabilidad en sistemas OLTP. He liderado más de 100 proyectos, no sólo en España, sino también en otros países como USA, Holanda, México, etc. siendo el principal arquitecto de soluciones como HealthCheck, QueryAnalytics y DatabaseObfuscator.
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.