blogs.conchango.com

This post started out as a comment on one of Jamie's recent posts , however, I realised that the

Hi Jamie,

I'm not familiar with the specific COM component you mention, but this type of issue can typically be resolved in a more secure manner by granting the relevant account the required rights (in this case 'Local Activation') via 'Component Services'.

I've posted a more detailed description here: http://blogs.conchango.com/jamesdawson/archive/2007/09/06/Configuring-DCOM-Security.aspx

Jamie,

By the Service account that runs the packages do you mean, the Service account that runs SQL Agent Service.

If so this should not be a member of the local Administrators group as this goes against the 2005 Security setup and principle of least privilege.

Configuring the proxy account to have Administrator rights does not keep with a least-privilege security model.  You could have simply granted the proxy account Activate/Access permissions to the MsDtsServer Component as mentioned by james.dawson  Note that service packs and cumulative updates will overwrite this DCOM setting.

When executing an SSIS package from a SQL Agent job as a proxy on a cluster, and the proxy does not belong to the local administrators group, you have to do the following:

- grant Administrator rights to the cluster service (KB316881)

- subinacl /service MSDTC /grant="authenticated users"="F"

- sc sdset SCMANAGER D:(A;;CCLCRPRC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;MU)(A;;CCLCRPRC;;;LU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)

The section "A;;CCLCRPRC;;;AU", is the portion that changes the default DACLS on the SC Manager object.

Without doing the above, you will get access is denied or not running error messages on MSDTS.

granted to proxy account DCOM permissions for DTS Package Host component.

Out of process packages worked free of issues, ran under proxy account.

I solved this problem by using the Component Services manager to give the account running the package with the packages running out of process execute permissions for DTS Package Host  I gave that account Launch and Activate remote and local and access permissions.  Now it runs to completion.  Here's how you get there:

Start>Programs>Administrative Tools>Component Services

Open the Computers>My Computer>DCOM Config and find the DTS Package Host

Right click on the DTS Package Host, select Properties, Security

Select Customize for Launch and Activiation Permissions and for Access Permissions

Add the account you are using to run the SSIS package and give it Local and Remote Activiation and Local and Remote Access

I tried putting  the local account in the SqlServer2005DTSUsers group but that did not grant the permissions (however I did not do a reboot).