Setting up SqlCe Merge Replication with ISA Server in the middle.

Wow! I had to venture into the ‘cave’ and solved this problem – talk about a character
building experience!

I’m currently building a Mobile BizTalk RFID 1.1 solution for TechEd08 that
runs on a PPC with a Kenetics
CFUHF Reader.

*** Early Screen Shot *** 🙂

So in building out this application the details always bring unforeseen challenges
to light:

1) The application houses all the BizTalk RFID pieces (providers, device proxies etc)
so registration, and starting/stopping providers/device discovery and applying properties
to the device needs to be all taken care of.

2) I built an RFID Mobile Provider for the Kenetics device – I worked with their support
engineers solidly for a week to build what I needed. I took a trip down memory lane
and have had enough pinvoking to last till Christmas.

3) The app also manages a several local SQLCe databases – one for my app, the others
for the operation of BizTalk RFID Mobile locally on the device (mainly for it’s OOTB
store/forward mechanism).

After weighing up several options in this solution and how to get data to/from the
device reliably I decided to go with SqlCe Merge Replication as we needed to push/pull
data from several tables and schema changes.

4) Which leads me onto one of the most little known items……

How do I setup SqlCe Merge replication? it’s a mine field, change
something here and boom over there.

The picture

Phase 1:

Forget ISA for the moment. If you can, aim to get replication running in a local environment
first (e.g. Local LAN on same network, through VPNs etc)

Getting the SQL bits Setup

Ok – the pieces to the initial puzzle…..

Sql Server Side
1. Sql Server and it’s additional Sql Mobile Replication Bits – download
  from here.
2. IIS to expose a replication ‘end point’ where the remote devices will connect to and
  replication will take place through. IIS can be separate out onto a different machine.
3. As in my case, somewhere that the ‘snapshot’ DB information will live to merge down
  to the devices. Mine was a UNC share – SQL created this after I completed the Publication
  wizard.
4. Installation -You want the SQL
  Server Compact 3.5 Server Tools installed on BOTH the IIS AND SQL Machines (if
  these are one and the same, then you only need it once)
  The server tools has two main components – one being the bits that drive IIS and the
  other being a wizard that configures the exposed virtual directory and sets security
  onto it.
  If IIS and SQL are on separate machines, the easiest way to go is:
  – get SQL to publish the snapshot to a UNC share e.g. \\sqlserver\data
  
  – On the IIS box, run the Configure Web and Synchronization Wizard (installed
  with the above server tools) and a later screen will ask you where this data is coming
  from – simply point to the UNC share.
Mobile Device Side
1. The equivalent SQL Mobile Replication tools need to be installed (above and beyond
  just normal SqlCe database components install) – SQL
  Server Compact 3.5 for Windows Mobile
  
  *** NOTE: make sure that the bits on both the Mobile + Servers all match ***
Server Side Security – For this let’s work backwards, from the publication
through to the exposed endpoint.
1. Publication Security – this is set through the Publication Access List within
  SQL Mgmnt Studio
  The group in question is the ExhibitorsGroup
  
  Create a publication within the SQL Management Studio
  
  (Publication General Properties)
  
  (Snapshot Properties – note the file location)
  
  (FTP Snapshot + Internet – I’ve just used Internet and no IIS server name as this
  is configured in the Mobile Wizard)
  
  (Publication access list – I’ve blanked out sensitive info, but you can see the BETDEV\ExhibitorGroup
  being manually addded to the list)
  The rest of the publication settings are defaults – for me anyway.
2. Let’s go to the UNC share – = C:\Public\Exhibitor.SqlCE.FileShare
  This is the UNC share that IIS repl component will connect to at the back
  end.
  Note: the BETDEV\ExhibitorsGroup obviously needs r/w access to this folder.
