As we have pointed out earlier, the ability to secure HTTP traffic between remote clients and an IIS server relies on a digital certificate associated with a target Web site. To obtain it, it is necessary to create a request that is submitted to a Certificate Authority. Once its processing is successfully completed, the resulting certificate is imported into the Web site (note that subscribers need to trust the Certificate Authority in order to accept certificates it issued, which can be accomplished by installing its own certificate in the Trusted Root Certificate Authority node of their local certificate stores). There are three basic methods of implementing this procedure:

• submit an online request to a Certificate Authority using "Send the request immediately to an online certificate authority" option of IIS Certificate Wizard (invoked via the Server Certificate... command button on the Directory Security tab of the target Web Site Properties dialog box). Providing that the CA is accessible and configured (via its policy module) to automatically issue a certificate, its installation will be performed during the final stage of the wizard. This is the simplest way of handling this task and the one that we have described previously.
• submit an offline request to a Certificate Authority using the "Prepare the request now, but send it later" option of the IIS Certificate Wizard. While you will be prompted to provide the same information as in the first case (with the online approach), your settings will be stored in a certificate file (saved to an arbitrary location specified on the Certificate Request File Name page), which you can submit to a CA once that becomes available.

In the case of a Windows-based Public Key Infrastructure, you can use the Certificate Services Web site (accessible typically via http://CAServerFQDN/certsrv, where CAServerFQDN designates the fully qualified domain name of the computer hosting Certificate Authority) for this purpose. On its initial page, select the "Request a certificate" link, followed by "advanced certificate request". Next, click on "Submit a certificate request by using a base-64-encoded CMC or PKCS #10 file, or submit a renewal request by using a base-64-encoded PKCS #7 file" entry. The "Browse for a file to insert" option appearing in the Saved Request section will allow you to point to the request file you created earlier (alternatively, you can open it in Notepad and paste its entire content directly into the textbox on the page). In the Certificate Template section, select the Web Server entry and click on the Submit command button. Assuming that the CA policy module is configured for immediate processing, you will be able to download the resulting certificate in either DER encoded or Base 64 encoded format once you reach the next Web page. (Otherwise, you will need to connect to the Certificate Authority via the management console and accept the appropriate entry in the Pending Requests folder in order to issue the certificate). Pick the desired format and specify the download location, saving the file with an arbitrary name (or certnew.cer default).

Return to the Directory Security tab on the Web Site Properties dialog box and click on the Server Certificate command button. This will invoke the IIS Certificate Wizard, prompting you whether to delete the pending request or process it and install the certificate. Choose the latter and provide ther location of the certificate file. Next, specify the SSL port that the site will be using, review and verify settings on the Certificate Summary page, and click on the Finish command button to assign them.

• submit a request to Certificate Authority without using the Web Certificate Wizard. This can be done by employing the Certificate Services Web site-based process. Start by connecting to http://CAServerFQDN/certsrv, choose the link labeled "Request a certificate" on the initial page, followed by "advanced certificate request" and "Create and submit a request to this CA". This will trigger a display of the Advanced Certificate Request form. Pick the Web Server entry in the Certificate Template listbox and populate the Identifying Information, Key Options, and Additional Options sections with the appropriate values (matching the ones you entered via the IIS Certificate Wizard). Once you have completed this step, click on the Submit command button to deliver your request to the Certificate Authority. Assuming that the CA policy module allows for automatic processing, you will be able to install the corresponding certificate in the local certificate store (otherwise you will have to approve the request via the Certificate Authority management console and download it manually), and import it afterwards into the target Web site using the "Assign an existing certificate" option in the IIS Certificate Wizard.

Another factor that you should keep in mind when planning implementation of Web Synchronization for merge subscriptions is the location and mode of operation of your remote clients. In our initial demonstration, we have conveniently assumed that subscriptions already have been set up prior to running REPLMERG.EXE for the first time (which implies that our subscriber had direct access to the publisher), however you might have to deal with scenarios, in which this is not the case. While creating a subscription in the manner we described earlier (via New Subscription Wizard from SQL Server Management Studio Express interface) is not possible without direct RPC-based connectivity between replication partners (which applies to remote, Internet-based clients), there are alternative methods of accomplishing this goal. One of them (which we will describe here) involves sp_addmergesubscription and sp_addmergepullsubscription replication stored procedures (on the publisher and subscribers, respectively). Providing that the merge publication based on the SalesTaxRate(Sales) table in AdventureWorks database already exists on the distributor/publisher located on the default SQL Server 2005 Enterprise Edition instance hosted on the computer ALPHA, and that our SQL Server 2005 Express Edition-based subscription database called AdventureWorksRepl resides on computer OMEGA, you would start by executing the following on ALPHA (which generates a pull subscription entry):

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
DECLARE @hostname AS sysname;
SET @publication = N'SalesTaxRate(Sales)';
SET @subscriber = N'OMEGA\SQLEXPRESS';
SET @subscriptionDB = N'AdventureWorksRepl'; 
SET @hostname = N'OMEGA'
USE [AdventureWorks]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'pull',
  @hostname = @hostname;

Keep in mind that, as before, you need to perform additional steps to enable Web synchronization of this publication, including successfully completing the Web Synchronization wizard resulting in the creation of its virtual directory, as well as adding a URL pointing to the replisapi.dll in the FTP Snapshot and Internet section of its Properties dialog box (for details regarding this procedure, refer to our previous article). Next, apply sp_addmergepullsubscription stored procedure to the subscriber in order to create the corresponding entry in its Local Subscriptions folder by running the T-SQL code listed below:

DECLARE @publisher AS sysname;
DECLARE @publication AS sysname;
DECLARE @publisher_db AS sysname;
SET @publisher = N'ALPHA';
SET @publication = N'SalesTaxRate(Sales)';
SET @publisher_db = N'AdventureWorks';
USE [AdventureWorksRepl]
EXEC sp_addmergepullsubscription 
 @publisher = @publisher, 
 @publication = @publication, 
 @publisher_db = @publisher_db;

At this point, you should be able to trigger synchronization using the REPLMERG.EXE utility (residing in the Program Files\Microsoft SQL Server\90\COM folder) in the same manner as described earlier. Note, however, that the syntax we used leverages Basic authentication to validate credentials of the user connecting to the Web Server (which, in turn, are relayed to the SQL Server 2005-based distributor/publisher). In the next installment of our series, we will describe other authentication mechanisms that can be employed in this arrangement.