Partager via

Le package SSIS ne s'exécute pas en cas d'appel depuis une étape de tâche SQL Server Agent

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écute correctement en dehors de SQL Server Agent.

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 pour laquelle le package a échoué. Les raisons pour lesquelles le package a peut-être échoué 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 n’a pas les autorisations requises 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 n’a pas les autorisations requises.
  • L’accès aux fichiers échoue, car l’utilisateur actuel n’a pas les 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 un 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 une réussite limitée, 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é du 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 autorise le contrôle d’accès via des rôles de base de données SQL Server.

  • Méthode 3 : Définissez la propriété package ProtectionLevel SSIS sur EncryptSensitiveWithPassword. Remplacez la propriété EncryptSensitiveWithPasswordpackage ProtectionLevel SSIS par . 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 du 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é DontSaveSensitive pour 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 produit 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 à l’aide de 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 propriété ProtectionLevel 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 de travail. Le texte de l’historique des travaux de l’Agent SQL Server affiche des informations qui ressemblent à ce qui suit :

Déchiffrer les secrets du package

Le paramètre par défaut de 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éessensitive, telles que les mots de passe, les noms d’utilisateur et les chaîne 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 à satisfaire les 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 qu’il doit avoir à se connecter.

Important

Envisagez 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 provoque 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 SSIS Dtutil.exe 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 de Dtutil.exe dans les fichiers et boucles batch, 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 sql Server Agent (cmd Exec) du système d’exploitation pour modifier la ProtectionLevel propriété EncryptSensitiveWithUserKeyen . Ce processus implique le déchiffrement du package à l’aide du mot de passe, puis le rechiffrage 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 d’ordinateur, l’effet du déplacement des packages vers un autre ordinateur peut être limité.

Vérifiez que vous disposez d’informations détaillées sur les erreurs relatives à l’échec du package SSIS

Au lieu de vous appuyer sur les détails limités de l’historique des travaux de l’Agent SQL Server, 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 du sous-système exec au lieu de la commande du sous-système SSIS.

À propos de la journalisation SSIS

Les fournisseurs de journalisation et de journalisation SSIS vous permettent de capturer des détails sur l’exécution et les échecs du package. Par défaut, le package ne journale 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 s’affichent comme suit. 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és à la ligne de commande SSIS pour appeler le fichier exécutable de ligne de commande SSIS Dtexec.exe. 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’historique des travaux de l’Agent SQL Server.

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 les détails qui ressemblent aux messages suivants :

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