Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Unanswered: dts works but run same dts via sql job failed

    hi all,
    i posted this somewhere else but i think here is the right group.

    i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \\128.1.1.1\dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.

    thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.

    now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,

    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 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

    i really hope and appreciate if someone out there can help me out. thanks!

    ps: pls let me know if more clues needed from my environment settings.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The account that executes the scheduled job does not have the same permissions as you. You need the job to be run with an account with the necessary permissions (same as your perhaps) which also has a non-changing password.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I had a similar problem which was caused by the account used by the sqlagent service. I changed it to a domain account with the appropriate privs, and the problem was resolved. Whey you exec the DTS job via the designer, it is using your credentials vs. the agent's credentials.

  4. #4
    Join Date
    Nov 2007
    Posts
    7
    thank u both for yr reply...

    what do u mean by change it to domain account? i tried to start up the sql server agent using THIS ACCOUNT, which taking sql id that i created as sysadm and the name and password is the same username/password i log on to the server. the reason to do this to make the job using the same logon id as i run it thru dts designer. but now i am getting this error:

    DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. 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.

    i think my problem now, i am not sure when running the dts thru job, from where the job is taking username/password to connect to the remote server location. appreciate further help...thnks much.

  5. #5
    Join Date
    Nov 2007
    Posts
    7
    hi again...

    i came across some suggestions on the net like this:
    Make sure that the SQL Agent uses the same account as SQL Server service.

    how to check the SQL Server service account?

    really need yr expertise here..thanks.

  6. #6
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by hameiza0304
    hi again...

    i came across some suggestions on the net like this:
    Make sure that the SQL Agent uses the same account as SQL Server service.

    how to check the SQL Server service account?

    really need yr expertise here..thanks.

    start -> run -type 'services.msc'
    Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.

  7. #7
    Join Date
    Nov 2007
    Posts
    7
    Quote Originally Posted by jeebustrain
    start -> run -type 'services.msc'
    Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.

    hi again...
    i already changed the sql services to use logon username/password to be the same as sql agent. means both sql agent and sql services are using domain account now, no longer system account. but unfortunately, i am stil getting the same error...

    anymore ideas can help me out here? please please....

    tqvm.

  8. #8
    Join Date
    Nov 2007
    Posts
    7
    hi all again...
    maybe i forgot to tell that my remote server i am connecting to is UNIX server (is that made a difference anyway?). well...just to test out, i just gave it a try to point to another remote server running on Windows. i shared the same folder and give sufficient privs while my sql server is stil using same domain account for sql agent as well as MSSQLSERVER services. then i run the job, it run successfully!

    1.now i wld like to ask yr expertise here to guide me - how do we share a folder in UNIX to enable another server to connect to it? right now i just give permission read/write/execute to the folder. and it seems giving the above error - logon error/bad username or bad password.

    2.how do we give permission to a server running on local? (not on network). the reason i asked is - normally its easy to give permission to some users on same network. is it by IP will work?

    hope u all out there can help! thanks....really appreciate yr help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •