在不同帐户下使用 T-SQL 运行 SSIS 包 [英] Run SSIS package using T-SQL under different account

问题描述

我通常使用 Sql 代理作业和代理用户运行 SSIS 包，如下所述:

您的 SSMS 安装位置取决于版本，但请在以下路径中尝试使用 140 的各种十位数增量

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe

对我来说，runas 的缺点是我无法自动将我的凭据传递给它.我看过有关使用自动热键之类的文章，但从来没有遇到过.

对于需要定期运行的东西，最干净/最简单的方法是使用带有代理的 sql 代理，或者您可以使用 Windows 任务计划程序并将其创建为目标用户.一次性执行，我可能会使用 runas 方法.

I normally run an SSIS package using a Sql Agent Job and a proxy user as described here: https://www.sqlservercentral.com/articles/run-an-ssis-package-under-a-different-account

I now need to run the same package using the same proxy user using T-SQL. I've been trying to use the [catalog].[create_execution] and [catalog].[start_execution] procedures to do this but there doesn't seem to be a way to specify a user.

How do I execute a package as a different user?

Is my best recourse the use of T-SQL to execute a SQL Agent Job that is configured to use the proxy user instead?

解决方案

I don't know how agent actually works to make proxy users work - especially with regard to SSIS packages.

In a "normal" sql session say in SSMS, if I wanted to run a query as another user

EXECUTE AS USER = 'TurgidWizard';
SELECT USER_NAME() AS WhoAmI;
REVERT;

That code would allow me to impersonate you until I hit the REVERT call.

But, if you swap out calls to create_execution/start_execution you'll run into the same issue as trying to use a local sql server user runs into with using the methods in the SSISDB - it doesn't work. The methods in the SSISDB all run checks before they begin to ensure users have the correct access level and there isn't impersonation going on. Because once those methods start running, they themselves do impersonation so I guess that doesn't work well.

How can I run a package using tsql under a different account? I would start SSMS/sqlcmd under the credentials using RUNAS For example, the following will open a new command window as you.

runas /netonly /user:corpdomain.com\turgiwizard "cmd"

From there, things I do will be under the aegis of your user so I could run sqlcmd calls like

sqlcmd -S TheServer -d SSISDB -Q "EXECUTE catalog.create_execution ...;"

Mouse click will be Ctrl+Shift+right click executable.

Your SSMS install location is version dependent but try various ten digit increments of 140 in the following path

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe

The downside for me with regard to runas is that I could not automate getting my credentials passed into it. I've seen articles about use auto hot key and such but never had any luck with it.

Cleanest/easiest approach for something that needs to run regularly is to use sql agent with a proxy, or you could use Windows Task Scheduler and create it as the target user. One off executions, I'd likely use the runas approach.

这篇关于在不同帐户下使用 T-SQL 运行 SSIS 包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！