Executing SSIS Package with SQL Authentication
This is what I did, maybe it can help someone.
First step, I created an SQL Authentication login as sysadmin and I allowed this login to impersonate other logins.
And then, I inpersonate a Windows Authentication login that is sysadmin too before calling SSIS packages. To execute scripts as another login, I used TSQL EXECUTE AS
. (check the example below)
For instance: server\winuser
with Windows Authentication as sysadmin, and a SQL Authentication login sqlauthuser
as sysadmin too.
Enable the "Inpersonate Any Login" for sqlauthuser
. Login (or use a connection string to connect) as sqlauthuser
then impersonate server\winuser
to be able to call SSIS packages or procedures:
EXECUTE AS LOGIN = 'server\winuser';
-- call ssis packages
-- call procedures that uses ssis packages
Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15