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 http://msdn.microsoft.com/en-us/library/bb734673.aspx. 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 www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx
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.
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:
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.
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 www.microsoft.com.
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.
