Successful DTS package fails when scheduled as a SQL Agent job.

The package runs only one ActiveX script, which makes a call to a COM DLL
that uses DAO to run read-only queries against an Access 2000 .mdb file with
workgroup security.

Domain\user1 is assigned to the workgroup, and has full NTFS file and folder
permissions.

The DTS package owner is domain\user1;
the Agent job owner is domain\user1;
the SQLSERVERAGENT service logs on as domain\user1.

SQL Agent security context can write files to the same folder as the .mdb
file.

All paths involved are confirmed UNCs.

Have I missed something? With these security settings, what's the difference
between Agent and DTS security context as far as Access is concerned?

Thank you.

And in what DB role on SQL Server is domain\user1?

Remember if they are not in the sysadmin role the job will execute under the
proxy account

http://support.microsoft.com/default...269074&sd=tech

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Mark Peacock" <fbmarkp (AT) community (DOT) nospam> wrote

I hadn't checked this before you asked about it, but turns out that
domain\user1 (who is a local admin on the server) does happen to be included
in sysadmin role.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

What is the error?

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Mark Peacock" <fbmarkp (AT) community (DOT) nospam> wrote

Thanks for the suggestions.
The final bit turned out to be a hardcoded ref to a mapped drive (in the
..mdw file) after all.
Mapping the drive at the start of the package in an ActiveX task took care
of it:

Dim wsh
Set wsh = CreateObject("WScript.Network")
wsh.MapNetworkDrive "Z:", "\\server\share"
If Not wsh Is Nothing Then Set wsh = Nothing

For production, though, SQLSERVERAGENT can't be a sysadmin. Can one set the
proxy to logon as an ordinary domain user?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Thanks anyway, I found this under SQL Agent properties / Job System ...

Turns out that with the script to map the drive, I was able to reset all
security settings to default.


"Mark Peacock" <fbmarkp (AT) community (DOT) nospam> wrote