Exécuter le package SSIS à partir de la procédure stockée en tant qu'user proxy sans xp_cmdshell

J'essaie d'exécuter un package SSIS via une procédure stockée, mais j'obtiens une erreur Access is denied si j'essaie d'importer un file CSV.

J'ai mis le package dans un travail et l'ai exécuté et cela a fonctionné aussi longtime que j'ai utilisé un count proxy. J'essaie de répliquer ce count proxy à l'appel de procédure stockée sans utiliser xp_cmdshell . J'ai également exécuté ce package dans Visual Studio et il s'est déroulé sans problème.

Mon package SSIS est simple: il importe un file CSV du réseau, convertit datatables en varchar et stocke datatables dans une table.

Même mon administrateur système n'a pas réussi à exécuter la procédure stockée.

Ma procédure stockée ressemble à ceci:

 ALTER PROCEDURE [dbo].[spImportFile] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @execution_id bigint EXEC SSISDB.CATALOG.create_execution @folder_name = 'folder_name', @project_name = 'project_name', @package_name = 'package_name.dtsx', @use32bitruntime = 1, @execution_id = @execution_id output EXEC SSISDB.CATALOG.start_execution @execution_id END

Ma question est, comment puis-je utiliser par programme un user proxy dans cette procédure stockée sans utiliser xp_cmdshell ?

METTRE À JOUR:

J'essaye maintenant d'usurper l'identité de mon user de proxy grâce à Billinkc , mais maintenant je cours dans cette erreur quand j'exécute le package de SSIS:

Le context de security actuel ne peut pas être rétabli. Veuillez basculer vers la database d'origine où 'Execute As' a été appelée et réessayez.

Voici mon code modifié:

 ALTER PROCEDURE [dbo].[spImportFile] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; EXECUTE AS LOGIN = 'domain\credentials' DECLARE @execution_id bigint EXEC SSISDB.CATALOG.create_execution @folder_name = 'folder_name', @project_name = 'project_name', @package_name = 'package_name.dtsx', @use32bitruntime = 1, @execution_id = @execution_id output EXEC SSISDB.CATALOG.start_execution @execution_id -- <<<< ERROR HERE! REVERT END

J'ai testé avec succès EXECUTE AS LOGIN et REVERT sans start_execution en regardant dans une table système à laquelle je n'aurais normalement pas access.

Je me suis rendu count que puisque je vais emprunter l'identité d'un user et que je suis encouragé à utiliser un travail, il sera beaucoup plus facile de faire un travail pour exécuter ce package SSIS sur le server avec un count proxy.

Voici ma solution qui inclut l' exécution d'un travail:

 ALTER PROCEDURE [dbo].[spImportFile] @intStatus int output AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT user_name() -- test before execute EXECUTE AS LOGIN = 'domain\credentials' SELECT user_name() -- test after execute -- Start job DECLARE @job_name VARCHAR(100) = 'JobName' EXEC msdb.dbo.sp_start_job @job_name = @job_name -- Wait for job to finish DECLARE @job_history_id AS INT = NULL DECLARE @intLimit AS INT = 10 DECLARE @intAttempt AS INT = 1 WHILE @intAttempt < @intLimit BEGIN SELECT TOP 1 @job_history_id = activity.job_history_id FROM msdb.dbo.sysjobs jobs INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id WHERE jobs.name = @job_name ORDER BY activity.start_execution_date DESC IF @job_history_id IS NULL BEGIN WAITFOR DELAY '00:00:01' CONTINUE END ELSE BEGIN BREAK END SET @intAttempt = @intAttempt + 1 END -- Check exit code SELECT @intStatus = history.run_status FROM msdb.dbo.sysjobhistory history WHERE history.instance_id = @job_history_id REVERT SELECT user_name() -- test after revert END

Ce code de travail était basé sur cette question, " Exécution du travail d'agent SQL Server à partir d'une procédure stockée et return du résultat du travail "

Constatations
J'ai appris que vous devez GRANT IMPERSONATE ON LOGIN::[domain\ProxyUser] to [domain\credentials] partir de cette source MSDN .

ALTER DATABASE database_name SET TRUSTWORTHY ON est un autre paramètre que sysadmin doit implémenter et cette source MSDN permet d'expliquer l'utilisation.

Remarques :
Cette solution est basée sur le fait que je suis le dbo de la database et j'ai eu un sysadmin accorder l'usurpation d'identité du count proxy à mon groupe de security Windows. J'utilise aussi l'authentification Windows.

J'ai mis à jour la question pour ne pas restreindre l'utilisation des emplois pour quiconque qui travaillait initialement sur cette question. S'il y a une solution qui ne nécessite pas de travail, je serai plus qu'heureux de jeter un coup d'oeil et même de changer la solution acceptée sur cette question.

Je ne l'ai jamais essayé contre un set de references, mais vous pouvez regarder EXECUTE AS

 ALTER PROCEDURE [dbo].[spImportFile] WITH EXECUTE AS 'domain\credentials' AS BEGIN ... END