Welcome to EMC Consulting Blogs

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).

Jamie.

I have a parent package that is calling 4 child packages. The child packages are stored in the msdb. Two of the child packages are being called InProcess and the other two are called OutOfProcess

When I  run this from BIDS, the InProcess packages always runs fine, but the OutOfProcess packages fails. I switch the OutOfProcess to InProcess for the two failed, it runs fine again.

I can't seem to nail the problem here - I am thinking something is preventing the additional threads from being opened up in the OS.

And each test run produces different and fairly vague error messages:

1. Error: Error 0x8002801D while preparing to load the package. Library not registered

2. Error: Error 0x80004005 while preparing to load the package. Unspecified error .

Environment

(64 Bit Win2008 EE, SS2005 EE, Dual 6-Core Proc, 32GB)

Any idea what is going on here?

Thanks

Anil

Oh and one more point to add to this to the above question: Instead of executing the parent package in its entirety, if I click on the 'outofprocess' childpackage call and execute just that, it runs just fine. Run the entire parent package all at once, and the outofprocess fails.

If I add precedence constraints between the child packages, then it runs to completion as well.

Any help is appreciated.