We have a stored procedure which calls an SSIS package from the SSISdb database.
It works when run as myself using my domain account, however the VB6 application which will call this connects to the databases via a SQL account.
SSIS needs to be run via a windows authentication account, so to get around changing the code to connect as a windows account, which is a big task apparently.. within the stored procedure that calls the ssis package, I added the "WITH execute as 'domain login'".
When you then run the stored procedure it comes up with the error:
Msg 916, Level 14, State 1, Procedure "SP", Line 17 [Batch Start Line
10] The server principal "windows account" is not able to access the
database "SSISDB" under the current security context.
It has all of the correct permissions to the SSISDB, it has been granted ssisadmin under the msdb database.
I have also checked that its not an orphaned user against the databases – that's all fine.
I even made the login sysadmin briefly to test that – and the same error still appears which is strange.
I confirm that the SSIS package does not access any file shares or anything like xp_cmdshell.
It works if I log in as the domain account that's in the EXECUTE AS, so it seems it doesn't like the EXECUTE AS statement.
Any ideas what is going on here?
Best Answer
"EXECUTE AS" works by default only within one database.
You can overcome this by enabling cross-database ownership chaining. But this implies security issues!
Please have a look at books online: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server
An alternative solution is to move the calling procedure into the SSIS database and grant the VB script user execution permission on that particular procedure. Within that procedure you can then use "EXECUTE AS" to call the second procedure with the privileges needed.