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.

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.

share|improve this question

migrated from stackoverflow.com Nov 19 '13 at 17:36

This question came from our site for professional and enthusiast programmers.

1 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... according to: http://technet.microsoft.com/en-us/library/ms190323.aspx

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: http://stackoverflow.com/questions/2883580/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:
technet.microsoft.com/en-us/library/ms147343%28v=sql.105%29.aspx
(I was not allowed to make it clickable, because my rating here only allows 2 links.)

... 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.


Also note this link, Security Role Requirements for Replication:
technet.microsoft.com/en-us/library/ms152528%28v=sql.105%29.aspx

... and 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...

share|improve this answer
    
@user30770 So, I'm curious about a StackExchange question -- do you see an "add comment" link, or not, just below my answer? ((On StackOverflow, I see "add comment" just below all questions, but here, I'm not seeing it below your question--because here, I don't have enough points. I'm only seeing "add comment" below my own answer. Are you having the same problem -- you can "add comment" on your own posts (your question, and your answer), but cannot comment on my posts?)) I guess, for now, we can communicate by editing our own posts, and commenting below our own posts. –  Doug_Ivison Nov 21 '13 at 4:11
    
@user30770 Checking up on your new info, I found some docs that could help, and posted them with my comments, AS EDITS to my answer. HTH... –  Doug_Ivison Nov 21 '13 at 4:49
    
@AaronBertrand Hi, Aaron, I don't know if this comment on my answer notified you... I understand that your moving his "answer", to be an edit to his question, was enforcing common sense communication & standard practice here.... but note: the question migrated from SO as a question from "user3009843", but he did not get that signon on dba.stackexchange.com -- he got "user30770". So, he had no points, (not even the points of asking a question). I don't think that gave him the ability to comment on my answer, or even on "his own" question -- only on his own answer... now gone. –  Doug_Ivison Nov 25 '13 at 22:18

Your Answer

 
discard

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