Le package SSIS ne s’exécute pas lorsqu’il est appelé à partir d’une étape de travail SQL Server Agent

  • Article

Cet article vous aide à résoudre le problème qui se produit lorsque vous appelez un package SSIS à partir d’une étape de travail SQL Server Agent.

Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 918760

Symptômes

Lorsque vous appelez un package Microsoft SQL Server Integration Services (SSIS) à partir d’une étape de travail SQL Server Agent, le package SSIS ne s’exécute pas. Toutefois, si vous ne modifiez pas le package SSIS, il s’exécutera correctement en dehors de SQL Server Agent.

Résolution

Pour résoudre ce problème, appliquez l’une des méthodes suivantes : La méthode la plus appropriée dépend de l’environnement et de la raison de l’échec du package. Les raisons de l’échec du package sont les suivantes :

  • Le compte d’utilisateur utilisé pour exécuter le package sous SQL Server Agent diffère de l’auteur du package d’origine.
  • Le compte d’utilisateur ne dispose pas des autorisations nécessaires pour établir des connexions ou accéder aux ressources en dehors du package SSIS.

Le package peut ne pas s’exécuter dans les scénarios suivants :

  • L’utilisateur actuel ne peut pas déchiffrer les secrets du package. Ce scénario peut se produire si le compte actuel ou le compte d’exécution diffère de l’auteur du package d’origine et que le paramètre de propriété ProtectionLevel du package ne permet pas à l’utilisateur actuel de déchiffrer les secrets dans le package.
  • Une connexion SQL Server qui utilise la sécurité intégrée échoue, car l’utilisateur actuel ne dispose pas des autorisations requises.
  • L’accès aux fichiers échoue, car l’utilisateur actuel ne dispose pas des autorisations requises pour écrire dans le partage de fichiers auquel le gestionnaire de connexions accède. Par exemple, ce scénario peut se produire avec des fournisseurs de journaux de texte qui n’utilisent pas de connexion et de mot de passe. Ce scénario peut également se produire avec n’importe quelle tâche qui dépend du gestionnaire de connexions de fichiers, comme une tâche de système de fichiers SSIS.
  • Une configuration de package SSIS basée sur le Registre utilise les clés de HKEY_CURRENT_USER Registre. Les HKEY_CURRENT_USER clés de Registre sont spécifiques à l’utilisateur.
  • Une tâche ou un gestionnaire de connexions nécessite que le compte d’utilisateur actuel dispose des autorisations appropriées.

Pour résoudre le problème, utilisez les méthodes suivantes :

  • Méthode 1 : utiliser un compte proxy SQL Server Agent. Créez un compte proxy SQL Server Agent. Ce compte proxy doit utiliser des informations d’identification qui permettent SQL Server Agent d’exécuter le travail en tant que compte qui a créé le package ou en tant que compte disposant des autorisations requises.

    Cette méthode fonctionne pour déchiffrer les secrets et répond aux exigences de clé par l’utilisateur. Toutefois, cette méthode peut avoir un succès limité, car les clés utilisateur du package SSIS impliquent l’utilisateur actuel et l’ordinateur actuel. Par conséquent, si vous déplacez le package vers un autre ordinateur, cette méthode peut toujours échouer, même si l’étape de travail utilise le compte proxy approprié.

  • Méthode 2 : Définissez la propriété Package ProtectionLevel SSIS sur ServerStorage. Remplacez la propriété SSIS Package ProtectionLevel par ServerStorage. Ce paramètre stocke le package dans une base de données SQL Server et permet un contrôle d’accès via SQL Server rôles de base de données.

  • Méthode 3 : Définissez la propriété package ProtectionLevel SSIS sur EncryptSensitiveWithPassword. Remplacez la propriété Package ProtectionLevel SSIS par EncryptSensitiveWithPassword. Ce paramètre utilise un mot de passe pour le chiffrement. Vous pouvez ensuite modifier la ligne de commande de l’étape de travail SQL Server Agent pour inclure ce mot de passe.

  • Méthode 4 : Utiliser les fichiers de configuration du package SSIS. Utilisez les fichiers de configuration de package SSIS pour stocker des informations sensibles, puis stockez ces fichiers de configuration dans un dossier sécurisé. Vous pouvez ensuite modifier la ProtectionLevel propriété en DontSaveSensitive afin que le package ne soit pas chiffré et n’essaie pas d’enregistrer les secrets dans le package. Lorsque vous exécutez le package SSIS, les informations requises sont chargées à partir du fichier de configuration. Assurez-vous que les fichiers de configuration sont correctement protégés s’ils contiennent des informations sensibles.

  • Méthode 5 : Créer un modèle de package. Pour une résolution à long terme, créez un modèle de package qui utilise un niveau de protection différent du paramètre par défaut. Ce problème ne se produira pas dans les packages futurs.

