Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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

share|improve this answer
    
Brandon, I appreciate your help and have seen similar advice by you. I did try changing to a local account that matches a local account on the publisher with the same password. There was no change in behavior. Furthermore, the working subscription on the old server uses the above configuration, and the user used to connect to the distribution share is not defined as a database user in the subscription database. –  slachterman Apr 18 '13 at 17:28
1  
I can assure you it is a permissions related issue. Grant full control to the snapshot share and pare it down from there. Forget about the working subscription and ensure you setup the new subscription and accounts with the permissions required that are covered in Replication Agent Security Model: msdn.microsoft.com/en-us/library/ms151868.aspx –  Brandon Williams Apr 18 '13 at 18:08

This is what we did to fix the issue.

  1. We changed the user account under which the distributor is running to service.sqlserver.

  2. We then changed the account the distributor agent is running on to a local Windows account on the subscribing server, that matched (user/pass) a local Windows account on the publication server.

  3. We then stopped the job on the subscribing server which runs for the replication.

  4. We then reinitialized the subscription and another snapshot was created successfully, downloaded to the subscribing server, and applied.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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