Configure SQL Server Agent access to a mapped drive?

• dapoole

  Default port

  Points: 1470

  More actions

  October 16, 2014 at 9:13 am

  #297520

  Hi,

  I'm trying to create a job that will copy a backup file from a mapped network drive to a local folder however it keeps failing due to a privilege.

  The command is:

  copy \\10.10.10.5\f$\test.nightly H:\MSSQL\Backup\test.nightly

  I can run the command successfully from the command prompt, however using the same command in a SQL Server job it fails with "Access is denied".

  How do I configure the mapped network drive to allow the Sql Server Agent to get access to it?

  TIA

• dapoole

  Default port

  Points: 1470

  More actions

  October 16, 2014 at 10:04 am

  #1752913

  I've worked out a solution. Use 'net use' with xp_cmdshell.

  DECLARE @CmdString1 VARCHAR(2000)

  DECLARE @CmdString2 VARCHAR(2000)

  SET @CmdString1 = 'net use Z: \\10.10.10.5\f$ /persistent:yes /user:domain\userid password'

  SET @CmdString2 = 'copy \\10.10.10.5\f$\test.nightly H:\MSSQL\Backup\test.nightly'

  EXEC master.dbo.xp_cmdshell @CmdString1

  EXEC master.dbo.xp_cmdshell @CmdString2