I have installed three SQL Server Instances on three different server and created an availability group using all three instances. The sql server engine service runs on all three instances with the same domain account. The sql server agent service runs with the same dedicated domain account on all three instances.
Now I want to create a agent job which can be run and accomplishes the following tasks:
- Check if job runs on primary, if not abort
- Remove Database from availability group
- Restore the database
- Drop old databases from secondaries
- Add restored database to availability group.
My Problem occurs in step "Drop old database from secondaries". I thought I could accomplish this using SQLCMD with powershell. I thought, as I use the same domain account for the agent on all three instances it should be possible to connect from the job using integrated security. But it is not possible for the agent to connect to the secondaries. I think I found out that the reason therefore is, that the agent connects to the database using a local virtual account NT-SERVICE\SQLAGENT which has a different SID on all three instances. But now I wonder what would be the best way (from a security point of view) to accomplish the task. I see the following options:
- Adding the agent service account domain user to all three instances as login and grant sysadmin rights. I cannot tell why but this feels a little bit strange although it would be very easy.
- Create a proxy account for the step and add it as login on the instances using sysadmin rights. I cannot see any difference to option 1 except it gets more complicated.
I tend to use option one but would like to know if option two would be better and if yes, why. Maybe there are other options as well. I "stumbled" also over group managed service accounts although this is not an option at the moment.