3. Let’s run the ‘Configure Web and Synchronization Wizard’ to configure
  the IIS component.
  (you’ll find it off the tools menu after you’ve installed the Mobile Server Tools
  from the links above)
  Note: one of the interesting things I found here is that after running
  the wizard, I normally go a tweak a few things in IIS – directory browsing etc. As
  a rule of thumb, if you want to change something with the Virtual Directory that is
  created at the end of this wizard, re-run the wizard to do it!!! 🙂
  
  Press next if prompted with the welcome screen note my options here – SQL Mobile and
  press Next.Cool
  
  Select the site and Create a Virtual Directory (I’m re-running the
  wizard so I’m going to select Configure Existing). Press Next.
  
  I created an alias of SqlCERepl directory and accepted a sub-directory
  under the SqlMobile dir.
  (you can change this, but looking around the forums it was a source of grief – I could
  do without 🙂 )
  
  Here – I selected HTTP and not HTTPS access to the VirtualDirectory (and
  SQL Service agent).
  I did this as if you remember the diagram at the top of this post, ISA will
  serve as the HTTPS endpoint and will fwd the request via HTTP to
  our IIS/SQL box.
  IF you do want to change from HTTP to HTTPS or visa versa – re-run this wizard. Save
  you about 4 hrs of head banging.
  Click Next when ready.
  
  On this page – I selected ‘Authentication required’ and not anonymous. This has something
  to do with the data that I’m replicating as I’m using a Filter based on ‘UserName’.
  So in my case, the username that the devices connect with will be my differentiator
  (I looked into using something like ‘deviceID’ but didn’t get too far)
  Click Next.
  
  Select the type of authentication to be made against IIS – I selected NTLM (basic
  is fine also – but you need to be mindful that we’re using HTTP at this point)
  Quick note on Security: So far, we’ve got 2 areas that need authentication.
  
  1) the IIS virtual directory and 2) accessing the actual SQL Publication in the UNC
  share and SQL Publisher Access List.
  
  So if the two machines are separated (IIS + Sql), NTLM will no transverse these machines
  (known as the ‘double-hop’ problem) so I’m assuming Basic or Kerberos is the safer
  bet.
  Click Next when ready.
  
  On the Directory Access Screen note the presence of the ExhibitorsGroup
  and also this publication is accessing the UNC Share we created earlier.
  Next to continue.
  
  UNC path specified – here you can see how this could be pointing to this SQL Share
  sitting on another machine as in the 2 machine hosted scenario.
  Click Next and Finish to see something like:
  
  You’re virtual directory is now configured.
  To test your configuration so far go to:
  http://<server>/sqlcerepl/sqlcesa35.dll?diag –
  diagnostics screen to get something like:
  You should be prompted to login – enter account details that have
  access.
  
  This is our fallback screen – next we will configure the ISA component and come back
  to our test screen to make sure.
  You’re done – here. 🙂
Configure ISA Server
ISA server will be the bridge between our public SSL access and our internal
IIS/SQL Server. We would effectively like ISA to simply route the request and pass
it through without to much tampering with our good packets.

ISA Server is on IP address: IP:Y_Internal
The Internal Server here is : 10.1.0.191
The public Interface on the ISA Server is for our purpose known as IP:X_Public
and it’s FQDN is : demo.micks.org (in otherwords – this is the
public DNS name that will point to the public interface of your ISA box)

NOTE: Make sure you have your SSL cert ready – I created an inhouse
cert from a standalone cert server.
You need at least a ‘Server Authentication’ Certificate to apply within ISA.
(I’ll show you a little trick in the mobile app to get round the fact that the certificate
is from a non-trusted Cert. Authority by default)
The friendly name on the cert should be – ‘demo.micks.org’ (without
the quotes)
All this keeps SSL happy.
1. Create a publishing rule in ISA 2006 that will effectively route all requests
  coming to the public interface to our internal IIS/SQL Server.
2. Fire up the ISA MMC and create a New Web Server Publishing Rule –
  I’ve called this sample rule, “Public to Internal IIS/SQL Repl”
  
  Click Next when done.
3. Rule Action – set to Allow
  
  Next
4. Publishing Type=Single Web
  
  Next
5. Server Connection Security – SSL.This means that SSL is going to be used over the
  public network.
  
  Next
