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?