Sql-server – How to copy backup files to remote share in SQL Server Agent job without AD/domain accounts involvement

maintenancepermissionssql server

MS SQL Server 2012… with nightly databases backups to the same/local as SQL Server machine…

I am trying to add another SQL Server Agent Job to copy the .bak files to remote (non-windows, i.e. Linux) share with non-Windows (non-AD) user/password credentials.
I do not have any access to configuring or changing that access which is under control of other, quite remote people.

For this (copying) I created local user with the same user name and password, gave it permissions to the (source or local) backup-folders upon which all perfecly works from command line (Win + К or cmd) if to enter the command manually: 

RUNAS /user:UserName /savecred "robocopy d:\SQLBACKUP  \\10.195.xx.yyy\backup /S /purge /MAXAGE:7 /MT:1 /Z"

but fails to run as SQL Server Agent job (type of step is "Operating System(CmdExec)". SQL Service Agent (with standard configuration of running under [NT Service/SQLServiceAgent] account, the job is owned by SA SQL Server superuser).

Can anybody explain me why it is failing and how to correctly make it running (taking into account that I do not have access to domain users configuration)?

Best Answer

To answer the specific question of why it is failing is that the service account on your server does not have the correct permissions to access the share.

I have an environment where I have 2 servers not on a domain. Using SQL Server Agent proxies is the way I solved this.

I have a special account I use for the 2 machines (Your accounts with the same name and password) which I had to add to the instance level credentials (under security in SSMS Object explorer) (see MSDN article "Create a Credential" ) and add an agent proxy under the Operating System (CmdExec) .

Once those are created you can select the account to run as (the name you gave your proxy) and it will work. However this is on 2008 and I have not played with the 2012 agent that much.
Should still work the same though.

You would then use your robocopy line "robocopy d:\SQLBACKUP \10.195.xx.yyy\backup /S /purge /MAXAGE:7 /MT:1 /Z" as your command (I use xcopy but it works the same).

Related Question