Paquete SSIS funciona manualmente pero falla como SQL JOB
por Luis Ramirez
Este tipo de incidente es uno del cual recibimos muchas llamadas, paquetes/tareas que corren manualmente pero fallan cuando se agendan como una tarea (JOB) de SQL.
La clave de todo esto es el CONTEXTO bajo el cual se ejecuta. Esto es cuando uno corre el paquete o tarea manualmente, lo hace bajo el contexto de los permisos que tengamos asignados y cuando el SQL Agent lo corre será bajo los permisos de la cuenta con la que este corriendo el Agente.
El error recibido en el visor de sucesos es:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Description:
SQL Server Scheduled Job <Nombre del Job>
(0x90CADC226423BE48B5A870303E5CAD9A) - Status: Failed - Invoked on: 2006-07-30
21:23:10 - Message: The job failed. The Job was invoked by User <Sql agent
account>.
The last step to run was step 1 (test).
…
En el historial del JOB veremos algo similar a:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
Aquí lo en base al historial el error es que no pudo desencriptar la clave (password). Pero a que clave se refiere?
SSIS tiene entre sus propiedades una opción donde uno marca como queremos que se maneje la seguridad de la información en este paquete. En otras palabras, que usuarios estarán autorizados para ver/ejecutar este paquete.
Para entender esto hay que entender cómo se guardan los paquetes, bajo Business Intelligence Development Studio (BIDS) se ofrecen tres maneras de almacenamiento:
“File System”: Significa que se guarda el paquete como un archivo XML en una localidad especificada por el usuario.
“SSIS Stored”: Es lo mismo que “File System” pero la localidad donde se guarda seria: “Program Files\Microsoft SQL Server\90\DTS\Packages”.
“SQL Server” : El paquete SSIS se almacena dentro de SQL Server bajo la base de datos MSDB.
Dependiendo como almacenemos el paquete de SSIS serán las opciones de seguridad que tendremos disponibles.
Cuando seleccionamos almacenar el paquete como un archivo ( “File System”/”SSIS Stored” ) en el servidor, se contara con las siguientes opciones de seguridad:
Do not save sensitive data (DontSaveSensitive) - Elimina los valores sensitivos del paquete (nombre de usuario, claves, cadenas de conexión) así que el usuario cada vez que abra este paquete tiene que llenar la información sensitiva.
Encrypt all with password (EncryptAllWithPassword) - Para abrir o correr este paquete necesita una clave dada por el usuario que guarda/exporta.
Encrypt all data with user key (EncryptAllWithUserKey) – Encripta el paquete en base al perfil de la cuenta del usuario que guarda/exporta el paquete. Solo este usuario podrá correr/ver el paquete
Encrypt sensitive with password (EncryptSensitiveWithPassword) – Encripta los valores sensitivos del paquete (nombre de usuario, claves, cadenas de conexión) y solo podrán ser vistos al poner la clave
Encrypt sensitive with user key (EncryptSensitiveWithUserKey) – Encripta los valores sensitivos del paquete (nombre de usuario, claves, cadenas de conexión) en base al perfil del usuario que guarda/exporta el paquete.
Cuando seleccionamos almacenar dentro de SQL Server:
Rely on server storage for encryption (ServerStorage) - La seguridad será implementada por medio de ROLES de SQL Server.
El nivel de seguridad por DEFAULT viene siendo “EncryptionSensitiveWtihUserKey” esto significa que la información sensitiva solo podrá ser vista por el usuario creador del paquete. Por lo tanto cuando lo ejecutas bajo una tarea programada (SQL JOBS) fallara si no es la misma cuenta que guardo/exporto el paquete y generara el error de no poder desencriptar la clave.
Para solucionar este comportamiento se puede:
- Crear una cuenta de SQL PROXY con los atributos necesarios y asignarla al JOB.
- Guardar el paquete en el servidor usando la opción “ServerStorage” manejando la seguridad por ROLES.
- Guardar el paquete como archivo con la opción “EncryptSensitiveWithPassword” y en los pasos de la tarea incluir la clave como parte de la línea de comando del paso (STEP).
- Usar archivos de configuración donde se almacene la información sensitiva en una carpeta con los permisos necesarios. El paquete se guardaría como “DontSaveSensitive” y programándolo que lea del archivo de configuración
- Crear una plantilla que por default tenga el nivel de encriptación diferente al default.
Estas opciones de almacenamiento y seguridad las puedes acceder desde el menú de “Business Intelligence Development Studio” seleccionado :
[FILE] -> [Save Copy of <nombre de paquete> As]
Bajo [Package Location] seleccionar donde se almacenara el paquete:
En la ventana anterior seleccionar [Protection Level] y dependiendo el almacenamiento del paquete seleccionar la seguridad a emplear.
