locked
delete files with SQL Agent job RRS feed

  • Question

  • Hello

    I am using SQL Server 2005 Standard edition. I have created a maintenance plan creating backup. After this step I should delete files from a location on another server in order to make space for the new backup. While executing this step I am getting the following message:

    [136] Job aaa reported: The process could not be created for step 2 of job ... (reason: 2)

    I have tried to put the Ip address in the file path, to map network drive, to give everyone full control..no success.

    This step I am configuring it as type: Operating System (CmdExec), then Run as : Sql Agent Service Account

    Please help how do I delete files on other server in same domain with SQL Agent Job

    Thursday, March 21, 2013 11:50 AM

Answers

All replies

  • It would be good to have details on the step that is failing.  Is it a Operating System, T-SQL, or other type of step?

    You mention giving "everyone full control".  Does that mean the users?  The SQL Agent Service Account?  The computer account?  This is almost certainly a permissions issue and once you find out which login is making the access then it will clear up pretty quickly.

    If you have xp_cmdshell enabled, you might add a test job step that does:  exec xp_cmdshell 'dir \\url\path\*.*'

    That will let you see if your service account has rights to the path.

    RLF

    Thursday, March 21, 2013 12:31 PM
  • It is a operating system step (CmdExec). I have tried directly with del, the put the command in bat file and execute the file, no success.

    Then I changed it as t-sql step, before that I enabled xp_cmdshell and in the step I added first delete command and then the bat file no success.

    I run the bat file from OS and runs fine.

    also  exec xp_cmdshell 'dir \\url\path\*.*' fails wirh the same message

    Thursday, March 21, 2013 12:48 PM
  • By default jobs are executed in the context of the SQL Server-Agent service account and this is mostly "LocalSystem"; an account with only permissions on the local system, not on others servers. So the job will fail because of missing permissions to delete the files on the other server.

    You could use a SQL Server-Agent proxy account which do have enough permissions.

    See How to: Create a Proxy (SQL Server Management Studio) and Implement SQL Server Agent Security


    Olaf Helper

    Blog Xing


    Thursday, March 21, 2013 12:52 PM
  • I agree with Olaf's recommendation.  However, there is another way if I have not overlooked some detail since back when I did this.

    http://blogs.technet.com/b/nettracer/archive/2010/06/17/null-credentials-migh-be-used-when-accessing-remote-systems-from-a-process-running-under-local-system-account-context.aspx

    If your computer is use LocalSystem and is Kerberos enabled, it will be known on the network as DOMAIN\ComputerName$.   So you could grant network rights (if you wished) to DOMAIN\ComputerName$ (whatever your computer name is).

    FWIW,
    RLF

    • Marked as answer by Barbi Rio Friday, March 22, 2013 5:03 AM
    Thursday, March 21, 2013 6:11 PM