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:

usuario local ssis

Le vamos a negar explícitamente acceso al fichero .csv

Contexto de Ejecución en SSIS

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.

Contexto de Ejecución en SSIS

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.

package user ssis

Como veis, nada del otro mundo.

Contexto de Ejecución en SSIS

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:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N‘Package.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N‘Test’,
@project_name=N‘TestSecurity’,
@use32bitruntime=False,
@reference_id=Null,
@runinscaleout=False
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
@object_type=50, @parameter_name=N‘LOGGING_LEVEL’,
@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO
 

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.

Contexto de Ejecución en SSIS

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

Contexto de Ejecución en SSIS

Y ahora lanzar el código anterior:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N‘Package.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N‘Test’,
@project_name=N‘TestSecurity’,
@use32bitruntime=False,
@reference_id=Null,
@runinscaleout=False
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
@object_type=50, @parameter_name=N‘LOGGING_LEVEL’,
@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO
 

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)

Contexto de Ejecución en SSIS

Y finalmente si vemos el error, es obvio que ha funcionado puesto que ha fallado tal y como queríamos nosotros.

on information user role ssis

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:

use SSISDB;
execute as login = ‘WS2019_SQLS\user_no_permissions’
 
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N‘Package.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N‘Test’,
@project_name=N‘TestSecurity’,
@use32bitruntime=False,
@reference_id=Null,
@runinscaleout=False
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
@object_type=50, @parameter_name=N‘LOGGING_LEVEL’,
@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO
 

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?

execution information sql server ssis

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
0 Shares:
1 comment
  1. 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.

Deja una respuesta

Tu dirección de correo electrónico no será publicada.

You May Also Like
Leer más

Power BI on premise Vs Power BI on Cloud

Tener la funcionalidad de Power BI, pero on premise en lugar de en la nube, ha sido algo muy demandado. Por fin está en camino, cono?9e las nuevas caracteríasticas de Reposting Services para gestionar informes de Power BI, prepárate y decide qué hacer cuando salga al mercado en unos meses.