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

S’applique à : SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Standard

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 avec succès 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 pour laquelle le package a échoué. Les raisons pour lesquelles le package a échoué peuvent être les suivantes : 
  • Le compte utilisateur appliqué pour exécuter le package sous SQL Server Agent diffère de l’auteur du package d’origine.
  • Le compte utilisateur ne possède pas les autorisations requises pour établir des connexions ou accéder à des ressources en dehors du package SSIS.
Le package n'est pas pris en charge 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 actif ou le compte d’exécution diffère de l’auteur du package d’origine et que le paramètre de la 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 une sécurité intégrée échoue car l’utilisateur actuel ne possède pas les autorisations requises.
  • L’accès au fichier échoue car l’utilisateur actuel ne possède pas les autorisations requises pour écrire sur le partage de fichiers auquel le gestionnaire de connexions accède. Par exemple, ce scénario peut se produire avec des fournisseurs d’informations de texte qui n’utilisent ni nom de connexion ni mot de passe. Ce cas de figure peut également se produire avec les tâches qui dépendent du gestionnaire de connexions de fichiers, par exemple, une tâche de système de fichiers SSIS.
  • Une configuration de package SSIS basée sur le registre utilise les clés de Registre HKEY_CURRENT_USER. Les clés de Registre HKEY_CURRENT_USER sont spécifiques à l’utilisateur.
  • Une tâche ou un gestionnaire de connexions requiert que le compte utilisateur actuel dispose d’autorisations suffisantes.
Méthode 1 : Utilisation d’un compte proxy SQL Server Agent
Créez un compte proxy SQL Server Agent. Ce compte proxy doit utiliser une autorisation qui permet à SQL Server Agent d’exécuter le travail en tant que compte à l’origine du package ou possédant les autorisations requises.

Cette méthode permet de déchiffrer des secrets et de satisfaire aux exigences de clé par utilisateur. Toutefois, la réussite de cette méthode peut être limitée dans la mesure où les clés utilisateur du package SSIS concernent l’utilisateur actuel et l’ordinateur actuel. Par conséquent, si vous déplacez le package vers un autre ordinateur, cette méthode peut encore échouer, même si l’étape de travail utilise le compte proxy correct.


Méthode 2 : Définition de la propriété ProtectionLevel du package SSIS sur ServerStorage
Redéfinissez la propriété ProtectionLevel du package SSIS sur ServerStorage. Ce paramètre stocke le package dans une base de données SQL Server et permet le contrôle d’accès par l’intermédiaire des rôles de base de données SQL Server.

Méthode 3 : Définition de la propriété ProtectionLevel du package SSIS sur EncryptSensitiveWithPassword
Redéfinissez la propriété ProtectionLevel du package SSIS sur 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 y inclure ce mot de passe.


Méthode 4 : Utilisation des fichiers de configuration du package SSIS
Utilisez les fichiers de configuration du package SSIS pour stocker des informations sensibles, puis enregistrez ces fichiers de configuration dans un dossier sécurisé. Vous pouvez ensuite redéfinir la propriété ProtectionLevel sur DontSaveSensitive pour que le package ne soit pas chiffré et ne tente pas d’enregistrer des secrets sur 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éation d’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 suivants.

État


Ce comportement est inhérent au produit.

Informations avancées


Procédure pour reproduire le problème

  1. Ouvrez une session en tant qu'utilisateur non membre du groupe SQLServer2005SQLAgentUser. 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 dans le fichier msdb local à l’aide de la chaîne suivante : ‘Windows Authentication’ -SQLSourceType: "Direct Input" -SQLStatement: "sp_who"
  3. Lancez le package pour vous assurer qu’il s’exécute avec succès.
  4. Notez que 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 Exécuter en tant que, cliquez sur Service SQL Server Agent pour exécuter l’étape de travail.
Le texte de l’historique des travaux de SQL Server Agent affiche des informations similaires aux suivantes :

Déchiffrement des secrets de packages

