3

I have a new install with push replication (20+ replications). When it was set up the SQL account 'distributor_admin' was created with sysadmin. I did not do the install but I am responsible for ongoing security of the databases. The install team says the account was auto created which is supported by the msdn article Secure the Distributor

According to the msdn article Replication Agent Security Model it looks like after install we can back the permission up to db_owner with a few other considerations.

The Windows account under which the agent runs is used when it makes connections to the Distributor. This account must:

-At minimum be a member of the db_owner fixed database role in the distribution database.

-Be a member of the PAL.

-Have read permissions on the snapshot share.

-Have read permissions on the installation directory of the OLE DB provider for the Subscriber if the subscription is for a non-SQL Server Subscriber.

-When replicating LOB data, the distribution agent must have write permissions on the replication C:\Program Files\Microsoft SQL Server\XX\COMfolder where XX represents the instanceID.

Note that the account that is used to connect to the Subscriber must at minimum be a member of the db_owner fixed database role in the subscription database, or have equivalent permissions if the subscription is for a non-SQL Server Subscriber.

Also note that when using -subscriptionstreams >= 2 on the distribution agent you must also grant the View Server State permission on the subscribers to detect deadlocks.

Per security rules, if the account keeps sysadmin the password needs to be changed regularly.

In this environment we expect additional replications to be added periodically.

Will dialing back the sysadmin for distributor_admin as described by msdn lead to issues?

  • 1
    I would leave the distributor_admin account alone. The MSDN article you reference is not actually talking about the distributor_admin account, it is talking about the distribution agent account, which is different. If you dial back the permissions for distributor_admin then things might not work correctly in the future. – Brandon Williams Dec 21 '16 at 17:32
  • 1
    @BrandonWilliams thank you, yes further research pointed out the error in the quote. I have looked around a lot, mostly finding old forum posts. distributor_admin is a SQL-auth account with sysadmin, given security recommendations, I would think there would be more about it from Microsoft. – James Jenkins Dec 21 '16 at 17:43
  • I couldn't find anything from Microsoft either. I know the account executes system stored procedures, but I'm not sure if some or all of them actually require sysadmin. A good test would be to dial it back and then run through the whole gamut and see if anything fails. – Brandon Williams Dec 21 '16 at 17:47
1

In theory no, in practice yes.

After testing for several months on an instance running SQL 2014 Enterprise edition, against 20+ replication instances running MySQL, I found:

distributor_admin does not need SA for day to day operations.

If there is a sync issue (like reboot during patching) if distributor_admin does not have SA it will not self recover. When distributor_admin does have SA, replication has always self recovered after patching.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.