Installing Remote Data Connectivity

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
9/20/2010 9:40:07 AM
If you will be using Data Synchronization Services only, you can skip this section and proceed directly to the Using Data Synchronization Services section. People familiar with IIS recognize that the device-to-IIS connection can be made in Anonymous, Basic Authentication, or Integrated Windows Authentication mode; people familiar with SQL Server know that the IIS-to-SQL-Server connection can be made in Windows Authentication or SQL Server Authentication mode. All of this results in a connectivity/security chain that involves your SQL Server CE application, the network, the operating system, IIS, NTFS, SQL Server, and, in the case of Merge Replication, a shared directory.

1. Creating the Virtual Directory

As we stated, three security contexts exposed by IIS can be used with SQL Server CE (Anonymous, Basic, and Integrated). Of the three, Integrated is the most restrictive when using SQL Server CE because it requires having IIS and SQL Server on the same machine. This is because NTLM does not support proxying security credentials through machines. Kerberos Authentication does support this in Windows, but support for Kerberos is not available on Windows CE devices. As with any Web-based application, if you use Basic Authentication, it is recommended that you also use SSL (i.e., Web connections should use HTTPS instead of HTTP as the transfer protocol) to prevent the username and password from being deciphered between the device and the IIS box.

Before you begin to code the application, you have to address a number of standard Web development issues. You must decide the following: Where will the ISAPI extension .dll file be located? Where will the SQL Server be located? Will there be a firewall or proxy server between them? What operating system user should the Server Agent execute as? How do you ensure that that operating system user is a valid SQL Server login? Should there be multiple sites, each with its own security? Or should there be just one site, one connection point, for all the applications?

These questions include the typical setup procedures for doing any Web-based application that also has back-end database support (setting up a virtual directory, installing the server-side components in the virtual directory, choosing the authentication modes for the virtual directory, assigning user rights, and setting up the necessary security and access rights for gaining access to SQL Server). It is not our intent to repeat the details for doing this; you can find that information in the SQL Server CE Books Online, located at Rather, we’ll take a high-level look at the SQL Server CE application to IIS to SQL Server chain.

Fortunately, SQL Server CE provides a tool for creating and configuring the Web server to be accessed by client devices. That means you must install the server-side component of SQL Server CE and then use it to install the IIS site(s). Books Online refers to this as “Installing SQL Server CE,” but it is really installing the server-side components of SQL Server CE.

You can find the software and installation programs at one of two locations. The root location for device software on your machine is Program Files\Microsoft.NET\SDK\CompactFramework\vN.N\WindowsCE. If you do not have the software already on your machine, you can go to and obtain the necessary connectivity tool. This site contains the tools and setup instructions for every common combination of SQL Server CE version and SQL Server version, including both the SQL Server and IIS on the same machine scenario and the SQL Server and IIS on different machines scenario.

But installing the Server Agent does not yet mean you can transfer data between the device and the desktop. Because this transfer is done via the Internet, you must establish one or more sites on your IIS machine for use by SQL Server CE. Into each site you must install the sscesaNN.dll file, and you must set the appropriate permissions for that site.

You can reach the program that helps you do this, the SQL Server Connectivity Management program, from the Start menu entry shown in Figure 1. This management application allows you to reach the Virtual Directory Creation Wizard, which helps you configure the site. To configure a new site from the SQL Server Connectivity Management program, select Microsoft SQL Server CE in the left-hand panel and click on Create a Virtual Directory in the right-hand panel, as shown in Figure 2. To modify an existing directory configuration, select the directory from the right-hand panel.

Figure 1. After Server-Side Installation of SQL Server CE

Figure 2. Launching the Virtual Directory Creation Wizard

The wizard walks you through five self-explanatory dialog boxes. Through the wizard you specify the name and location of the virtual directory, choose the authentication method, and optionally configure and share the snapshots directory needed for Merge Replication. The wizard creates the Web site and virtual directory, copies the sscesaNN.dll file to it, and registers sscesaNN.dll. It does not configure NTFS permissions, add domain users and groups, add SQL Server logons, or generate code. You or an appropriate administrator must perform those tasks after analyzing the needs of the site.

For instance, the need to provide read-only access to public data usually results in the following:

  • A site that allows anonymous access

  • A SQL Server logon for the IUSR_MachineName user

  • A SQL Server that allows SQL Server logon authentication

  • Application code that does not specify any of the change-tracking options

Authenticated access to corporate data might be accomplished with the following:

  • A site that uses Integrated Windows Authentication and SSL

  • A new SQL Server logons group

When you have finished configuring a Web site, you can test it by opening your browser and navigating to http://<server name>/<virtual directory name>/sscesaNN.dll, as shown in Figure 3. If you receive the response shown in the figure, your site installation was successful.

Figure 3. Configuration Test Showing Successful Site Installation

If you specified a security model that required authentication and if you were not currently logged on when you navigated to the site, you were asked to enter your username and password. Whatever URL provides access to the Server Agent is the URL to specify in your application code. Whatever username and password you specified to log on is the username and password to specify in your application code.

If you are unable to browse to the site from your device, try browsing from the server machine, using localhost as the server name. If this local access attempt fails, the problem lies in the Web server configuration. Return to the SQL Server Connectivity Management program and check the properties and spellings of your configuration.

If local access succeeds, the problem lies in configuring your device. From your device, try to browse to a known site, such as If that fails, you have a general network connectivity problem. If it succeeds, the problem lies in connecting your device specifically to your server.

2. Configuring Additional Components

The IIS authentication configuration that you specify determines the username that the SQL Server CE Server Agent uses when it connects to SQL Server and when it accesses the shared directory required by Merge Replication. This user must be able to log on to SQL Server and access the directory, which in turn must be shared. Your application is ready to connect when you have accomplished all of the following tasks.

  • All operating system users and groups, if any, have been added.

  • All SQL Server logons and groups have been added.

  • The directory has been shared.

  • Share permissions have been granted.