Thursday, September 17, 2009

Making SQL Server Replication work on Network Service Account

This is one of the topics i have been working on since last few days. There's not much information available on the web on how to do that so i decided to log it in this blog.

Problem Statement :-
Setup a SQL Server replication while running the SQL Server in "Network Service" Account.

Normal Convention :-
Most local SMEs i spoke to suggested that it is not possible to setup replication between SQL Servers using the Network Service account and suggested that i use the domain account instead. However the problem with that solution is that domain account password expires in some time and then we may even get a production downtime.

Proper Solution :-
The SQL Service like any other windows service can be run using Local Service, Network Service or a Domain Account credentials. Now if you want to setup a replication Local Service account is of no use since that is an account which has no identity over the network. Domain account is not ideal since passwords have to be changed at regular intervals resulting in downtime as well as maintainance costs. So Network Service account is the ideal way to go. This account presents the machine identity over the network.
So if we want to setup replication using this account the machine identities should have access to databases. We can create a security group in active directory and add all the machines which want to talk to each other in replication should be added to this group. Then give the permission to this security group on all the databases.
This way when the replication service on one machine sends the replication related instructions to another machine it presents the machine credential which has the permissions on the database, the replication has no security related problems.
so to summarize
1) Make sure that all the SQL Service and SQL Agent Service is running using Network Service credential
2) Create a security group in the domain and add all the SQL machines which would be part of replication to this group
3) Give this security group appropriate permissions on all the databases which would be part of replication.

Happy coding...

~Abhishek

1 comment:

Anonymous said...

Nice blog. Helpful.