SQL Server – Domain Account vs Windows Account for Transactional Replication

replicationsql serversql-server-agenttransactional-replication

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?

Best Answer

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.