SQL Server Performance Forum – Threads Archive
DTS Package Security Problem
Hello! I am new to using DTS (SQL Server 7.0). I created a package that runs successfully when ran locally but fails when scheduled as a job. The package queries 2 tables in SQL Server, uses transform data task to create a destination file locally, then copies the file to a file server. THANKS FOR YOUR HELP! [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />Here are the details:<br />DTSRun: Loading… DTSRun: Executing… DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Check the account under which SQL Server Agent is running. The account should have permission to access the File Server share. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
That worked!!! I added the sql agent account and added it the to Admin group. THANKS A BUNCH! [:I] Here’s what’s weird: Just for kicks, I deleted the sql agent account from the file server to see if the job would fail again. However, the job runs just fine. The job owner had admin rights to the file server before and after granting rights to the sql agent account. Can you explain this?
quote:Originally posted by gaurav_bindlish
Check the account under which SQL Server Agent is running. The account should have permission to access the File Server share.
Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
The permissions are cached.<br /><br />Just for fun, reboot the file server and see if it still works. [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]<br /><br />Derrick Leggett<br /><br />
]]>