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:

  1. Check if job runs on primary, if not abort
  2. Remove Database from availability group
  3. Restore the database
  4. Drop old databases from secondaries
  5. 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:

  1. 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.
  2. 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.

  • Third option: Use an enterprise scheduler (if you have one) or a management machine to run the powershell script as a specific user. Module sign the SQL pieces so that very little actual permissions are needed. – Sean Gallardy - Retired User Jul 31 '17 at 15:59
  • Thanks for the fast response. To be sure, the according sql user must still have relatively high SQL permissions as it must be able to restore a database (after removing from the availability group the db stays in "restoring" state on the secondary) and drop the database, right? – Tobi DM Jul 31 '17 at 16:09
  • Not if you use module signing, then it just needs execute: docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/… – Sean Gallardy - Retired User Jul 31 '17 at 16:51

Your Answer

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

Browse other questions tagged or ask your own question.