Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
All the online resources say to use a Windows account for Agent accounts, i.e. Snapshot, Distributor and Log Reader to set up Replication.
See the following links as examples:
My question is: what is the problem with using domain accounts for the above, which is much easier to maintain and administer?
Short Answer: Nothing at all is wrong with a domain account. When all the computers participating are in the same domain, go that way. Easier to manage and maintain and secure.
Your confusion here lies in the way the documents you are looking at are structured. In the document you reference in the comments above you see this section:
If your replication topology includes computers that are not in the same domain or are in domains that do not have trust relationships with each other, you can use Windows Authentication or SQL Server Authentication for the connections made by agents (For more information about domains, see the Windows documentation). It is recommended as a security best practice that you use Windows Authentication.
To use Windows Authentication:
Add a local Windows account (not a domain account) for each agent at the appropriate nodes (use the same name and password at each node). For example, the Distribution Agent for a push subscription runs at the Distributor and makes connections to the Distributor and Subscriber. The Windows account for the Distribution Agent should be added to the Distributor and Subscriber.
Ensure that a given agent (for example the Distribution Agent for a subscription) runs under the same account at each computer.
The article isn't saying "if you want to use a Domain account, we suggest you use a local account" - but I totally see why anyone would read it that way.
The article is saying, instead, "Use a domain account where you can. IF your computers are not in the same domain, here's a little trick you can use."
That trick, sometimes referred to as pass through authentication has you create an account on each computer. Same name. Same password. By doing that - windows will pass the credentials through and, for all intents and purposes of this conversation, the apps (SQL and Replication objects) won't realize it isn't really a "domain account" - and it will just work.
If, however, the computers are on the same domain? Just use the domain account.
If you go back to that first link you commented with - you'll notice the "if your replication topology includes computers that are not in the same domain..." line isn't bulleted or indented. Really the bullet about the local to each should be indented off of that.
The only problem I've ever had with using a Windows account for replication is when there were domain controller issues. A DC would stop authenticating and replication would fail.
After a few instances of this several years ago, I changed every publication and subscription to sa. I know all the issues about using sa, but it was used at a previous employer and that was that. After this whenever there was an issue with a DC, replication was never affected.
I think there was an instance of DC issues affecting replication and then alert emails going out. Funny thing is that it spawned something like a million emails and caused email issues as well.
And I think the only reason sa worked there was because all the sa accounts on every SQL server at this organization used the same password. Tried to change it, was overruled and didn't want to be a "problem".
