1

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?

5

SQL Server itself is performing the backup; therefore the DOMAIN\sqlengine account 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.

  • So this is just how it works then. Ok thanks. So is changing the running user of job pertinent to the scope of the Databases affected then? Just trying to better understand the option of the running user of the job. – Matt Mar 23 '17 at 18:22
  • The user running the job needs to have access to perform the operations in SQL Server. – Max Vernon Mar 23 '17 at 18:25
  • The link needs to be updated. I don't have enough rep to do it myself – Matt Mar 23 '17 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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