SQL Server Replication - setting up scheduler issue

Question

We have tasked to set up a scheduler to synchronize data between a publisher and subscriber on daily basis. The subscription was set using windows sync manager and it runs just fine when run manually. The subscriber is on the same system as the sync manager whereas the publisher has been set by the client (we do not have access to the database). It is pull type subscription.

The issue is I tried to set the scheduler through the Sync Manager but it does not run, this is happening because the subscription property is set to use windows authentication and I need to log off after every system usage. I am not able to use the SQL Server authentication property as windows does not let me save the username and password.

SQL Server Agent is not an option in this scenario.

I also tried using the replmerg.exe (hoping if it runs fine, I can set up the scheduler by calling the script through a batch file ), however the script also throws me some permission issue. I have added the user connecting to the subscriber database as the db_owner, but still get the error message as follows:

On trying to run replmerg.exe through command prompt, I get a permission error saying :

CReconcilerTask               , 2013/11/19 15:49:05.059, 9356,   409,  S1, INFO: =============== STARTING OF MERGE ===============
............
replrec!FillErrorInfo         , 2013/11/19 15:49:05.195, 9356, 20097,  S1, ERROR: ErrNo = 0x80045901, ErrSrc = , ErrType = 9, ErrStr = You do not have sufficient permission to run this command. Contact your system administrator.
DatabaseReconciler            , 2013/11/19 15:49:05.232, 9356, 20210,  S2, :T:,110,0,2,,,,,,,
DatabaseReconciler            , 2013/11/19 15:49:05.232, 9356, 20217,  S2, INFO: Session Highlights: FAIL, 
CReconcilerTask               , 2013/11/19 15:49:05.239, 9356,   645,  S1, INFO: =============== ENDING OF MERGE =================
I have added a log and the log reads like :

2013-11-19 15:49:05.141 OLE DB Distributor 'RMS': {call sp_MSgetversion }
2013-11-19 15:49:05.148 OLE DB Distributor 'RMS': {call sp_helpdistpublisher (N'RMS') }
2013-11-19 15:49:05.191 OLE DB Distributor 'RMS': {call sp_MShelp_repl_agent (N'RMS', N'rms', N'RMS1', N'D-RMS1', N'rms', 1)}
2013-11-19 15:49:05.195 You do not have sufficient permission to run this command. Contact your system administrator.
2013-11-19 15:49:05.201 Percent Complete: 0
2013-11-19 15:49:05.203 You do not have sufficient permission to run this command. Contact your system administrator.
2013-11-19 15:49:05.221 Percent Complete: 0
2013-11-19 15:49:05.224 Category:SQLSERVER
Source:  Merge Process
Number:  14260
Message: You do not have sufficient permission to run this command. Contact your system administrator.
2013-11-19 15:49:05.227 Percent Complete: 0
2013-11-19 15:49:05.229 Category:COMMAND
Source:  Failed Command
Number:  0

I am not sure what should I do make this work? I cannot manually synchronize daily and need to have some sort of scheduler set up.

The issue mentioned is with a client system and it was inaccessible to us for most part of the day today.

You are right, the user running the replmerg.exe does not have access to the publisher and distributor. They are set up on the client side with no access to us. I did set up the user to be the db_owner of the subscriber database.

The replmerg command looks like :

C:\Program Files\Microsoft SQL Server\100\COM\replmerg.exe" -Publisher [DB1] -PublisherDB [rms] -Publication [SYS1] -Subscriber [DB2] -SubscriberDB [rms] -SubscriberSecurityMode [0] -SubscriberLogin [test] -SubscriberPassword [test] -Distributor [DB1] -DistributorSecurityMode [0] -DistributorLogin [test1] -DistributorPassword [test1] -OutputVerboseLevel [2] -Output C:\ MergeAgent.log

I am not quite sure what the SQL Server version is for the Publisher and Distributor as they are on a different machine.

The Subscriber is SQL Server 2008 R2.

The sync process used to work fine through the Windows Sync Manager before when we had a centralized user to the system and we did not log it off. But now with new requirements, each of us have a unique username (admin rights) and have to log off the system after every usage.

Answer 1

What version of SQL Server are you running? Can you post your replmerg command line?
(substituting <database> for database etc.)

Re' using replmerg.exe, looking at your error message, it looks like abilities beyond synchronization are involved...

For example sp_helpdistpublisher returns properties of the publishers using a distributor... and requires db_owner or replmonitor for the distribution database.

So... does the user running replmerg.exe have the required access, to the distributor / distribution database?

Also, you might find this StackOverflow question to be of interest:

Permissions required to run REPLMERG.EXE

Is their solution --a special purpose user-- of interest? And note the comment at bottom: "Some of these instructions might even lead to the reinitialisation of the subscriber, which requires the right to a drop\recreate the corresponding database."

EDIT:
While looking up info on your REPLMERG.EXE command line, I saw this link:

How to: Synchronize a Pull Subscription (Replication Programming)

and this quote, which seems pertinent:

Pull subscriptions can be synchronized programmatically and on-demand by invoking the appropriate replication agent.

... and under To start the merge agent from the command prompt or from a batch file, this quote:

If you are using SQL Server Authentication, you must also specify the following arguments:

-DistributorLogin
-DistributorPassword
-DistributorSecurityMode = 0
-PublisherLogin
-PublisherPassword
-PublisherSecurityMode = 0
-SubscriberLogin
-SubscriberPassword
-SubscriberSecurityMode = 0

Note that your REPLMERG.EXE command line does not have PublisherLogin, PublisherPassword, or PublisherSecurityMode.

I also found Security Role Requirements for Replication

See the row titled Maintain replication agents.:

db_owner database role in the appropriate database or sysadmin server role on the appropriate server.

If the agent was created by a user in the sysadmin role, and a proxy account was not specified for the agent, the agent runs under the context of the SQL Server Agent account. In this case, a user in the db_owner role cannot modify the job associated with the agent.

Hope that helps...

Answer 2

While setting up replication for my servers, I found that the replication service account (the windows account) must have db_owner permission on the distribution database that exists under system databases in SSMS.