Problems with SQL Server agent

Hi,
I have som problems with a SQL Server agent job.
I have created a DTS package that extracts data from database and saves
the data to a csv file on the
network.
When I run the DTS package manually the file is created and all is OK.
But when I create a scheduled SQL Server agent job to run my DTS
package I get the following error message:
"Executed as user: SERVER\SYSTEM.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
(80004005)
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 D
TSRun: Package execution complete. Process Exit Code 1. The step
failed."
I understand as much as the user running the job hasn't got access to
the file...
So.. how do I change the user that executes the job'
Thanks for your help.
MikaelMcA wrote:
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> 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 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>
Are the SQL Server and SQL Agent services configured to run as "Local
System" or as a domain user? When you run the DTS package manually, it
runs using the permissions that your personal login has. When it runs
from a job, it uses the permissions that SQL Server has. To access
network resources, SQL must be running as a domain user, and that domain
user must have the necessary permissions to access the resource.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Check the services application. Is the SQL Server Agent service running as
a domain user account or as the local system?
You need to make sure that the account that the SQL Server Agent is running
under has permissions to the file/directory.
Keith Kratochvil
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1160055899.543735.146020@.i42g2000cwa.googlegroups.com...
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> 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 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>|||Hello
It looks like the SQLSERVERAGENT runs with the Local System account.
So if I change the "Log On As" user to at user with rights to my file
I'll be OK?
I'll try that thanks..
Mikael|||McA wrote:
> Hello
> It looks like the SQLSERVERAGENT runs with the Local System account.
> So if I change the "Log On As" user to at user with rights to my file
> I'll be OK?
> I'll try that thanks..
> Mikael
>
There's more to consider than whether the user has rights to your file,
but yes, that's the solution to your problem. You need to read about
Service Accounts in Books Online before changing anything, to make sure
you understand the requirements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com