January 7, 2003 at 4:58 pm
I'm trying to schedule a DTS job to run a Select into statement against a database.
The SQL 2000 database lives on an NT server and I connect to it using a Win2k desktop. We use Windows authentication.
I have sysadmin rights on the database and full rights on the server.
When I make myself the owner of the job, it runs under the context of the startup account and all is good.
I have a user who has owner rights on the database. I setup a proxy account which has sysadmin rights and I gave this account read and write access to the C:\temp drive on the server.
I made my user the owner of the job and ran it.
Here is the error message:
Executed as user: DHS-BAY\SQL05Proxy. DTSRun: Loading... Error: -2147024891 (80070005); Provider Error: 0 (0) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.
I read the Q269074 article from Micro$oft.
Can anyone help?
"I hope you enjoy your retirement as much as I will."
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 7, 2003 at 5:07 pm
What account is SQL Server Agent running under and what permissions does that account have?
Michael Weiss
Michael Weiss
January 7, 2003 at 5:12 pm
The startup is a domain account with local admin rights on the server.
BTW, I just gave the proxy acct local admin rights and ran the job successfully as my user. Therefore read and right access on the c:\temp drive must not be sufficient. Any clues?
"I hope you enjoy your retirement as much as I will."
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 8, 2003 at 4:06 am
To know for sure create another package from the first using save as but remove the need to access the drive. Then set the use back and see what occurrs. If does not happen then the drive access is the issue, if the error repeats then is some other.
January 8, 2003 at 9:28 am
I did this early on in the process. Drive access is the issue. Today I will use C:\WINNT\temp and see what happens.
"I hope you enjoy your retirement as much as I will."
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 8, 2003 at 11:28 am
That did not work. The Microsoft Article says The Proxy Account must have read and write access to the temp folder of the Account the SQl Server Agent is running under.
Of course this only applies to Win2k servers.
I am busy testing various scenarios to try to solve this. If I find an answer that does not require giving the Proxy acct Local Admin rights on the server, I'll post the solution.
"I hope you enjoy your retirement as much as I will."
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 15, 2003 at 10:44 am
Final solution to this from Micro$oft.
In the NT environment, when permitting developers without sysadmin rights to schedule DTS jobs, the only way to determine which files on the server the proxy account needs access to is to download a utility called filemon from http://www.sysinternals.com and run it while the dts job is running!
Very ugly indeed! In my case the proxy account required read and write access to the c:\winnt folder. In other words, my developer could write jobs which overwrote key components of the Winnt O/S.
In the Win2k environment the q269074 article
says the proxy account requires access to the temp folder the sql server account is running under.
C:\documents and settings\<account>\local settings\temp.
I was delaying our migration in win2k on that server. Maybe it is time to reconsider.
"I hope you enjoy your retirement as much as I will."
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply