Le package SSIS ne s’exécute pas lorsqu’il est appelé à partir d’SQL Server de travail de l’agent de sécurité

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

Version du produit d’origine :   SQL Server
Numéro de la ko 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, utilisez 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 requises pour établir des connexions ou accéder à des 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 ne peut pas écrire dans le partage de fichiers accessible par le gestionnaire de connexions. 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, telle qu’une tâche de système de fichiers SSIS.
  • Une configuration de package SSIS basée sur le Registre utilise les HKEY_CURRENT_USER clés de Registre. Les HKEY_CURRENT_USER clés de Registre sont spécifiques à l’utilisateur.
  • Une tâche ou un gestionnaire de connexions requiert que le compte d’utilisateur actuel dispose des autorisations correctes.

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

  • Méthode 1 : utiliser un compte proxy SQL Server’agent de gestion. Créez un SQL Server proxy agent de gestion. Ce compte proxy doit utiliser une identification qui permet à l’agent SQL Server d’exécuter le travail en tant que compte qui a créé le package ou en tant que compte qui dispose des autorisations requises.

    Cette méthode fonctionne pour déchiffrer les secrets et répond aux exigences clés de 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 du travail utilise le compte proxy correct.

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

  • Méthode 3 : définir la propriété du package SSIS ProtectionLevel sur EncryptSensitiveWithPassword . Modifiez la propriété du package SSIS ProtectionLevel sur EncryptSensitiveWithPassword . Ce paramètre utilise un mot de passe pour le chiffrement. Vous pouvez ensuite modifier la ligne de commande SQL Server l’étape du travail de l’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 propriété afin que le package ne soit pas chiffré et n’essaie pas d’enregistrer les ProtectionLevel DontSaveSensitive 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 futurs packages.

Étapes 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 connexion 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 ProtectionLevel propriété est définie sur EncryptSensitiveWithPassword.
  5. Créez un SQL Server agent de sécurité et une étape de travail. Dans la liste Exécuter en tant que, cliquez SQL Server Service d’agent pour exécuter l’étape du travail. Le texte de l’historique SQL Server travail de l’agent de contrôle affiche des informations semblables à ce qui suit :

Déchiffrer les secrets du package

Le paramètre par défaut de la propriété du package SSIS ProtectionLevel est EncryptSensitiveWithUserKey . Lorsque le package est enregistré, SSIS chiffre uniquement les parties du package qui contiennent des propriétés marquées, telles que les mots de passe, les noms d’utilisateur et les sensitive chaînes de connexion. Par conséquent, lorsque le package est rechargé, l’utilisateur actuel doit satisfaire les exigences de chiffrement pour les propriétés sensitive à déchiffrer. 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 SQL Server travail de l’agent, le compte par défaut est le compte SQL Server service d’agent. Ce compte par défaut est probablement un utilisateur différent de l’auteur du package. Par conséquent, l’SQL Server de travail de l’agent peut charger et démarrer l’étape du 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 avoir besoin pour se connecter.

Important

Examinez le processus de développement et l’environnement pour déterminer les comptes nécessaires et utilisés sur chaque ordinateur. Le paramètre EncryptSensitiveWithUserKey de la propriété ProtectionLevel 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 Dtutil.exe SSIS pour modifier les niveaux de protection à l’aide d’un fichier .cmd et du sous-système de commande de l’agent SQL Server. Par exemple, suivez ces étapes. Étant donné que vous pouvez utiliser l’utilitaire Dtutil.exe dans des boucles et des fichiers de lots, vous pouvez suivre ces étapes pour plusieurs packages en même temps.

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

  2. Utilisez l’utilitaire Dtutil.exe par le biais d’un système d’exploitation (cmd Exec) SQL Server’agent de gestion pour modifier la ProtectionLevel propriété en EncryptSensitiveWithUserKey . Ce processus implique le déchiffrement du package à l’aide du mot de passe, puis le nouveau chiffrement du package. La clé d’utilisateur utilisée pour chiffrer le package est le paramètre de l SQL Server de l’agent de sécurité dans la liste Exécuter en tant que.

    Notes

    É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é.

Assurez-vous que vous avez des informations détaillées sur l’échec du package SSIS

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

À propos de la journalisation SSIS

Les fournisseurs de journaux et de journalisation SSIS vous permet 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 semblables à 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

À l’aide de l’approche de commande du sous-système exec, vous ajoutez des commutateurs de journalisation de console détaillées à 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 SQL Server de l’agent de journalisation.

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 renvoie 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

Références

  • Pour plus d’informations sur un problème similaire, voir « Erreur de chargement » lorsque vous essayez d’exécuter un package services d’intégration SQL Server 2005 dans SQL Server 2005

  • Pour plus d’informations sur l’utilisation de l’utilitaire Dtutil.exe dans les opérations par lots, voir Comment utiliser l’utilitaire dtutil (Dtutil.exe) pour définir le niveau de protection d’un lot de packages SSIS (SQL Server Integration Services) dans SQL Server 2005

  • Pour plus d’informations sur la création de modèles de package, voir Comment créer un modèle de package dans SQL Server Business Intelligence Development Studio

  • Pour plus d’informations sur la sécurité du package SSIS et la propriété, voir la rubrique Considérations de sécurité pour les services d’intégration ProtectionLevel dans SQL Server 2005 Books Online.

Malheureusement, les utilisateurs ne savent pas que les paramètres d’étape du travail d’agent par défaut les placent dans cet état. Pour plus d’informations sur SQL Server proxies de l’agent de SQL Server et le SSIS, consultez les rubriques suivantes dans SQL Server 2005 Books Online :

  • Planification de l’exécution du package dans SQL Server Agent
  • Création de SQL Server proxies d’agent de sécurité