Does the SQL Server Service Account have to have access to a network share to perform a backup?

Question

I've been provided a specific domain account to handle backups from my sysadmins. I am slightly confused as I've never been provided a separate domain account to handle backups in the past. I still want to use Agent to handle this, so I am thinking that they are thinking the below:

If you are running the job under a proxy account, the proxy must be a domain account. Add the account to the sysadmin server role and grant it full control of the directory and the network share.

So I've done what I thought the above is describing, I added the domain account as a login, I created a credential & proxy, and I created the job step as CmdExec to run under the proxy, but I still get:

Operating system error 5(Access is denied.).

It isn't until I give access to the Service Account that is running the SQL Server Service that I am able to backup to this network share. I'm also concerned about restores and I wanted to utilize Agent to handle cleanup tasks & moving files around to various directories as they age.

So I'm not sure what's going on here, but I think the service account at a very minimum needs write access? One sysadmin said he was aware of someone doing the backup process as a "separate service", which I don't quite understand, unless its something like ApexSQL backup or something...

Answer 1

If you're performing native SQL Server backups, the service account running the SQL Server database engine must have access to the backup location. The Agent job can run under a proxy to fire off the job itself, but the engine still performs the backup so still needs access.

The only way to have the backup write to disk as another user would be to use a third-party tool that receives the backup data through the SQL VDI interface and writes it to disk as the appropriate user.

From Docs:

Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions.

For managing the files after they've been backed up, you can use a SQL Server Agent proxy for this to ensure you're using the correct account for file system access.