1

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)?

Gennady Vanin Геннадий Ванин
  • 646
  • 2
  • 8
  • 18
  • Have you considered running the backup using a command line program and a scheduled task or PowerShell job instead of SQL Server Agent? – Aaron Bertrand Oct 14 '13 at 12:15
  • @AaronBertrand, yes, I eventually was forced to run it through Windows Server 2008 Task Scheduler but it is unfortunate since: 1)it is decentralized; while logically and physically it depends on completion of (source for copy target) local backup through maintenance plans by SQLServerAgent job; 2)difficult to see how long it took or why they failed - the info is buried among thousands of (note it is on heavily used app server) app events (in eventvwr.msc; the copying takes hours); – Gennady Vanin Геннадий Ванин Oct 14 '13 at 17:27
  • 3)the last but not least - I'd like to understand why SQLServerAgent job fails and how to correctly succed with it. It either starts and fails with no specific info in 3-4 min or hangs indefinitely without any errors and really not starting any copying – Gennady Vanin Геннадий Ванин Oct 14 '13 at 17:27
  • You know that you can run the backup that way too, not just the copy, so you don't have this dependency on some maintenance plan... – Aaron Bertrand Oct 14 '13 at 17:29

2 Answers2

1

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).

Gennady Vanin Геннадий Ванин
  • 646
  • 2
  • 8
  • 18
RubberChickenLeader
  • 684
  • 4
  • 14
0

Another way to copy files to the networked computer is to use source computer domain credentials - https://dba.stackexchange.com/a/128282/51798

Community
  • 1
Oleg Strutinskii
  • 223
  • 2
  • 12