6. On the Internal Publishing Details – I tend to hardcode the IP address in, just to
  reduce any ambiguity.
  Note the IP address – internally acessible only. 10.x.x.x
  
  Next
7. Further settings on the Internal Publishing Details
  NOTE: the option of fwding the original client host headers to the internal
  IIS/SQL (I found a variety of incomplete HTTP Header details errors attempting
  to sync if I cleared this checkbox)
  
  We also can restrict the access on this rule by specifying the path of /SqlCeRepl/* (this
  is obviously the Virtual Directory created earlier)
  
  Next
8. Fill in your public DNS name – don’t worry that the wizard screen is showing http://demo.micks.org and
  NOT https://demo.micks.org
  
  Next
9. Create a listener (if you need to ) as follows:
  (I’ve modified the screen shot slightly – from my listener)
  Note the ports: 8443 that SSL requests is coming on. You can use 443 if you prefer,
  I had other things on those ports)
  Also – I setup NO Authentication and replication works. You *could*
  try setting up Basic Authentication here and using Delegated Authentication (ISA server
  will login to the IIS/SQL box on your behalf with the inputted security credentials).
  
  I’ve also supplied the Certificate here as well (add your cert to the machine store
  ahead of time)
  
  A way to test if your auth is going to work – fire up your browser and try http://<server>/sqlcerepl/sqlcesa35.dll?diag
  
  You should be prompted for login details ONLY ONCE. If you need to supply
  them twice and then you see the diagnostic page, your mobile application
  replication will fail :-(. Once and once only.
  
  Next.
10. Authentication Delegation- we want the client to auth. directly against the backend
  (routed through ISA of course 🙂 )
  
  Next.
11. User Sets – because we don’t have authentication here, ISA can’t
  determine users, so All Users is our only option.
  
  Next.
12. What a glorious site….almost done……
  
  Click Finish to complete the wizard.
13. Right click on the rule just created and select Properties – we need
  to change the Link Translation to OFF
  
  This was the major source of my grief – I kept getting ‘HTTP Headers malformed…’
  ERROR:28035 when trying to sync from the Device – yay!
  
  I was fortunate to be able to contact a friend of mine Darren
  Shaffer (Mobile MVP) that explained what was required to be sent back/forth in
  the headers during the conversation – big thanks there Darren!
14. You should be able to browse to https://demo.micks.org/sqlcerepl/sqlcesa35.dll?diag –
  it should WORK 🙂
  If not – resolve before moving on. (you may get IE grumbling about the Certificate
  being invalid if it’s an inhouse cert)

Configure the MOBILE replication piece!!!

Make sure you have installed the SQL CE 3.5 Core + Repl CABs at least.

On the mobile device, I tend to have routines to Add and Remove
DB Subscriptions as I found that if any publication changes on SQL Server
happened – e.g. a field was modified, or a table added/removed from the Publication,
then Merge Repl would fail even though it previously was working.

Easier to Remove the Subscription on the local SQLCE db, and then add it again.

Note: InternetUrl = https://demo.micks.org

Username + pass must be a user that has access to all the bits we configured above.
In my case, someone who is a member of the ExhibitorsGroup.

The code looks like this:

 1:  public void AddReplAndSync()

 2:  {

 3:  //using System.Data.SqlServerCe;

 4:  bool bAddRepl = false;

 5:  try

 6:  {

 7:  if (DoDBLookup("SELECT
count(*) as cRow FROM __sysMergeSubscriptions WHERE Subscriber='ExhibitorSubscription'", "cRow")
!= "1")

 8:  {

 9:  bAddRepl = true;

 10:  }

 11:  }

 12:  catch

 13:  {

 14:  bAddRepl = true;

 15:  }

16:

 17:  SqlCeReplication repl = new SqlCeReplication();

 18:  repl.InternetUrl = AppSettings.Settings.ReplServer
+ "sqlcesa35.dll";

 19:  repl.InternetLogin = AppSettings.Settings.ReplUser;

 20:  repl.InternetPassword = "XXXXXX";

21:

 22:  repl.Publisher = AppSettings.Settings.ReplPublisher;

 23:  repl.PublisherDatabase = AppSettings.Settings.ReplPubDB;

 24:  repl.PublisherSecurityMode = SecurityType.NTAuthentication;

 25:  repl.Publication = AppSettings.Settings.ReplPubName;

 26:  repl.Subscriber = AppSettings.Settings.ReplSubName;

 27:  repl.SubscriberConnectionString = string.Format("DATA
SOURCE='{0}'", ESDAL.GetDBPath());

28:

 29:  try

 30:  {

 31:  if (bAddRepl)

 32:  repl.AddSubscription(AddOption.ExistingDatabase);

 33:  CloseAllDBConnections();

 34:  repl.Synchronize();

 35:  }

 36:  catch (SqlCeException
e)

 37:  {

 38:  MessageBox.Show(e.ToString() + e.NativeError.ToString());

 39:  }

40:

 41:  }

42:

 43:  public void ReplRemove()

 44:  {

 45:  CloseAllDBConnections();

 46:  SqlCeReplication repl = new SqlCeReplication();

 47:  repl.SubscriberConnectionString = string.Format("DATA
SOURCE='{0}'", ESDAL.GetDBPath());

 48:  repl.InternetUrl = AppSettings.Settings.ReplServer
+ "sqlcesa35.dll";

 49:  repl.InternetLogin = AppSettings.Settings.ReplUser;

 50:  repl.InternetPassword = "XXXXXX";

 51:  repl.Publisher = AppSettings.Settings.ReplPublisher;

 52:  repl.PublisherDatabase = AppSettings.Settings.ReplPubDB;

 53:  repl.PublisherSecurityMode = SecurityType.NTAuthentication;

 54:  repl.Publication = AppSettings.Settings.ReplPubName;

 55:  repl.Subscriber = AppSettings.Settings.ReplSubName;

 56:  try

 57:  {

 58:  CloseAllDBConnections();

 59:  repl.DropSubscription(DropOption.LeaveDatabase);

 60:  }

 61:  catch (SqlCeException
e)

 62:  {

 63:  MessageBox.Show(e.ToString() + e.NativeError.ToString());

 64:  }

 65:  }

66:

 67:  private void CloseAllDBConnections()

 68:  {

 69:  if ((_dbCon != null)
&& (_dbCon.State != ConnectionState.Closed))

 70:  {

 71:  _dbCon.Dispose();

 72:  _dbCon = null;

 73:  GC.Collect();

 74:  }

75:

 76:  }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Trick to deal with Inhouse generated certificates –

Within your mobile app we create a class that essentially returns True when
asked ‘Is this Cert. valid?’

Somewhere upon starting up your app – e.g. Form_Load – insert LINE#1 below.

LINE#3 onwards describes the class ‘MyCustomSSLPolicy’

 1: System.Net.ServicePointManager.CertificatePolicy
= new MyCustomSSLPolicy();

 2: ......

 3: using System;

 4: using System.Collections.Generic;

 5: using System.Text;

 6: using System.Net;

 7: using System.Security.Cryptography.X509Certificates;

8:

 9: namespace MicksDemos.Utilities

 10: {

 11:  public class MyCustomSSLPolicy
: ICertificatePolicy

 12:  {

 13:  public bool CheckValidationResult(ServicePoint
srvPoint,

 14:  X509Certificate certificate, WebRequest request, int certificateProblem)

 15:  {

 16:  return true;

 17:  }

 18:  }

 19: }

Closing note:

Hope you find this useful – I’ve done this a few times now and am amazed with the
lack of info around this especially through ISA.

If you get any errors around “Can’t contact SQL Reconciler…” etc errors – GENERALLY
try and rebuild the snapshop server side, then try syncing again.

Nighty night!

Setting up SqlCe Merge Replication with ISA Server in the middle.

Search this Site:

Recent Posts

Recent Topics