SQL2005: Configuring Web synchronization for merge replication
I'm trying (and failing!) to get Web synchronization for merge replication working with SQL Server Standard 2005 Sept CTP. I've created and configured a merge publication and have configured the publication for Web synchronization using the 'Configure Web Syncrhonization' wizard. I've followed all the setup instructions in the
Configuring Web Synchronization article in Books Online and have tried testing that it works by accessing https://<machinename>/syncvdir/replisapi.dll?diag
I can only get the diagnostic mode to work and return any information if I access the the replisapi URL using the credentials of an Administrative account on the Web/SQL server machine. If I try to access it from the account which is configured as the indentity of the app pool (as described in theConfiguring Web Synchronization article in Books Online) all I get when accessing the replisapi.dll URL is 'Access Denied'. This account is in the db_owner role for the publication and distribution databases in the SQL Server.
I've spent quite a bit of time trying to get this going and I must admit I'm stumped. I'd be very grateful for any advice offered as I need to make this work soon.
David
[1223 byte] By [
DavidG] at [2008-2-23]
Hi David,
Only an admin can get the diagnostic information. Hence in your case, the account which is running the app pool will not be able to access the diagnostic information.
However, that aside, you should be able to synchronize successfully. You should not be getting any errors when you try to synchronize with the specified (non admin account running the app pool). Make sure the account is part of IIS_WPG group.
Also, ensure that the InternetLogin account has read permissions on the snapshot share otherwise it will not be able to apply the snapshot.
If you are facing any synch problems, please let us know.
Hi Mahesh,
Thanks for the reply; that certainly explains why I can't run the diagnostic mode from a non-administrative account.
I've spent most of the last few days trying to get merge replication going using the Web synchronization and it's giving me a few problems.
The scenario I'm working to is this:- we need to replicate data to an outside partner organization whose users/machines are not members of our domains. Consequently I'm using a domain-account from our domain to create the snapshot at the publisher which runs on a machine on our domain. At the subscriber (at the partner) I'm using a local-machine Windows account to run the synchronization agent but the connection to the publisher is made using a SQL Authenticated account.
This all works perfectly using standard SQL merge replication but I can't make it work easily using the Web synchronization. I've enabled the publication for Web synchronization, run the Configure Web Synchronization wizard on the publication and checked that the various accounts, app pool, etc have the right permissions on the shares and databases. I've also set up a local-machine only Windows account which is the account used for the Internet logon. This account also has all the revelant permissions on the snapshot share and respective databases.
At the moment I'm testing from a subscriber machine that is actually on our domain; if I set the Subscriber synchronization agent to run under the identity of the domain level account that I'm logged in to the subscriber machine with, Web synchronization works correctly (this domain-level account has also been given permissions to access the snapshot share using the Configure Web Synchronization wizard). If I set the Subscriber synchronization agent to run under the identity of a different domain level account which has also been granted permissions using the Configure Web Synchronization wizard, Web synchronization doesn't work and I get the following error:-
The Merge Agent could not connect to the URL <url> during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable
The Web server exists and is reachable, the URL is correct as are the login credentials supplied (I have not changed the login credentials for any of these tests, just the identity of the Subscriber synchronization account). The same thing happens if I set the Subscriber synchronization agent to run under the identity of a local-machine account (which is our desired scenario). The strange thing is that nothing regarding these failed synchronization attempts ever gets recorded in the websync.log at the Web server. This suggests that the ISAPI filter doesn't even get invoked to handle the request and that a security issue is preventing the request being accepted.
The interesting thing is that all three of these accounts can be used quite happily as the security context for the Subscriber synchronization when standard SQL merge replication is used. I'm somewhat confused by all of this and would be grateful for any light you can shed on the matter.
David
Mahesh,
I've just run my tests again to triple check everything I said in my previous post. It turns out that standard SQL replication does NOT work properly if the Subscriber local-machine account is used as the synchronization agent security context when making the initial connection to the publisher. This consistently fails with an error message about the merge process being unable to clean up the subscription. If I change the synchronization agent security context to use a domain level account, the initial synchronization works correctly. I can then change the synchronization agent security context back to using my subscriber local-machine account and all subsequent synchronizations work correctly.
I'm guessing this problem is caused by a lack of understanding of the SQL replication security configuration on my part. How do I get round this so that all synchronizations, including the initial one, can be made with the Subscriber synchronization agent running under the context of a local machine account? Could this have a bearing on my Web synchronization problem?
Thanks again,
David
Hi David,
Here is what I understand:
You have an account X on the subscriber Machine that you want to use to run the sync with.
To do that, firstly the account X need to be atleast a member of the dbowner role of the subscriber database.
In addition, if you are using Windows authentication (i.e Integrated authentication by using PublisherSecurityMode 1 and DistributorSecurityMode 1, then this X is what will be used to connect to the Publisher and Distributor). But since your Publisher and Subscriber are on different domains, this may fail (unless instead of machine account, you use a domain account that is either in the same domain as the Publisher or is a trusted domain). Also note that the Publisher connection login should have atleast PAL (publication access list) privileges to the publisher database.
Now if you are using SQL authentication to connect to the Publisher/Distributor, your connections to the Publisher/Distributor will not have problems.
Next coming to the connection to the IIS server part. If you are using basic auth and have provided InternetLogin, this is what is used to connect to the IIS machine.
Instead, if you have not provided InternetLogin and using Integrated auth, then the context of X is used to connect to the IIS machine. Now if IIS machine is not aware of X (seems like in your case), then it will fail to even connect to the IIS machine and you may not find anything in websync.log because as you guessed, the reconciler is not even invoked.
Another thing to note is that when you have PublisherSecurityMode 1 and DistributorSecurityMode 1 and have provided an InternetLogin (basic auth), this is what will be used to connect to the publisher. And if your Publisher and IIS machines are different then you will need to enable Kerberos delegation on the IIS machine.
And if you are using integrated auth, then X is used to connect to the IIS and then also to the Publisher/Distributor.
Lastly, InternetLogin (when basic auth) or X (when integrated auth) is used to acess the snapshot share.
Now with this setup, you will need to choose which auth you want to use and what permissions you will need to give to each of the logins.
Hope this gives you a good idea of what logins are used to connect during synchronizing.
Thanks Mahesh, that helps a lot. I hadn't appreciated that when using InternetLogin and basic auth, the InternetLogin is used to access the snapshot share. I think that's the source of my problems.
I've been reading Books Online quite a bit recently whilst trying to get this going and I re-read the Replication Security Best Practices article which really helped. I've come to the conclusion that everything you need to know regarding setting up replication is detailed there in BOL. For my scenario at least I've had to read and inwardly digest the contents of almost every article on merge replication and replication security in order to make it work. Might I suggest adding an additional overview type article to BOL which simply lists some common replication scenarios and links readers off to the relevant BOL articles they need to read to make that scenario work (e.g. Implement merge replication between machines in same domain using Integrated Auth: read X, Y, Z; Implement merge replication between machines in a different domain: read A, B, C; Implement replication using Web synchronization with basic auth: read D, E, F, etc). The BOL content is great but I think this would really have helped me get to grips with some of my replication issues much sooner.
David
Hi David,
We are planning to add such information in BOL and other means as soon as we can. Thanks for your suggestions and I appreciate your feedback. We will do our best to incorporate all the changes you suggested.
