Jonas Stawski

Everything .NET and More

Pull Merge Replication Security with Sql Server 2005 with no Domain Set Up

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

Comments (28) -

hi ,jonas.I do it you telled step by step,but don't success.The error logs say"Replication:agent LIUQINA\SERVER2005-TestTran-Tran-LIUQIANHOME\HOME-8 failed. The process could not read file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\LIUQINA$SERVER2005_TESTTRAN_TRAN\20070409162993\name_2.pre' due to OS error 3." please tell me how to solve it .My email is liuqian5036@163.com.Thanks.

Reply

What kind of replication are you using?

Reply

hello, jonas.
I done the same thing like you.
I have two computer.
One use SQL 2005, the other use SQL Express,
and I created the local user separately(Annika/ReplicationUser and SRV151/ReplicationUser). I can connect with Server when I use the local account(Annika/ReplicationUser) . I got error message whatever I used windows authentication or SQL server authentication to login.
Is your computer is the same computer name??
Would you help me to solve this problem?
Thanks a lot!

Reply

Annika,
      My computers had different names. I believe the reason you're not able to connect when you use windows authentication and SQL server authentication is because you're using a different user than the one that has access to do the replication. I'm just guessing since you're not telling me what the error is. Make sure the agent runs as the user created on step 1 and make sure both users on the client and the server are named the same. Try using the same password. If this doesn't help, please post your error message.

Reply

jonas,

Thanks for your help.
Because I am doing another job now.
May I discuss with you About the replication questions later?
I am really interested.

Finally, thanks...

Reply

You're always welcomed to come back and ask any question you might have.

Reply

Hello Jonas,

I performed all the steps u mentioned i created a windows user with name ReplicationUser on both client an server, my server is connected to client through remote access but i m having the following error when i run the snapshot agent

"Executed as user: ACK-SERVER\ReplicationUser. The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information.  The step failed."

please tell me the solution my id is dr_hunaindurrani@hotmail.com

Thanks in advance

Reply

Hunain,

Here are a few questions i have for you. What do you mean when you say your server is connected to the client with Remote Access? Do you mean Remote Desktop Connection?
If you read the error it says "See previous job step history message or replication monitor for more information." Did you look at that?
Are you doing merge replication?

Reply

First of all thanks for your interest in my problem.
Actually the client and server are connected through the local network like intranet.
Secondly i saw the replication monitor but there i found no detail and i am using merge replication.

Reply

First of all thanks for your interest in my problem.

Actually the client and server are connected through the local network like intranet.

Secondly i saw the replication monitor but there i found no detail and i am using merge replication.

sorry i wrote your name mistakenly.

Reply

I like to know How i configure Replication on MSSQl 2005

Reply

I have been trying to follow this and other examples for setting up replication between untrusted networks. I have had no luck with what I really need. I need two replicate to a network where connection is not possible. Not even with FTP. I want to copy files to another network and have the subscriber receive the changes without having to report back to the publisher. Seems like this is possible, however, the method escapes me.
Any help or pointers would be appreciated.

Reply

Hunain,
I had a similar problem to yourself. I resolved it by adding my user to the local group SQLServer2005SQLAgentUser$SERV-DATA$MSSQLSERVER

Reply

Hi Jonas,
I have created Distributor and Publisher in the server successfully. Our application creats subscriber at runtime and do the replication. If I log into as a administrator in subscriber machine (clinet machine) it works fine. It creats the subscriber and doing the synchronization. But if I log into a a normal user then it fails to create subscriber. error like "unabe to register user <username>". But as per ourrequirement client user can't have administrator privilage. After the installation of our application wee takes all information for subscriber creation and synchronization in a setup wizard. Can you please help me in this regard.

Reply

Manoj,
      It's been a while since I've worked with the replication service, but the main point is that the user that runs the replication (the agent) needs to have enough priviliges. The user that runs the agent should be independent from the user that is logged in the system.

Reply

Leora Betesh
Leora Betesh

Thank you for helping us out with your knowledge of pull subscriptions set up remotely.  I have followed the instructions above but I still get errors such as these:

ERROR 1
Login failed for user 'MachineName\Guest'.

For that error I disabled simple file sharing on the server but on my client wasn't able to as it is Windows XP Home.

ERROR 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPLLINK_USER-AC025-813889083_DSFDATA-1100581136_DSFDATA_PE-371629900_DSFDATA-1100581136" was unable to begin a distributed transaction.  OLE DB provider "SQLNCLI" for linked server "REPLLINK_USER-AC025-813889083_DSFDATA-1100581136_DSFDATA_PE-371629900_DSFDATA-1100581136" returned message "The partner transaction manager has disabled its support for remote/network transactions.". (Microsoft SQL Server, Error: 7391)

What am I missing here?  Somehow my connection from the client to the server isn't set up properly so each time I set up a subscription it completes with errors.

Reply

Leora,
   Please read this document on how to troubleshoot your error: http://support.microsoft.com/kb/306212

Reply

Hi Jonas,
Thanks for your reply.  I read through the article but didn't get from it a solution.  We have already tried many of the things mentioned there.
We actually decided to hire a consultant to help us.  I can post back the solution if I get it.
Thanks again for sharing your knowledge!

Reply

Hi, great article by the way, MSDN is vague to say the least. My problem is the IIS & SQL are in untrusted domains/workgroups for security reasons, therefore I cannot create matching domain accounts.

SQLserver\myuser
IISserver\myuser

How do I give IISserver\myuser NTFS rights on the SQL server snapshot folder? I cannot select thie IIS user form any domain list as it doesnt exist?

Make sense?

Thanx

Reply

@GaryB

Create a user with the same username and password in the SQL Server domain/workgroup. By default it will try to use the credentials.

Reply

Thanx Jonas,
I got it working by adding a sql account [same name as windows account but no domain\] rather than a windows machine account as in step 2 above. Can connect via ODBC & normal replication using mobsync.exe. However when I try to use web synchronisation it comes back stating
"The Merge Agent could not connect to the URL 'https://vm_2003_iis/websync1/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable."

Now I know this is OK because I can browse to that URL, enter the same login details and see the replisapi.dll fine using IE?

Am I missing something else? Appreciate your time.

Reply

Hi GaryB, any updates with your error? have you found a solution for this? I also encounter that the same error you got.

Reply

How do I delete Master database??

Reply

Hi,
I am facing different issue in current org,
i get a one request from my Clint yhat is he asked configure the replication but diffrent domains .
how is it possible please give me your valuable information .  

Reply

The process could not read file:........ .sch' due to OS error 3.


I tried and followed your steps. But no luck for some reason.

Any help would be appreciated.

Thanks
dat

Reply

Just wanted to offer a big THANK YOU for presenting this!

Even though my situation uses Transactional rather than Merge, your instructions worked perfectly for me.  In our situation, we need to publish certain customer and vendor transaction data from our internal accounting database to a subscriber database for our website.  Customers and Vendors can then view information on the site.  Works great!

Reply

Another thank you! 5.5 years later and your instructions are still valid. Worked perfectly for a pull subscription, transactional replication, 2005 publisher to 2008 R2 subscriber, non-domain scenario.

Reply

Add comment

biuquote
Loading