SQL 2005 and DTS and sp_cmdshell across the network (whew!) - Integration Services (SSIS) and DTS

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Database Administration » Integration Services (SSIS) and DTS » SQL 2005 and DTS and sp_cmdshell across the network (whew!)

SQL 2005 and DTS and sp_cmdshell across the network (whew!)

Options

Previous Topic · Next Topic

rcnet

Posted: Monday, October 01, 2007 1:59:00 PM

Rank: Newbie

Joined: 10/1/2007
Posts: 1
Points: 3
Location: Georgia

SQL2005 DTS question (yes, I'm migrating packages over).
I'm using master..xp_cmdshell "dtsrun / blah.blah.blah" trying to import an MS Access mdb from Server1 to my SQL server on Server2.

I'm getting an permissions error when I run a DTS package. Error looks like this:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft JET Database Engine
Step Error Description:The Microsoft Jet database engine cannot open the file '\\[network_machinename]\D$\[folderpath]\someAccess.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003051

When the DTS package runs (NOT USING SSMS, but using ASP/ASP.NET code), how come on SQL 2000 it always runs as whatever I set the "Startup service account" (SQL Server properties, 'Security' tab), HOWEVER, on SQL 2005, it NEVER runs as the "Server proxy account" (which should be the equivalent name for the SQL 2000 feature), but ALWAYS runs as the "SYSTEM" account. SYSTEM??? What kind of account is that??? Why, why, why? I setup a nice "admin" like account but SQL2005 never uses that account (keep in mind, when the package involves using "xp_cmdshell" only).

And therefore I'm having permission problems going across the network to the
other server to get the MS Access mdb.

Here's some more information I found out (looking at msdb.dbo.sysdtspackagelog...)
1. Operator = NT AUTHORITY\SYSTEM when I try to load it from ASP/ASP.NET page (using the sp_cmdshell sproc option)
2. Operator = [local_machinename]\[admin_account] when I directly load and run the package in SSMS
3. Operator = [network_machinename]\IUSR_[network_machinename] when I try to load it from an ASP/ASP.NET page (using LoadPackageFromSQLServer function)

Haven't tried the DTS - SSIS migrate option, yet. Can I expect better
control over package network permissions, or is there another gotcha? What
account can I universally use to get across the network between both
machines?

Running Windows 2003 Server. My 2000 -> 2005 migration has turned into a royal pain because of a bunch of
little gotchas. I'm lost in permissions land.....Any insight is appreciated.

Robert

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.