Options for DTS package ownership

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Options for DTS package ownership

Author

Topic

djpaz25
Starting Member

4 Posts

Posted - 2004-07-29 : 10:14:16

Running SQL Server 2K. Have a DTS package that copies data from an Access file that is on a network share into a SQL database. Runs interactively, fails when scheduled with:

Step Error Source: Microsoft JET Database Engine
Step Error Description:The Microsoft Jet database engine cannot open the file '\\cnxlib\apps$\labtest\winoils\paztest.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

I understand the problem is that the interactive job runs under my account, while the schedule job runs under the SQLSERVERAGENT service account, which is a local administrator account that doesn't have access to the network share.

Is there a best practice for this situation?

I can run the SQLSERVERAGENT under my domain account, which causes the DTS package to run properly. However, that means that all DTS packages and scheduled jobs run under my account. I don't think that is a good thing.

Is there a way to put account information into a specific package so that it still runs as a local admin but has permissions for the network share? I'm using the graphical designer and don't see a way to store username/password as part of the connection object.

Thanks for any thoughts.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-08-10 : 01:17:25

Hi,

Hope this helps. http://www.sqldts.com/default.aspx?212

phxr68dao
Starting Member

1 Post

Posted - 2004-08-24 : 15:31:55

To elevate this problem do the following:

1. Create a domain account specifically to run jobs in sql server (ie. agentsql)
2. Give this account domain priviledges that will allow it to access the paths on the network that are required for you jobs.
3. Give this account local adminstrator priviledges
4. Go into the services administrator program
5. Find the service that runs sqlagent. It will start sqlagent.
6. go to the logon tab for this service.
7. Change the login information from local system account to use this account.
8. Add in the account username and password you created in step one.
9. save the changes.
10. Rerun the job that is giving you a problem.

I have encountered this type of problem many times. This should solve you problem. You are being limited by the scope of the user running the job. Your account has domain priviledges and access. The local system account has administrator priviledges and acces on the local machine, but is not configured for network rights and priviledges.

Let me know it this does not work, but I am pretty sure it will take care of the problem.

Please provide feedback either way. :-)

Justus

Justus maybe blind,
But Justus is always swift and fair.

djpaz25
Starting Member

4 Posts

Posted - 2004-09-20 : 09:30:19

Thank you for the post, Justus. It isn't easy (by possibly doable) for me to create a domain account specifically for this. I've been using my regular domain account as the account that sqlagent runs as. That account has local admin privs and can access the network. The problem is that I have to keep changing the login info for sqlagent every time I have to change my password. I guess that is something I just have to live with.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources