SQL Server Agent – Proxy/Credential Permissions for SSIS Packages
Just a quick self-help note today……if you ever see the error message below with a SQL Agent Job calling an SSIS Package stored in SQL Server (MSDB database) and leveraging a Proxy built on a specific credential, ensure the credential under the proxy has membership in the db_ssisoperator role in MSDB. Without it (or a bunch of individual object level permissions granted), the credential account doesn’t have permissions to objects needed to get the SSIS Package and execute it. Remember that the credential account must also have the required permissions on all the objects within the SSIS Package as well or you’ll just be off to fix another error after you correct this one….
Could not load package “xyz” because of error 0xC001404A. Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E09 (The EXECUTE permission was denied on the object ‘sp_ssis_getfolder’, database ‘msdb’, schema ‘dbo’.).
Even after ensuring the Proxy was configured and granted SQL Server Integration Services subsystem access, I got this error. It wasn’t until granting the credential account access to MSDB and then membership in the db_ssisoperator role did it work. Seems like a shortcoming in MS’ design to me. Either that or a bug. If you intentionally gave the proxy access to the SSIS subsystem as an administrator, why not take care of granting the permissions necessary to carry out such a feat?
To be honest, this is the first time I’ve run into this error. I don’t recall ever having to grant MSDB permissions before, but if this helps you, glad I ran into it.
-Patrick