I have a SQL Server 2012 Standard install where the Engine and Agent are running under the default NT Service accounts
Engine – NT Service\MSSQLSERVER
Agent – NT Service\SQLSERVERAGENT
I am trying to get Ola Hallengrens backup scripts to backup to a Network Share but this is proving a bit problematic.
Normally I would have the Engine and Agent running under domain accounts and grant those access to the network share and it would just work however this was setup before my time and am unable to change these.
As the Agent and Engine are not running under domain accounts I am attempting to use a Proxy to get this working.
I have a share \\ComputerName\Folder
and a service account Domain\account.svc
. I am able to browse and create files and folders using the Domain\account.svc
account so the access seems to be working ok. However when running this through the Agent it is not working.
What I have done.
- Created a Credential using
Domain\account.svc
- Created a Operating System (CmdExec) Proxy and linked it to the Credential
- In the Backup System Databases job step set the "Run as" to the Proxy account
The error I am getting in the log file is
Msg 50000, Level 16, State 1, Server [SERVER NAME], Procedure DatabaseBackup, Line 457
The directory \\ComputerName\Folder does not exist.Msg 50000, Level 16, State 1, Server [SERVER NAME], Procedure DatabaseBackup, Line 740
The documentation is available at https://ola.hallengren.com/sql-server-backup.html.
This being the same error you normally get when the Agent Domain account doesnt have access to a share.
Question – Can someone shed some light on why this would not be working.
Thanks.
Edit 1:
The Domain\account.svc has Full Control on the share, is a local admin on the machine as well as a sysadmin within SQL.
Edit 2:
Installed a test instance of SQL on my desktop and am able to replicate the issue. When I then change the Engine and Agent service accounts to run under Domain\account.svc the backup completes successfully to the share.
Edit 3:
Not an ideal solution but for now I am backing up locally and have a robocopy job set to mirror the directory to the share.
Best Answer
To make this happen, you need to give the computer domain account on which you have SQL Server installed rights to write to the share [Folder] on [ComputerName].
Say you have a computer SRV-SQLSERVER, and SQL services you run with NT Service*, but in some situation you can't create a domain account for them. But you need to store backups on other domain server \SRV-BACKUP.
To have this working, create an share on \\SRV-BACKUP\Folder and give write permission for SQL box domain account DOMAIN\SRV-SQLSERVER$.
Some related info can be found here:
How to grant network access to LocalSystem account?