October 23, 2017 at 5:34 am
I really hope someone can help as this is driving me crazy!
I'm trying to configure SQL Server Merge Replication with Web Syncronization using RMO.
Distributor, Publication, IIS all running on the same remote server. Subscription is running on a local server. All instances are SQL Server 2017 (64bit), Developer Edition.
I create a subscription entry in the subscriber database via this code which works fine...
// Define the Publisher, publication, and databases.
static string publicationName = "TestMerge";
static string publisherName = @"REMOTE-SERVER\SQLSERVER2017";
static string subscriberName = @"LOCAL-SERVER\SQLSERVER2017";
static string subscriptionDbName = "test";
static string publicationDbName = "test";
static string webSyncUrl = "https://replication.mysite.com/rep/replisapi.dll";
static string winLogin = @"LOCAL-SERVER\repl_distribution";
static string winPassword = "xxxxx";
static string internetLogin = "repl_merge";
static string internetPassword = "xxxxxx";
// Create the Subscriber connection.
ServerConnection conn = new ServerConnection(subscriberName, "sa", "xxxxx");
// Create the objects that we need.
MergePullSubscription subscription;
try
{
// Connect to the Subscriber.
conn.Connect();
// Define the pull subscription.
subscription = new MergePullSubscription();
subscription.ConnectionContext = conn;
subscription.PublisherName = publisherName;
subscription.PublicationName = publicationName;
subscription.PublicationDBName = publicationDbName;
subscription.DatabaseName = subscriptionDbName;
// Specify an anonymous Subscriber type since we can't
// register at the Publisher with a direct connection.
subscription.SubscriberType = MergeSubscriberType.Anonymous;
// Specify the Windows login credentials for the Merge Agent job.
subscription.SynchronizationAgentProcessSecurity.Login = winLogin;
subscription.SynchronizationAgentProcessSecurity.Password = winPassword;
// Enable Web synchronization.
subscription.UseWebSynchronization = true;
subscription.InternetUrl = webSyncUrl;
// Specify the same Windows credentials to use when connecting to the
// Web server using HTTPS Basic Authentication.
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication;
subscription.InternetLogin = internetLogin;
subscription.InternetPassword = internetPassword;
// Ensure that we create a job for this subscription.
subscription.CreateSyncAgentByDefault = true;
// Create the pull subscription at the Subscriber.
subscription.Create();
}
catch (Exception ex)
{
// Implement the appropriate error handling here.
throw new ApplicationException(String.Format("The subscription to {0} could not be created.", publicationName), ex);
}
finally
{
conn.Disconnect();
}
If I go into SQL Manager & right click the subscription entry created & select View Syncronization Status then click Start, it initializes the database with the snapshot from the remote server over https. All is ok...perfect.
But trying to do the same snapshot initialization process via the windows app does not work.
I re-run the script above on a new database (deleted old, created new with same name) to add the subscription entry, all ok.
I then try to initialize the database with a snapshot from the remote server in the windows app with the code below...
// Create a connection to the Subscriber.
ServerConnection conn = new ServerConnection(subscriberName, "sa", "xxxxx");
// Merge pull subscription
MergePullSubscription subscription;
try
{
// Connect to the Subscriber.
conn.Connect();
// Define the pull subscription.
subscription = new MergePullSubscription();
subscription.ConnectionContext = conn;
subscription.DatabaseName = subscriptionDbName;
subscription.PublisherName = publisherName;
subscription.PublicationDBName = publicationDbName;
subscription.PublicationName = publicationName;
// If the pull subscription exists, then start the synchronization.
if (subscription.LoadProperties())
{
// Get the agent for the subscription.
agent = subscription.SynchronizationAgent;
// Set the required properties that could not be returned
// from the MSsubscription_properties table.
agent.PublisherSecurityMode = SecurityMode.Integrated;
agent.DistributorSecurityMode = SecurityMode.Integrated;
agent.Distributor = publisherName;
// Enable verbose merge agent output to file.
agent.OutputVerboseLevel = 4;
agent.Output = @"C:\Temp\merge-agent.log";
// Handle the Status event
//agent.Status += new AgentCore.StatusEventHandler(agent_Status);
// Synchronously start the Merge Agent for the subscription.
agent.Synchronize();
}
else
{
// Do something here if the pull subscription does not exist.
throw new ApplicationException(String.Format(
"A subscription to '{0}' does not exist on {1}",
publicationName, subscriberName));
}
}
catch (Exception ex)
{
// Implement appropriate error handling here.
throw new ApplicationException("The subscription could not be " +
"synchronized. Verify that the subscription has " +
"been defined correctly.", ex);
}
finally
{
conn.Disconnect();
}
But I keep getting the error: The processing of the response message failed.
Here is an excerpt from the log...
2017-10-20 08:53:49.239 The request message was sent to 'https://replication.mysite.com/rep/replisapi.dll'
2017-10-20 08:53:49.241 OLE DB Subscriber 'LOCAL-SERVER\SQLSERVER2017': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2017-10-20 08:53:49.246 Downloaded a total of 3 chunks.
2017-10-20 08:53:49.453 OLE DB Subscriber 'LOCAL-SERVER\SQLSERVER2017': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2017-10-20 08:53:49.461 The processing of the response message failed.
After this failure, if I go back to SQL Manager & run the View Syncronization Status > Start process there, the snapshot initializes fine. Why does the sync process work fine in SQL Manager, but not in the windows app?
What is strange, once the snapshot initialization is done via SQL Manager and all data is equal in both databases, I then make changes to database data on both the remote and local databases and run the code above in the windows app to re-run the sync, it runs fine & the data is synced between both servers.
So it seems only the initial snapshot initialization has the problem via the windows app, thereafter it works fine.
I've ended up giving just about everything everywhere full permissions...in the database, files/folders etc.
I've tried running the app as x64 with these dll references:
Microsoft.SqlServer.Replication.dll, from
C:\Windows\Microsoft.NET\assembly\GAC_64\Microsoft.SqlServer.Replication\v4.0_14.0.0.0__89845dcd8080cc91
Microsoft.SqlServer.Rmo.dll, from
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.Rmo\v4.0_14.0.0.0__89845dcd8080cc91
Microsoft.SqlServer.ConnectionInfo.dll, from
C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\14.0.0.0__89845dcd8080cc91
...and tried running the app as x86 with these dll references:
Microsoft.SqlServer.Replication.dll, from
C:\Windows\Microsoft.NET\assembly\GAC_32\Microsoft.SqlServer.Replication\v4.0_14.0.0.0__89845dcd8080cc91
Microsoft.SqlServer.Rmo.dll, from
C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies
Microsoft.SqlServer.ConnectionInfo.dll, from
C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies
Both end up with the same error.
I have tried just about everything for 2 weeks now and cannot resolve this issue. It would be awesome if someone could help find a solution!
Thanks!
November 8, 2017 at 6:59 am
I don't suppose that processing the response message requires Kerberos authentication because of the remote server? This is a total stab in the dark..
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 9, 2017 at 7:48 am
sgmunson - Wednesday, November 8, 2017 6:59 AMI don't suppose that processing the response message requires Kerberos authentication because of the remote server? This is a total stab in the dark..
I wouldn't have thought so as after the database initialization, the responses are processed fine which is using exactly the same process. It's just the initialization bit that fails to work.
November 9, 2017 at 7:58 am
zee.internet - Thursday, November 9, 2017 7:48 AMsgmunson - Wednesday, November 8, 2017 6:59 AMI don't suppose that processing the response message requires Kerberos authentication because of the remote server? This is a total stab in the dark..I wouldn't have thought so as after the database initialization, the responses are processed fine which is using exactly the same process. It's just the initialization bit that fails to work.
From what you posted initially, I thought this is more of an issue with the permissions difference in the Windows application vs doing it through SSMS.
Can't say where exactly in the Windows application the problem lies though.
Sue
March 13, 2019 at 7:36 am
Was there ever a resolution to this?
Here's my similar situation:
Windows WPF App - compiled against those dlls from SQL2008R2 - works fine.
Compile against those dlls from anything higher than 2008r2 and I get the same results as OP.
If I use the command line replmer.exe it will successfully do the create from snapshot, and then my WindApp will sync just fine.
dlls in quesiton:
Microsoft.SqlServer.Replication
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Rmo
George
May 17, 2021 at 11:56 pm
Did you get a resolution to your issue George?
We are facing the same issue.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply