xp_cmdshell and network permissions

[k8277]

I setup up a sql server agent job to run a batch file that does nothing more than copy some files from a network location to a share on the local SQL server where the batch job is scheduled to run.

My only step in the job is of type Transact-SQL and is as follows:
exec xp_cmdshell '\\grcsql\offsitebackup\offsite.bat'

When the job runs through SQL server agent it is failing due to access being denied (I tested using query analyzer). The batch job is:
copy \\grcmfs1\docfiles\MANS\thomas\39tb01_.XLS \\grcsql\offsitebackup\staging\39tb01_.XLS

If I run the batch job manually, everything works .... So my question is, when the Sql server agent job runs, with what account does it try to access the network path? I have changed the job owner and it doesn't seem to make a difference. Is it using the Sql server agent starup account?

 

[JamesLean]

If the job is owned by a member of the sysadmin fixed server role, the job executes using the Windows account under which the SQL Server service is running. If the job owner is not in sysadmin, the job executes using the SQL Server Agent proxy account, and an error is raised if no proxy account has been set.

My advice would be to set a sysadmin member as the job owner (eg, sa), make sure the SQL Service is running under a Windows account (not local system account) and grant that account the necessary network permissions.

--James

 

[donutman]

Why use SQL Server's scheduler? Just use the OS.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[k8277]

I wanted to use SQL because I have other steps in the job stream that I would like to add, that way I can have one schedule that does everything.

 

[k8277]

James, it worked as soon as I gave permissions to the SQL domain account at the file level.

Thanks