Change SQL Service and/or Agent accounts to Domain level?

Question

Hello, our SQL Server like in general uses 2 main SQL accounts (SQL Service & SQL Agent).

Currently both of them are Local User accounts.

We have a daily job that runs full DB backups onto its local SQL Server VM.

We would like to add an extra step/job within SQL Agent to copy these .bak files onto another different network location and realize will need to use Domain account(s).

To do this would just the SQL Agent account need to be changed to Domain level or both SQL Service as well?

Thanks in advance.

Answer 1

Is it possible to have a SQL Agent job (Local User like mentioned earlier) to call a .bat script file that copies the .bak backup files using the Domain\writer account to run (guess like proxy) or set the .bat copy file in Windows Task Manager (runs under Domain\writer) on the local machine and then SQL Server Local User calls that Task Manager job (since its on its own same local machine)?

Yes, that is possible, and the way you do it is to use proxies. This may not be the best introduction to proxies, but I discuss them in this chapter: http://www.sommarskog.se/perm-hijack.html#agentjobs.

I don't like the alternative to grant permission to the machine account. That opens up a little too much.

And, yes, you can have Agent running under a normal domain account, but that also come with a bag of problems.

Proxies is the way to go.

Answer 2

You can do this without changing the service account for the agent. Create a new credential - and proxy account using a domain account and add that proxy account to the Powershell subsystem in the agent.

Answer 3

Thanks for the replies all.

Just want to keep it simple and wanted to know if just the SQL Agent account needs to be Domain level or also need to put SQL Service account to Domain level as well (both are current Local Users on its own machine).

Answer 4

Hi techresearch7777777-7743,

In addition, you can grant the network permissions to local user account without changing SQL Service Agent account to Domain account. You can add account called computername$ to the network file sharing properties->sharing tab.

Please refer to this thread which might help.
Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 5

Thanks for the additional replies.

I'm not quite sure I understand AmeliaGu-msft and also checked that link.

Going down the route of not changing our SQL Agent Local User account...we actually have already a domain account (i.e. Domain\writer that has access-permissions to write-read to the foreign network share.

Is it possible to have a SQL Agent job (Local User like mentioned earlier) to call a .bat script file that copies the .bak backup files using the Domain\writer account to run (guess like proxy) or set the .bat copy file in Windows Task Manager (runs under Domain\writer) on the local machine and then SQL Server Local User calls that Task Manager job (since its on its own same local machine)?