I have been testing the merge replication from Sql Server 2005 for a client. The truth is that I was very impressed with it. This thing works very well (at least with the requirements we had) and it's all a matter of configuration. I had some difficulty setting up the subscribers running on each client (pull replication). The agents would constantly fail due to security reasons. After a lot of trial and error and finally reading about it here I was able to have a secured model and make replication work. I must say I didn't have a domain set up on neither the client nor the server and therefore made this much more difficult. Here are some of the errors I was getting:

"The process could not read file ... because of OS error 1326"
"The process could not read file ... because of OS error 53"

Both of these errors relate to authentication and authorization at the OS level.

The article linked above is not very helpful on the how to's, but it's helpful on the theory. So I'm going to put the theory to practice and hopefully it will help you out.

Merge Agent for a pull subscription

The Windows account under which the agent runs is used when it makes connections to the Subscriber. This account must at minimum be a member of the db_owner fixed database role in the subscription database.

The account that is used to connect to the Publisher and Distributor must:

  • Be a member of the PAL.
  • Be a login associated with a user in the publication database.
  • Be a login associated with a user in the distribution database. The user can be the Guest user.
  • Have read permissions on the snapshot share.

This means the following:

  1. You need to have a Windows Login that is related to a SQL Login. This login must have db_owner role on the DB you're replicating. This user will be used to run the agent on the client. The SQL Login have the same username as the Windows Login.
  2. The publication also needs to have a Windows Login that is related to a SQL Login. I used the same user name as the Windows Login from the client and both the Sql Login and the Windows Login have the same username.
  3. This SQL Login on the publication server has to be at least a guest of the DB being replicated.
  4. He also needs to be a member of the PAL.
  5. The Windows Login from the publication folder needs to have read permissions on the snapshot share.

Step by Step on how to do this with Windows 2003 and SQL Server 2005 (this step by step assumes you have already set up the publication at the publication server):

  1. First we need to create a Windows Login that will be used to run the SQL Agent on the client.
    A.  Go to Start - Administrative Tools - Computer Management
    B.  Expand the Local Users and Groups and select the Users folder.
    C.  Right Click on the Users folder and select New User...
    D.  Fill out the new user info. I gave my User a username of [DBName]ReplicationUser. So if the DB name was TestDB then the username was   TestDBReplicationUser.
    E.  Uncheck User must change password... and check User cannot change password and Password never expires.
  2. Now we need to associate this user with a SQL Login.
    A.  Open SQL Server Management Studio and login with the admin account. I use the SA account.
    B.  Expand the Security folder of the server and then expand the Logins folder. All the logins to the server are listed here.
    C.  Right click on the Logins folder and select New Login...
    D.  Search the Login Name and enter the username of the Window Login we created on step 1. Click on check names to make sure it was spelled correctly. At the end you should have a user with [MachineName]\[UserName] format.
    E.  Leave Windows authentication selected and select the DB you will replicate as the Default database.
    F.  Click OK.
  3. Now we need to set this newly created SQL Login db_owner privileges to the DB we're going to replicate.
    A.  Expand to the following folder: Database - [DB] - Security - Users.
    B.  Right click on the Users folder and select New User...
    C.  Click on the ... of the login name and browse to select the newly created SQL Login. Click OK twice to go back to the New User window.
    D.  I name the User name just like the Login name without the Computer name. So if the Login Name is MyComputer\MyUser, the User name is MyUser.
    E.  Set the Default schema to db_owner. You can user the ... and browse to make sure you select the right schema.
    F.  Check db_owner as the Role members of this user.
    G.  Press OK.
  4. Create a Windows Login at the publication server just like you did on step 1. I name them exactly the same on both servers.
  5. Associate this Windows Login with a SQL Login just like you did on Step 2.
  6. Now we need to give access to this SQL Login to the DB we're replicating. Follow the steps on step 3, but there's no need to set a Default schema or apply any Role memebers.
  7. We need to make this user a member of the PAL.
    A.  Expand the folder Replication - Local Publications.
    B.  Right click on the already set up publication and select properties.
    C.  Select Publication Access List on the left pane.
    D.  Click on Add on the right pane.
    E.  Select the user created on step 5 and 6 and click OK.
    F. Do not Click OK yet.
  8. Give read access to the snapshot folder.
    A.  Select Snapshot from the left pane and take notice of the Location of snapshot files. You should have shared that file and therefore it should be a shared folder. i.e. \\mycomputer\repldata.
    B.  Browse to this folder. Most likely the folder is located at [Drive]:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL.
    C.  Right click on the repldata folder and select properties.
    D.  On the Sharing tab, click on Permissions and give the Windows Login created on step 4, read access. Note you might have to add the user by clicking on Add...
    E.  Click OK and move to the Security tab.
    F.  Over here give the same Windows Login Read access. Note you might have to add the user by clicking on Add...
    G.  Click OK. Close the opened folder.
    H.  Click OK on the Publication Properties window.
  9. Set up the subscription to the publication on the client's machine.
    A.  On SQL Server, expand the Replication - Local Subscriptions folder.
    B.  Right click on the Local Subscriptions folder and select New Subscription.
    C.  Follow the wizard and select all the appropriate properties until you get to the Merge Agent Security. (It is very important you select the Agent to run at the subscriber. Pull Subscription).
    D.  At the Merge Agent Security page, click on the ...
    E.  Select Run under the following Windows account
    F.  Provide the user created on Step 1 in the format of MyComputer\User.
    G.  Enter the password.
    H.  Make sure the By impersonating the process account is selected on both Connect to the Publisher and Distributor and Connect to the Subscriber.
    I.  Click OK and continue with the wizard.
  10. Make sure the agent is able to connect, pull the snapshot, and replicate with no problems.

Happy Configuring! Laughing