Post reply

SQL 2000 DTS runs ok, when called from a JOB access denied

  • May 9, 2017 at 2:51 am

    #149363

    Hi,
    My environment is the next:
    - Windows 2012 R2 Datacenter / SQL 2008 R2 ('Default' instance) + SQL 2000 ('SQL2000' instance)
    I have a DTS en SQL 2000 running ok when I try to run the DTS directly.
    But when I schedule the DTS on a job, then the job fails because 'access denied' to the xls file.
    This is the code that fails in the DTS VBScript task:

    Dim oFS
    Set oFS = CreateObject("Scripting.FileSystemObject")
    'STEP 1 WORKS GOOD
    '------------------------------------------
    Set a = oFS.CreateTextFile("D:\Folder1\file1.txt", True)
    a.WriteLine("Create the file each time.")
    a.Close
    'STEP 2 WORKS GOOD
    '------------------------------------------
    Set b = oFS.OpenTextFile("D:\Folder1\file2.txt", 8, True) '8 => Append Text
    b.WriteLine "Open file and append text..."
    b.Close
    'STEP 3 FAILS
    '-----------------------'------------------------------------------
    Dim oExcel
    Dim oBook
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks.Open("D:\Folder1\file1.xls") '--> This line fails because access denied

    The SQL Service account and the SQL Agent account is the same windows account with admin rights on the server, and sysadmin rights on SQL Server.
    I do not understand why is failing when called from a job. Should be something related to rights, but note that in the 1st STEP I can create a new TXT file in the same folder.
    And in the 2nd STEP I can open an existing TXT file, append text, and save it.
    So, Why the Workbook.Open fails, only when called from a job? How could I solve this?
    Thanks for your help.
    Regards,

    EDIT:
    I've found some tips on internet related to use proxy accounts to solve this kind of permission problems.
    But, in theory, you only need to create a proxy account when the Login that executes the Job is not in the sysadmin group and the process uses CmdExec.
    In my case the user is a Windows User that is member of Administrators on Windows and member of Sysadmin group on SQL Server.

  • May 12, 2017 at 1:34 pm

    #1942395

    I found the solution.
    https://superuser.com/questions/579900/why-cant-excel-open-a-file-when-run-from-task-scheduler
    Regards,

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply