I am trying to migrate a working pull subscription for transactional replication from one subscribing server to a new one. The subscribing server is at another site and is connected via a VPN tunnel. The serverName is resolvable via the hosts file.
I am trying to capture the existing configuration precisely, but clearly am missing something.
The error is The process could not read file '\\[server]\repldata\unc\[folder]\[folder]\[file].pre' due to OS error 5. I can RDP into the subscribing server with the distributor connection account and can access the file on the UNC share.
Everyone has permissions to the UNC share and we haven't seen this problem with other subscriptions.
The distribution process account is the SQL Server Agent, which I know is not best practice but matches the configuration of the existing working replication. I temporarily tried using a (local) Windows account
Again, we have tried to configure the subscribing server exactly as the working server. What are we missing? We never saw this error when setting up the previous subscription.
One note: the old subscription is still up and functioning, and uses the same accounts to connect to the distributor. I wonder if Access is Denied could be thrown due to a sharing conflict.
Best Answer
OS error 5 means Access is denied.
Instead of using SQL Server Agent service account — use pass-through authentication.
Create a local Windows account on both Publisher and Subscriber that has the same username and password. Add the Windows account to SQL Server logins on both Publisher and Subscriber. Use this account for the Distribution Agent process account.
Ensure the account is a member of the db_owner fixed database role in the subscription database, a member of the PAL at the Publisher, is a login associated with a user in the publication and distribution databases, and has read permissions on the snapshot share.
Replication Agent Security Model