I recently created a new SQL Server environment and I am trying to use best practices where I can, especially the least privileged approach. Other servers in our environment just have jobs running with combinations of Domain Admin rights and SA rights which is "simpler" and scary.
That aside, I have the Database Engine running with the domain account DOMAIN\sqlengine and the Agent running with DOMAIN\sqlagent.
I have a simple maintenance plan that does a full backup of all databases on the server to a remote UNC Path. I had assumed/planned that the agent account (DOMAIN\sqlagent) would be executing the jobs of the plan. The jobs were failing with an OS-driven Access Denied error. So I added the DOMAIN\sqlagent account modify rights to the directory so that it could perform the backup. To my surprise it failed again. When I check the security logs of the target server I see that DOMAIN\sqlengine was the account authenticating. That account was not given rights to the UNC path. Why is the engine account, not the agent account, running the job?
If I look at the job owner I had set DOMAIN\sqlagent for all of the jobs in that Maintenance Plan. Under the job step it is set to run as the "SQL Server Agent Service Account".
What I am doing wrong or not understanding?
Best Answer
SQL Server itself is performing the backup; therefore the
DOMAIN\sqlengineaccount needs access to the share.SQL Server Agent is simply starting the job.
This is exactly the same as if you started a backup using the T-SQL
BACKUP DATABASE [xyz] TO ...command. SQL Server would still require access to the destination path. You can still lock-down the destination path, and should do so, to prevent unauthorized access to the data stored in the backup files.Also, instead of using Maintenance Plans to perform maintenance, I'd recommend using Ola Hallengren's scripts; they are excellent, well-supported, and widely-used.