Procédure pour reproduire le problème

  1. Connectez-vous en tant qu’utilisateur qui ne fait pas partie du groupe SQLServerSQLAgentUser. Par exemple, vous pouvez créer un utilisateur local.
  2. Créez un package SSIS, puis ajoutez une tâche ExecuteSQL. Utilisez un gestionnaire de connexions OLE DB pour le fichier msdb local en utilisant la chaîne suivante : 'Windows Authentication' -SQLSourceType: "Direct Input" -SQLStatement: "sp_who".
  3. Exécutez le package pour vous assurer qu’il s’exécute correctement.
  4. La ProtectionLevel propriété est définie sur EncryptSensitiveWithPassword.
  5. Créez un travail SQL Server Agent et une étape de travail. Dans la liste D’identification, cliquez sur SQL Server Agent service pour exécuter l’étape du travail. Le texte de l’historique des travaux SQL Server Agent affiche des informations qui ressemblent à ce qui suit :

Déchiffrer les secrets du package

Le paramètre par défaut pour la propriété de package ProtectionLevel SSIS est EncryptSensitiveWithUserKey. Lorsque le package est enregistré, SSIS chiffre uniquement les parties du package qui contiennent des propriétés marquées sensitive, telles que les mots de passe, les noms d’utilisateur et les chaînes de connexion. Par conséquent, lorsque le package est rechargé, l’utilisateur actuel doit satisfaire aux exigences de chiffrement pour que les sensitive propriétés soient déchiffrées. Toutefois, l’utilisateur actuel n’a pas besoin de satisfaire aux exigences de chiffrement pour charger le package. Lorsque vous exécutez le package via une étape de travail SQL Server Agent, le compte par défaut est le compte de service SQL Server Agent. Ce compte par défaut est probablement un utilisateur différent de l’auteur du package. Par conséquent, l’étape de travail SQL Server Agent peut charger et commencer à exécuter l’étape de travail, mais le package échoue car il ne peut pas terminer une connexion. Par exemple, le package ne peut pas terminer une connexion OLE DB ou une connexion FTP. Le package échoue, car il ne peut pas déchiffrer les informations d’identification dont il doit disposer pour se connecter.

Importante

Considérez le processus de développement et l’environnement pour déterminer quels comptes sont nécessaires et utilisés sur chaque ordinateur. Le paramètre EncryptSensitiveWithUserKey de la ProtectionLevel propriété est un paramètre puissant. Ce paramètre ne doit pas être réduit, car il entraîne d’abord des complications de déploiement. Vous pouvez chiffrer les packages lorsque vous êtes connecté au compte approprié. Vous pouvez également utiliser l’utilitaire d’invite de commandes Dtutil.exe SSIS pour modifier les niveaux de protection à l’aide d’un fichier .cmd et du sous-système de commande SQL Server Agent. Par exemple, procédez comme suit. Étant donné que vous pouvez utiliser l’utilitaire Dtutil.exe dans les fichiers de commandes et les boucles, vous pouvez suivre ces étapes pour plusieurs packages en même temps.

  1. Modifiez le package que vous souhaitez chiffrer à l’aide d’un mot de passe.

  2. Utilisez l’utilitaire Dtutil.exe via une étape de travail de SQL Server Agent système d’exploitation (cmd Exec) pour remplacer la propriété par ProtectionLevelEncryptSensitiveWithUserKey. Ce processus implique le déchiffrement du package à l’aide du mot de passe, puis le rechiffrement du package. La clé utilisateur utilisée pour chiffrer le package est le paramètre d’étape de travail SQL Server Agent dans la liste d’identification.

    Remarque

    Étant donné que la clé inclut le nom d’utilisateur et le nom de l’ordinateur, l’effet du déplacement des packages vers un autre ordinateur peut être limité.

