SQL Merge Replication over SSL

I’ve spent the last few days pulling my hair out trying to get SQL Merge replication over SSL working on SQL Server 2008 R2.  The situation I have is basically I have a SQL server behind our corporate firewall with some data.  We are setting up a SQL VM on Azure and need to constantly replicate data out to the Azure server.  The only built-in option in SQL server is to perform merge replication with the help of an IIS server.

To understand the setup you can refer to this msdn article, it does a decent job so I’m going to retype it.

There are a few catches and gotcha’s, that drove me crazy and I had to scour the web to find answers on why things weren’t working.

These are the things that I used, it may not be best practices but it worked out in the end:

  • SQL Server 2008 R2 (with source data, inside corporate firewall) (Publisher)
  • SQL Server 2008 R2 Azure VM (server to have data replicated to) (Subscriber)
  • Windows 2008 R2 with IIS installed (WebServer)
  • SSL Cert for above web server (not self-signed)
  • Domain Account, used to access the publication
  • Local Account, on WebServer for IIS application pool
  • Publically available dns name (I’ll use https://replication.domain.com)
  • SQL Management Studio (SSMS)

Setup of WebServer

All the instructions I read really only covered configuring the website in IIS for replication, but no one really described what you needed from scratch.  I started with a fresh install of Windows 2008 R2.

  1. Add the “Web Server” role with the following features (in addition to default features):
    • Application Development > ISAPI Extensions, ISAPI Filters
    • Security > Basic Authentication
    • Management Tools > IIS Management Console, IIS Management Service
  2. Configure IIS for Web Synchronization, this is a great step by step at msdn.  While going through this guide, the 2 accounts they mention are the ones listed above in what you need.  The domain credentials are the ones you will use at the end to test the replisapi.dll connection, once basic authentication is enabled.

There were however 2 issues when following the msdn article.

  • If you are on a 64-bit machine, the replisapi.dll may not be the correct version, if you are receiving an “HTTP 500.0 – Internal Server Error”.  The two versions are different sizes and I believe are compiled for 64bit and 32bit.  Even if you follow the instructions and use the wizard, it still copied the wrong version in my case. I believe I had to copy the version fron the x86 directory.
    • C:\Program Files\Microsoft SQL Server\100\COM\
    • C:\Program Files (x86)\Microsoft SQL Server\100\COM\
  • If you get a plain page with “Access Denied” in when going to http://replication.domain.com/replisapi.dll?diag , it is most likely the fact that the credentials you use need to be an Administrator on the WebServer.  In my case, I was using the domain credentials, so I had to add those to the local Administrators group.
    Hopefully at this point, you can see the replisapi.dll?diag page and we can move on to configuring the replication.

Configuring Replication

Part of the trick wit this step is that you apparently have to setup and do the initial replication locally, you cannot setup replication over SSL until after you setup both the Publisher and Subscriber.  Once setup you simply change over the settings and theoretically it’s good to go.

This part of the setup is fairly straight forward.  On the publisher, open up replication and add a new “Local Publication”, be sure to select “Merge Replication”.  If you select the default location, make sure you share the directory and call it “ReplicationSnapshots”:

Snapshot Directory: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\repldata\unc\[server_snapshot name]

Share Directory: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\repldata

Share UNC Path: \\[servername]\ReplicationSnapshots

Be sure to add your domain credentials to the directory security and share permissions with read only permissions.

On the subscriber, if it’s off site (ex. SQL Azure VM), you’ll need to connect into your corporate network using vpn to do the initial configuration.  In SSMS, open up replication and add a new “Local Subscription”.  You will be pointing it to the publisher and selecting merge replication.  Once you finish the wizard, it will fail the first time because the default directory it is looking at for the snapshot is inaccessible.

For some reason or another it sets it up using the local path, instead of the unc path.  Simple fix, right click on the subscription and click on properties.  Scroll down to “Snapshot” section and change “Snapshot location” from default to “Alternate Folder” and fill in the “Snapshot folder” to the unc path shared above.  Once this is complete open up the “Synchronization Monitor” and run it, it should replicate the data to your subscriber.

Configure Web Synchronization

If you’ve made it this far, then you are almost done!  Now that the merge replications are working, we just need to point it to our WebServer and have it make it’s data requests through the server.

First we’ll configure the Publisher.  In SSMS, open up the replication folder and right click on the Local Publication you created earlier and select “Properties”.  Check the “Allow Subscribers to synchronize….” and fill in the address for the web server.

image

Next we’ll configure the subscriber.  It’s just as simple as with the publisher.  Open up a connection in SSMS to the subscriber and go to the replication folder.  Right click on select “Properties” on the subscription created before.  Scroll down to the “Web Synchronization” section and change “Use Web Synchronization” to true, fill in the Web server address, Change the Web Server Connection to use your domain credentials (it should change to “basic Authentication” as seen below).  You should be able to run the synchronization from the subscriber without vpn now.

image

Sit back and let the kudos roll in

Theoretically, if nothing funky goes wrong, you should be ready to go.

Update: So, I appeared to have overlooked one important detail.  In my case, my subscriber is a SQL Azure VM (using SQL authentication) and my publisher is a local VM using Integrated Authentication.  Subscriber is not part of the domain, publisher is part of a domain.  And if you’ve used SSMS in the past, you’ll know that it doesn’t let you enter domain credentials when selecting integrated authentication.  So how do we solve this problem?  I found this great little solution posted here.

The basic solution is this:

runas /netonly /user:DOMAIN\username ssms.exe