SQL Server 2005 Job, SSIS Step, Data Sources Access Denied

This particular issue was interesting.  I was troubleshooting an issue where an individual had rights to run their SSIS Step in a SQL Job.  They could also change variables being passed, add/remove steps in job, almost everything except modify the Data Sources.   When attempting to change the data source you would get an access denied.  If you want to the SQL Server and Remote Desktop in then no access denied. I am going to spoil the ending and tell you it was related to DCOM permissions on the client and server. 

By default (unless part of the Admin group) then users on Windows XP (SQL Server installed) do not have access to make DCOM calls.  SSIS Step editor in the data sources tab make a call via DCOM to get the data source information.  To enable the ability to talk to SQL Server specifically you must enable by following the steps outlined in the following KB article which is related.

Error message when you use SQL Server Management Studio to connect to SQL Server 2005 Integration Services from a client computer: "Connect to SSIS Service on machine '<SSISServer>' failed Access is denied"

https://support.microsoft.com/kb/940232

Comments