Vérifiez que vous disposez d’informations d’erreur détaillées sur l’échec du package SSIS

Au lieu de vous appuyer sur les détails limités de l’historique des travaux SQL Server Agent, vous pouvez utiliser la journalisation SSIS pour vous assurer que vous disposez d’informations d’erreur sur l’échec du package SSIS. Vous pouvez également exécuter le package à l’aide de la commande exec subsystem au lieu de la commande de sous-système SSIS.

À propos de la journalisation SSIS

La journalisation SSIS et les fournisseurs d’journaux vous permettent de capturer des détails sur l’exécution et les échecs du package. Par défaut, le package n’enregistre pas les informations. Vous devez configurer le package pour journaliser les informations. Lorsque vous configurez le package pour journaliser les informations, des informations détaillées qui ressemblent à ce qui suit s’affichent. Dans ce cas, vous savez qu’il s’agit d’un problème d’autorisations :

OnError,DOMAINNAME,DOMAINNAME\USERNAME,FTP Task,{C73DE41C-D0A6-450A-BB94-DF6D913797A1},{2F0AF5AF-2FFD-4928-88EE-1B58EB431D74},4/28/2006 1:51:59 PM,4/28/2006 1:51:59 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

OnError,DOMAINNAME,DOMAINNAME\USERNAME,Execute SQL Task,{C6C7286D-57D4-4490-B12D-AC9867AE5762},{F5761A49-F2F9-4575-9E2B-B3D381D6E1F3},4/28/2006 4:07:00 PM,4/28/2006 4:07:00 PM,-1073573396,0x,Failed to acquire connection "user01.msdb". Connection may not be configured correctly or you may not have the right permissions on this connection.

À propos de la commande du sous-système exec et des informations de sortie

En utilisant l’approche de commande du sous-système exec, vous ajoutez des commutateurs de journalisation de console détaillé à la ligne de commande SSIS pour appeler le fichier exécutable de ligne de commande Dtexec.exe SSIS. En outre, vous utilisez la fonctionnalité De travail avancé du fichier de sortie. Vous pouvez également utiliser l’option Inclure la sortie de l’étape dans l’historique pour rediriger les informations de journalisation vers un fichier ou vers l’SQL Server Agent Historique des travaux.

Voici un exemple de ligne de commande :

 dtexec.exe /FILE "C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT

La journalisation de la console retourne des détails qui ressemblent à ce qui suit :

Error: 2006-04-27 18:13:34.76 Code: 0xC0202009 Source: AgentTesting Connection manager "(local).msdb" Description: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'DOMAINNAME\username'.". End Error

Error: 2006-04-28 13:51:59.19 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.". 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. End Error

Log: Name: OnError Computer: COMPUTERNAME Operator: DOMAINNAME\username Source Name: Execute SQL Task Source GUID: {C6C7286D-57D4-4490-B12D-AC9867AE5762} Execution GUID: {7AFE3D9E-5F73-42F0-86FE-5EFE264119C8} Message: Failed to acquire connection "(local).msdb". Connection may not be configured correctly or you may not have the right permissions on this connection. Start Time: 2006-04-27 18:13:34 End Time: 2006-04-27 18:13:34 End Log

References

Malheureusement, les utilisateurs ne savent pas que les paramètres d’étape de travail de l’agent par défaut les placent dans cet état. Pour plus d’informations sur les proxys SQL Server Agent et SSIS, consultez les rubriques suivantes dans la documentation en ligne de SQL Server 2005 :

  • Planification de l’exécution du package dans SQL Server Agent
  • Création de proxys SQL Server Agent