Configuring Transactional Replication by following principles of least privilege

Hi Friends,

Today we are going to explore how to configure Transactional Replication with minimal permissions.
In various Environments I have see Replication being configured with higher privileges than what is being really required.

In this post you will come to know about the permissions & different errors that we will come across by following this.

Environment: 2 Default Instances by name NODE10 & NODE20 running on SQL Server 2017 Version. We have 4 Logins by name

1)contoso\SQLSVC--> Account used to configure replication

2)Contoso\sqlsnapshot-->Account used to run Snapshot Agent

3)contoso\sqllogreader-->Account used to run Log reader Agent

4)contoso\sqldistri-->Account used to run Distribution Agent

I have not given any permissions to SQLSVC & when you try to configure publication. We will get the below error

As you can see we need to own the database to create publication. So there are 2 ways to achieve this either giving db_owner or owning the database itself. Here I have given db_owner permission.

DB_owner database role has been granted to SQLSVC on publication database in my case it is NormalDB & on Distribution Database.

Now I went on to create publication & to my surprise this is what the message I got

Boom So how to overcome this well I now connected with sysadmin in another window & ran the below command

use master
exec sp_replicationdboption @dbname = 'normaldb',
@optname = 'publish',
@value = 'true'

This command will help you to enable Database to configure for Replication.

Now I started creating publication so as expected this time it allowed us to view the Database..

Now at the time of configuring Agent properties (snapshot & log reader) I have given the above mentioned dedicated logins

NOTE: I have not given any permissions to them on to any of the databases.

My publication(PUB10) got created successfully & if I open Publication Access list I could see the account with which we configure replication (contoso\sqlsvc) got added to it automatically.

So Now let's configure our Subscription.

As you can see I am not able to view the existing databases to configure it for subscription so I have given db_owner role to SQLSVC Account just like of what we did on publication.

After giving the permissions I was able to view my database.

We are configuring our Distribution agent with the login (contoso\sqldistri) of what we specified above

Finally my subscription got created.  So I launched Agent on Publisher & checked the job history of the Snapshot Agent & this is how it looks

And this is the status of Log reader agent job

As expected both of them are getting failed as we have not  grant any permissions to those logins.
Let's now grant the required privileges

For contoso\sqlsnapshot we need to grant db_owner on distribution & publication Database.

For Contoso\sqllogreader we need to grant db_owner on distribution & publication Database.

Having granted the permissions I started the snapshot agent however if you would have guessed properly even this time it fails as there are no permissions on to snapshot agent on shared folder

So as you can see we from the above screen shot we need to grant write permissions on to snapshot shared folder which in my case is (C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\ReplData\unc\NODE10_NORMALDB_PUB10\20190128023040)

After grating write permissions my snapshot Agent job ran successfully.  And there are no problems with Log reader either as we have given the permissions.

Now we need to check the permissions that we need to grant for Distribution agent. In my case as I opted for pull subscription so My Distribution agent would be running on Subscriber Server.

As we have not given permissions currently it is not running as expected

To make it run successfully we need to grant the below permissions

contoso\sqldistri should have db_owner on subscriber Database.
Also need to grant read permissions to snapshot folder.

Thinking that it would suffice the requirements I cross checked in replication Monitor. Boom for my surprise it again went out of sync.

As you can see in the screen shot it says "Agent message code 14080. The remote server "NODE10" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers."

So where do we miss? Here comes the importance of PAL (publication Access list) which has been explained very nicely here

After adding the distribution agent to PAL everything went fine at last.

NOTE: If it push Subscription then we need to grant db_Owner permissions on to distribution database as well which is not required in the above case as we opted for pull.

Hope this clarifies about the privileges section while working on Transactional Replication.