Why does our DTS package fail when executed from a separate server under different permissions?

Our DTS package fails to execute when executed from a separate server under different permissions. Here are the specs:
  • Both Server1 and Server2 are on the same domain. Server1 is the SQL Server instance (Enterprise Edition) with SP4.

  • Server2 has a batch script on it that opens DTSrun and executes a DTS package on Server1 to bulkload some XML into a database on Server1 using a trusted connection.

  • Server2 has no instance of SQL Server on it; instead it has the SQL tools installed.

  • The owner executing the batch script (BBB) has appropriate rights in the database as a DBO and is a member of the bulkadmin fixed server role.

  • The permissions (BBB) executing the script are not the DTS package owner (AAA), nor are they members of the SA group.

  • Additionally, Server2 has the necessary files (XBLKLD3.dll) to create the XMLBulkLoad object, with the latest SQLXML and .NET 1.1 installed. However, the execution of the batch script on Server2 fails with the error message that permissions are denied on the createobject SQLXMLBulkLoad when BBB executes the batch script.

  • The executing account (BBB) has permissions to where the files being loaded are kept.

  • When the batch script is executed off a local XP Pro machine in the same domain with BBB logged on, the script executes successfully. The machine only has the SQL tools installed with no running instances.

  • Server2 is a Windows 2000 server.

  • Batch script executes successfully under the DTS package owner (AAA) off of a local XP Pro machine in the same domain. The machine only has the SQL tools installed with no running instances.
Is this a potential case of cross-database ownership chaining (CDOC). However, the MS literature does not make specific mention that this would affect DTS package execution from another server. Specifically the documentation mentions that CDOC is between databases instances that access objects in another database, and in my case there is only one instance on Server1.

Thanks,
Frustrated with DTS

    Requires Free Membership to View

    Login

This is a case for the DTS superheroes -- Allan Mitchell and Darren Green of SQLDTS. They're both friends of mine, SQL Server experts, speakers at the PASS conference and fellow SQL Server MVPs.

In particular, check out their FAQ entry on Package Ownership Issues. This is a real gem that addresses many (if not all) of your issues. And since you're working with DTS packages a lot, but sure to put SQLDTS on your list of favorites!

This was first published in August 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top