Invocation de SSIS à distance

Comment invoquer SSIS à distance ?

C'est un besoin fréquemment soulevé.

Deux solutions simples peuvent être mise ne place :

· La première consiste à appeler l'utilitaire DTEXEC depuis une procédure stockée qui exécute la commande XP_cmdShell. Pour exécuter cette commande, il est nécessaire de l'activer à travers la procédure stockée système sp_configure. Il faut valider que la procédure " XP_cmdShell" puisse être activée au sein de l’environnement de production. Le lien suivant https://msdn.microsoft.com/fr-fr/library/ms162810(SQL.90).aspx illustre un appel de SSIS avec cette méthode.

· La deuxième consiste à encapsuler l'appel du package à travers un job, lui-même invoqué à travers la procédure sp_startjob. Cette méthode ne permet pas le passage de paramètre.

La première méthode est plus souple surtout pour le passage de paramètre d’exécution du package.

Contraintes

Cependant, pour invoquer SSIS, il faut que SQL Server et SSIS soit sur la même machine. En effet il n’est pas possible de manière simple d’invoquer un process SSIS distant depuis SQL Server.

Nous présenterons donc dans ce cas, l’invocation de SSIS à l’aide de la procédure XP_cmdShell

Fonctionnement de la commande XP_CmdShell

Afin d’activer cette commande, il est nécessaire deNous présenterons donc dans ce cas, l’invocation de SSIS à l’aide de la procédure XP_cmdShell

Etape 1 : Activation de la commande XP_cmdShell

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

Par défaut, pour exécuter la commande XP_CmdShell, il faut être sysadmin sur le serveur SQL Server.

Cependant donner des droits sysadmin à un compte applicatif n’est pas une bonne pratique en termes de configuration de sécurité.

Afin d’éviter d’utiliser ce scénario, un compte de proxy dédié à la commande XP_CmdShell peut être crée. Ce compte correspond à un utilisateur de domaine ou local à la machine.

Par la suite, l’invocation de XP_CmdShell sera réalisée avec le contexte d’exécution

La figure ci-dessous illustre ce mécanisme :

Dans un second temps, il est important d’affecter les bons droits au compte de proxy par rapport aux actions réalisées au niveau du package SSIS (droit en base de données, sur le système de fichiers,…)

Etape 2 : Création d’un compte de proxy

La commande suivante permet d’enregistrer un compte de proxy :

execute sp_xp_cmdshell_proxy_account 'MyDomain\ProxyAccount' , 'Mypassword1'

Il faut bien entendu que le Windows « MyDomain\ProxyAccount » soit crée sur la machine auparavant.

Etape 3 : Affectation des droits d’exécution de la procédure xp_cmdshell

L’utilisateur (Login SQL) doit être enregistré dans la base master. Par la suite, il est possible de donner des droits sur la procédure stockée xp_cmdshell à notre utilisateur applicatif.

Ci-dessous un exemple de script qui autorise l’utilisateur applicatif à exécuter la procédure xp_cmdshell

use master

GRANT EXECUTE on xp_cmdshell to MyApplicationUser

Etape 3 : Execution du package

L’utilisateur applicatif peut par la suite exécuter le package SSIS à l’aide de l’instruction SQL suivante :

DECLARE @returncode int

EXEC @returncode = xp_cmdshell 'dtexec /f "C:\ ProxyAccountTest\ProxyAccountTest\Package.dtsx"'

GO