Post reply

Permission Denied while Executing DTS Package thru Job

  • September 7, 2005 at 10:05 am

    #66894

    Hi Gurus

    I have a Package that has an "Execute Process Task" executing and VB6 exe.

    This exe has code to export data in a text file which has to be created on a different machine.

    The problem is that when the package is executed thru a scheduled Job, it gives permission denied error (in vb error handler) as the file has to be created on a different machine.

    If I execute the package manually the file is created.

    Also, if I run the EXE code seperately thru VB the file is created.

    The Permission denied error comes in the VB error handler and happens only when the package is executed thru a scheduled Job.

    Please help

    Sanjay Masawan

  • September 7, 2005 at 10:09 am

    #588150

    The job runs under the context of the owner of the job in Sql Agent. (Opent the job in the owner dropdown in the general tab)

    The owner needs to be a user of sufficient rights to access ALL resources that are used within the job.

    So for example if the job has tasks that need to access the disk on other servers then the owner needs to have access to those other servers.

    BTW its not a good idea to make yourself as the owner, because sql agent will remember your password so if you do a password change sql will continue to run with your old password causing your account to get locked out.

     

  • September 7, 2005 at 10:26 am

    #588156

    How can we make the owner access the other machines. Can I make "sa" as the owner. how to grant full permissions.

    Also, as u said that "its not a good idea to make yourself as the owner", what should be the best practise.

    Thanks

  • September 7, 2005 at 3:16 pm

    #588267

    To give access to other machines you need to make a domain account, that is used only for services, (Cannot log on interactively)

    Set sql server service, and agent service to run under the domain account (Not local Account)

    and give the domain account access to all machines required.

    SA is a sql logon/password that resides only on the specific machine, you can use pass thru authentication (sa exists on all machines, make passwords all the same accross all machines, but this is not a good security practice)

     

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_runsetup_9ann.asp

     

Viewing 4 posts - 1 through 3 (of 3 total)

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