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

Improve this question
4
  • 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
    Commented Oct 14, 2013 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 Геннадий Ванин
    Commented Oct 14, 2013 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 Геннадий Ванин
    Commented Oct 14, 2013 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
    Commented Oct 14, 2013 at 17:29

2 Answers 2

Reset to default
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).

Improve this answer
2
  • Thanks for the edit. Ill use this style in my future postings.
    – RubberChickenLeader
    Commented Oct 15, 2013 at 18:43
  • I just wanted to insert the space between source and destination directory in copy-pasted from my answer robocopy command (which originally was with an error) but the edit with less than 6 symbols changed was not accepted. So, I had to expand my editing...
    – Gennady Vanin Геннадий Ванин
    Commented Oct 16, 2013 at 4:23
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.