Thursday, January 6, 2011

SQL 2008 R2 Replication Config Errors

Quick Description:  SQL 2008 R2 installed on Windows 2008 R2, replication configures and starts, then fails with an Access Denied error.

Problem: 
Transactional Replication is configured on a SQL 2008 R2 server that is installed on a Windows 2008 R2 operating system.  When you run the agent, it fails with error.
Replication-Replication Distribution Subsystem: agent DistributionName failed. The distribution agent failed to create temporary files in 'c:\Program Files\Microsoft SQL Server\100\COM' directory. System returned errorcode 5.
 This Microsoft article addresses this error if you are running your replication agent as the SQL Server Agent account, but in the case where the subscriber, publisher, and replication agent all use different domain service accounts, this did not fix the error.

Solution:
The following fixes the problem, it may be overkill, I wasn't able to isolate which of the two additional permissions is responsible for fixing it.

On the subscriber grant write permissions for the directory 'c:\Program Files\Microsoft SQL Server\100\COM' to the following accounts:
SQL agent account for both subscriber and publisher
Replication agent account

1 comment:

  1. I tried granting full permission to Everyone on the folder, it still does not work.

    ReplyDelete