Run Ssis Package Using T-sql Under Different Account

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 n

Solution 1:

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

EXECUTEASUSER='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.