Le paramètre par défaut pour la propriété de package SSIS ProtectionLevel est CrytySensitiveWithUserKey. Lorsque le package est enregistré, SSIS chiffre uniquement les parties du package qui contiennent des propriétés marquées « sensibles », 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 des propriétés sensibles à déchiffrer. Cependant, l’utilisateur actuel ne doit pas satisfaire aux exigences de chiffrement pour charger le package. Lorsque vous exécutez le package via une étape de travail de SQL Server Agent, le compte par défaut est le compte Service SQL Server Agent. Ce compte par défaut est probablement un utilisateur différent de celui de l’auteur du package. Par conséquent, l’étape de travail de SQL Server Agent peut charger et commencer à exécuter l’étape de travail, mais le package échoue car il ne peut pas établir de connexion. Par exemple, le package ne peut pas établir de connexion OLE DB ni de connexion FTP. Le package échoue car il ne peut pas déchiffrer les informations d’identification dont il doit disposer pour se connecter.


Important Considérez le processus de développement et l’environnement pour déterminer les comptes requis et utilisés sur chaque ordinateur. Le paramètre EncryptSensitiveWithUserKey de la propriété ProtectionLevel est puissant. Il ne doit pas être négligé au risque d’entraîner des complications de déploiement au début. Vous pouvez chiffrer les packages lorsque vous êtes connecté au compte approprié. Vous pouvez également appliquer l’utilitaire d’invite de commande SSIS Dtutil.exe pour modifier les niveaux de protection en utilisant un fichier .cmd et le sous-système de commande de 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, appliquez ces procédures pour plusieurs packages en même temps.
  1. Modifiez le package que vous souhaitez chiffrer en utilisant un mot de passe.
  2. Utilisez l’utilitaire Dtutil.exe via une étape de travail SQL Server Agent Operating System (cmd Exec) pour redéfinir la propriété ProtectionLevel sur EncryptSensitiveWithUserKey. Ce processus consiste à déchiffrer le package en utilisant le mot de passe, puis à le rechiffrer. La clé d’utilisateur appliquée pour chiffrer le package est le paramètre de l’étape SQL Server Agent dans la liste Exécuter en tant que.

    Remarque Dans la mesure où la clé comporte le nom d’utilisateur et le nom de l’ordinateur, l’effet d’un déplacement des packages vers un autre ordinateur peut être limité.

Assurez-vous de disposer d’informations d’erreur détaillées sur l’échec du package SSIS.

Au lieu de vous contenter des détails limités de l’historique des travaux de SQL Server Agent, utilisez la journalisation SSIS pour vous assurer de disposer d’informations d’erreur complètes 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 du sous-système SSIS.

À propos de la journalisation SSIS

La journalisation et les fournisseurs d’informations SSIS vous permettent de saisir des détails sur l’exécution et les échecs d’un package. Par défaut, le package ne contient pas de données. Vous devez configurer le package pour enregistrer les informations. Lorsque vous configurez le package pour enregistrer les informations, des informations détaillées similaires aux suivantes s’affichent. Dans ce cas, vous saurez qu’il s’agit d’un problème d’autorisation :

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". La connexion n’est peut-être pas configurée correctement ou vous ne possédez peut-être pas les autorisations adéquates pour cette connexion.

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

Avec l’approche de commande du sous-système exec, vous pouvez ajouter des commentaires de journalisation de console à la ligne de commande SSIS pour appeler le fichier exécutable de ligne de commande DEXEC.exe SSIS. Vous utilisez en outre 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 SQL Server Agent.

L'exemple suivant illustre la ligne de commande :
 
dtexec.exe /FILE "C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V  /CONSOLELOG NCOSGXMT 


La journalisation /console renvoie des détails similaires aux 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:34End Log 

Références


Pour plus d'informations à propos d'un problème semblable, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.

904800  Vous recevez un message d’erreur « Erreur de chargement » si vous essayez d’exécuter un package SQL Server Integration Services dans SQL Server 2005

Pour savoir comment utiliser l'utilitaire Dtutil.exe dans des opérations par lot, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.

906562 Comment utiliser l’utilitaire dtutil (Dtutil.exe) pour définir le niveau de protection d’un lot de packages SQL Server Integration Services (SSIS) dans SQL Server 2005

Pour savoir comment créer des modèles de package, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.

908018 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é 2005 ProtectionLevel, consultez la rubrique « Considérations de sécurité relatives aux services d’intégration » dans la documentation en ligne de SQL Server 2005.

Malheureusement, les utilisateurs ne savent pas que les paramètres par défaut d’étape de travail de l’Agent les placent dans cet état. Pour plus d'informations sur les proxys SQL Server et SSIS, reportez-vous aux 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 proxy SQL Server Agent