March 11, 2010 at 8:47 am
I want to run job that execute DTS
I use
Operating system command to execute
"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /S "ssss" /U "sa" /p "sss" /N "dsdsds" /W "0" /E /A Conn:8="provider=SQLOLEDB.1; data Source=sdsds initial catalog= xx; User ID= sa; pwd=ttt " /A Path:8="\\dfdsfds\fffff\"
it always give me the following error :
Executed as user: FNS\SYSTEM. DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnError: DTSStep_DTSDataPumpTask_2,
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_2
DTSRun: Package execution complete.
Process Exit Code 1. The step failed.
I don't know why give access denied, while when running it manualy use enterprise manager work very well
ANy suggestion, and sorry if I post question regards SQL server 2000 on this form
I need urgent help please
March 11, 2010 at 10:01 am
You're probably using Windows Authentication in your Transform Data task. When you run the package it uses your login and you have permission to access the flat file, so it works fine. When the package runs in a job, it uses the login of the account that runs the SQL Server Agent service. That account probably doesn't have permission to access the flat file. Change that and you'll fix the problem.
Greg
March 14, 2010 at 1:48 am
Greg Charles (3/11/2010)
You're probably using Windows Authentication in your Transform Data task. When you run the package it uses your login and you have permission to access the flat file, so it works fine. When the package runs in a job, it uses the login of the account that runs the SQL Server Agent service. That account probably doesn't have permission to access the flat file. Change that and you'll fix the problem.
all the connection use sql server user sa and my account is admin on the server
the same error appear any suggestion please
March 14, 2010 at 3:07 am
ali.mahmoud.habib (3/14/2010)
Greg Charles (3/11/2010)
You're probably using Windows Authentication in your Transform Data task. When you run the package it uses your login and you have permission to access the flat file, so it works fine. When the package runs in a job, it uses the login of the account that runs the SQL Server Agent service. That account probably doesn't have permission to access the flat file. Change that and you'll fix the problem.
Does sa account has access to flat file?
all the connection use sql server user sa and my account is admin on the server
the same error appear any suggestion please
Change job owner as your account and make sure you have access to flat file and your account is used in DTS.
March 14, 2010 at 3:28 pm
Ok, you are accesing file system files, right?
Are you running the package from the same workstation/server you created it? Check the connection managers for the filesystem files (flat files/excel workbooks/etc), are these paths accesable from the workstation/server that is running the package from the command line?
if the workstation/server you created the package is diferent from the one intended to run the package I recomend that you create a repository folder (on the workstation/server that the package is stored in) to put the source files and create a job to run the package.
when you start the job, the package will run from the workstation/server that is stored and will have access full access to the source filesystem files.
Best regards
--
Douglas
March 15, 2010 at 1:49 pm
ali.mahmoud.habib (3/11/2010)
Executed as user: FNS\SYSTEM. DTSRun:....I don't know why give access denied, while when running it manualy use enterprise manager work very well
When running it manually, you are using your account settings. When running the package through the job it is running as FNS\SYSTEM.DTSRun. FNS\SYSTEM.DTSRun is probably a local account which does not have access to network files.
Drew Allen
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 15, 2010 at 3:40 pm
drew.allen (3/15/2010)
ali.mahmoud.habib (3/11/2010)
Executed as user: FNS\SYSTEM. DTSRun:....I don't know why give access denied, while when running it manualy use enterprise manager work very well
When running it manually, you are using your account settings. When running the package through the job it is running as FNS\SYSTEM.DTSRun. FNS\SYSTEM.DTSRun is probably a local account which does not have access to network files.
Drew Allen
how to change this user FNS\SYSTEM.DTSRun please to be sa for example , is this applicable
Best regards
March 16, 2010 at 11:55 am
sa won't be able to access files since it's a SQL Server login. Just grant permissions on the file to FNS\SYSTEM.DTSRun.
Greg
March 17, 2010 at 5:28 am
Greg Charles (3/16/2010)
sa won't be able to access files since it's a SQL Server login. Just grant permissions on the file to FNS\SYSTEM.DTSRun.
I created user FNS|System.DTSRUN and give him the privellage on the server but the same error appeared with different user
the sql server agent is under sa running and me is administrator
and the command is:
"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /S "192.168.0.50" /U "FNS\SYSTEM.DTSrun" /E /N "sds_V1.3vb" /W "0" /E
Executed as user: FNS\SYSTEM. ...un OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnError: DTSStep_DTSDataPumpTask_2, 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_2 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_2' failed. Error source: Microsoft Data Transformation Services (DTS) Pac... Process Exit Code 1. The step failed.
any suggestion please
March 18, 2010 at 7:36 am
Caveat: I'm not a security expert, so be careful with this...
It's not clear from your response if you are actually doing what folks are suggesting, so just to make sure, did you grant the SQL Server Agent account and the account "FNS\SYSTEM.DTSRun" access to the *file* (or the folder that contains the file)? If you're running the package as a SQL Server job, then I think it's going to be the SQL Server Agent account (whatever that is) that's going to need permissions, not the FNS\SYSTEM.DTSRun account.
This needs to be done in Windows (i.e. Explorer), not SQL Server. Go to the file or folder that contains the file, pull up Properties on the file/folder (right-click it) and check the Security settings (under "Security" tab). Make sure that the SQL Server Agent account has sufficient priveleges to the file or folder (at a minimum, "Read" permission). Or, better yet, add the SQL Server Agent account to a group on the server that has permissions or create a new group if there isn't one.
I'd carefully reconsider doing this though, as you may very well be opening a gaping security hole on your server. If you're going to futz with security settings try first with the bare minimum you need (e.g. "Read" on the file, or on the folder if it could be multiple files in the folder). Also, strongly recommend you discuss this with your server admins if you have them, before you do anything. It might be better to take the file/folder off the server and put it somewhere else (e.g. a network share) and then lock that down -- although you'll still need to deal with security settings regardless.
Anyway, as I said I'm not a security expert, so be careful with this advice and make sure you're not creating a risk of exposure by doing this, and consult with your server folks too if you can.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply