SQL Server 2000 - DTS package fails when run thru job scheduler

[jjkatz]

I have a DTS package set up in SQL Server 2000. One step connects to a Microsoft Access database to obtain data. The package succeeds if I run it manually, but fails if I schedule the package and try to execute the job. The error message I get is:

The Microsoft Jet database engine cannot open the file '[file path]\[file name].mdb'. It is already opened exclusively by another user, or you need permission to view its data.

I have tried it by mapping a drive letter on the server, and also by using the UNC path of the Access database, which resides on a different server. In both cases the package succeeds if I run the package directly, but fails if I use the job scheduler to run it. I have also confirmed that the file is not in use when I get this error.

Any ideas?

[rmiao]

Is it remote file? Does sql agent service account have permission to read that file?

[jjkatz]

It is a remote file. I'll check the permissions, I bet that's it. I will post back to confirm once I know.

Thanks!

[jjkatz]

We gave the SQL Agent account full rights on the folder that the Access database is in, but the job still failed with the same error message.

[rmiao]

Who's job owner?

[jjkatz]

The job owner is the same account (Windows logon) that created the package. However I noticed that the startup account for the SQL Agent is a different account (sqlsrvr). I've asked the DBA who manages this job to try changing the startup account, alternately we'll try adding the sqlsrvr account to the folder the Access db is in. I'll post back and let you know how it goes.

[rmiao]

So you start sql agent with domain account? If job owner is not sysadmin, sql will run job under localsystem that has no remote permission at all. Need proxy in this case.

[jjkatz]

I believe we have resolved the issue. We gave both the SQLAgent and sqlsrvr accounts permissions on the folder the Access db is in, and the job ran successfully when executed manually. We've got it scheduled to run again in the morning (unattended). Looks like this one is solved, thanks!