Security Models and Scenarios for SQL Server 2000 Windows CE Edition 2.0

Security Models and Scenarios for S...

Windows CE and SQL Server Technical Articles

Kevin J. Boske
Microsoft Corporation

November 2002

Summary: Learn how to set up Microsoft SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE) and Microsoft Internet Information Services (IIS) for connectivity security. Special emphasis is given to scenarios that are useful to application developers and testers, but not necessarily recommended in production environments. The paper covers requirements for multiple computers as well as for a single computer. After reading this document, you should be familiar with the details of setting up security and encryption on the server for use with SQL Server CE clients.

This paper assumes that you are familiar with the two types of connectivity supported by SQL Server CE (replication and Remote Data Access). (27 print pages)

Contents

Introduction
Getting Started
Getting Started Using the SQL Server CE Connectivity Management Utility
IIS Security Overview
SQL Server Security Overview
SQL Server CE Security Overview
Windows Security Overview
Single Server Scenarios
Multiserver Scenarios
Conclusion
References

Introduction

Microsoft® SQL Server™ 2000 Windows® CE Edition 2.0 (SQL Server CE) extends access to data to an organization's mobile users who are using the Windows CE mobile device. Using replication or Remote Data Access (RDA), you can share your data with disconnected users in the field. But in such a distributed environment, data can be even more difficult to keep secured than in a traditional centralized environment.

SQL Server CE relies on the security models of a combination of the following: Microsoft Internet Information Services 5.0 (IIS) or IIS 4.0, SQL Server 2000 (Service Pack 1 or later) or SQL Server version 7.0 (Service Pack 4 or later), and Microsoft Windows 2000, Microsoft Windows XP Professional, or Microsoft Windows NT® 4.0. In some environments, you will also need to consider Microsoft Internet Security and Acceleration Server 2000 (ISA) as well. As a result, there are many gateways through which a user must pass in order to connect from a device to SQL Server. For a more detailed description of the security models discussed in this paper, see Designing Secure, Web-Based Applications for Microsoft Windows 2000 (bibliographic information for this title is cited at the end of this paper).

Getting Started

You should have two servers running Windows 2000 Server (or Professional) and SQL Server 2000 Service Pack 1 or later to use merge replication. If you are using RDA, you can use SQL Server 7.0 (Service Pack 4 or later). Server A will act as a SQL Server and an IIS server, and it will require installation of SQL Server CE 2.0 Server Tools. Server B will act as a SQL Server and a Certificate Server (optional). If you want to use Certificate Services on Server B, you must install Windows 2000 Server. You will also need the SQL Server CE 2.0 Development Tools installed on your development machine, though it does not have to be on either of these servers. Both servers should be using the NTFS file system. Both servers should be members of the same domain. Differences between merge replication and RDA will be noted. See the following table for a description of the server setups.

Software	Server A	Server B
Windows 2000	Yes	Yes
IIS 5.0	Yes	No
SQL Server 2000	Yes	Yes
SQL Server 2000 Windows CE Edition 2.0 Server Tools	Yes	No
Certificate Services (optional)	No	Yes
SQL Server 2000 Windows CE Edition 2.0 Development Tools (optional)	Yes	Yes

Getting Started Using the SQL Server CE Connectivity Management Utility

New to SQL Server CE is the SQL Server CE Connectivity Management utility. This utility contains the SQL Server CE Virtual Directory Creation Wizard for creating new virtual directories, and also a management console for managing existing virtual directories. Both of these are used to configure NTFS permissions on the content folder containing the SQL Server CE Server Agent, the SQL Server CE Server Agent itself, and the SQL Server merge replication snapshot folder.

Important The SQL Server CE Connectivity Management utility is a development tool and should not be considered a replacement for understanding the security methodologies between SQL Server, IIS and SQL Server CE described in this paper.

The SQL Server CE Connectivity Management utility does not provide a method to configure SSL encryption. In some scenarios SSL encryption is highly recommended (as described later in this paper).

IIS Security Overview

The IIS security model supports three different authentication protocols, Anonymous, Basic, and Integrated Windows Authentication (referred to as Windows Authentication in this document). IIS also supports Secure Sockets Layer (SSL) encryption. Additionally, SQL Server CE supports 128-bit SSL encryption on the client database and on the IIS server. All Windows CE platforms supported by SQL Server CE (see SQL Server CE Books Online) support the three IIS authentication protocols.

Anonymous Access

Anonymous access, as the name implies, allows the client access to the IIS server resources anonymously. The IIS server does not require any authentication to allow access to resources. Anonymous access is best used in situations when the server does not need to keep track of visitors using the data, or when the data available does not need to be protected. Generally, Anonymous access is not recommended for SQL Server CE deployments, because it is unsecured.

Basic Authentication

Basic Authentication relies on part of the HTTP 1.0 protocol (for more information about this protocol, visit the Internet Engineering Task Force). Users must provide a valid Windows login and password, and IIS then logs into the system using the Windows account supplied as user name and password. If the login is rejected, the connection is closed and an error is returned to the client. By itself, Basic Authentication is not secure because logins and passwords are transmitted in Base64 encoding, which is relatively easy to read. For maximum security, Basic Authentication should be used with Secure Sockets Layer (SSL). Basic Authentication is supported in all Windows CE devices supported by SQL Server CE 2.0.

Secure Sockets Layer (SSL)

SSL uses an algorithm to encrypt data based on a trust relationship between the sender and receiver. SSL requires both the sender (the Windows CE device) and receiver (IIS server) to be trusted through a third party, a Certificate Authority. If you have ever ordered merchandise online, you are likely familiar with this type of encryption.

Note In order to use the SHA-1 encryption algorithm on Pocket PC, you will need to install the 128-bit encryption pack. The 128-bit Encryption Pack is available at the Pocket PC home page.

Windows Authentication

Windows Authentication allows the user to log onto a Web site using a Windows Domain Account. Because this authentication method requires a domain user account, it can only be used in an intranet solution. Windows Authentication uses a hashing algorithm to protect user login and password information when transferring over the wire.

When Windows Authentication is used, the client uses hashing to prove its knowledge of the password through a cryptographic exchange with the IIS server. Both the client and the IIS server are authenticated during the cryptographic exchange.

Note Windows Authentication cannot operate over a proxy server or firewall.

As the result of successful authentication, IIS adopts the identity of the client. All of the work that IIS performs on behalf of the client is done under the client's identity.

SQL Server Security Overview

SQL Server relies on two methods for authentication, Mixed Mode and Windows Authentication. Mixed Mode allows users to connect using either Windows Authentication or SQL Server Authentication.

Mixed Mode Authentication

In Mixed Mode, SQL Server can rely on its own security or that of Windows Authentication. SQL Server Authentication relies on a non-trusted security connection between the server and the client. In this mode, SQL Server CE must pass a valid login and password (separate from the Windows login) to SQL Server. This method is less secure than Windows Authentication. For example, the login sa with no password is the default login and password installed with the Mixed Mode Authentication of SQL Server version 7.0. This security method does not enforce a minimum password length.

Windows Authentication

Windows Authentication relies on a trusted Windows login, either at the local computer or a domain login for authentication to the SQL Server. When Windows Authentication is used, SQL Server requires the Windows user to be mapped to a SQL Server login and user.

SQL Server uses the IIS user's login and password, which is associated with a Windows account. For example, an IIS server using Anonymous access has an anonymous guest account that has an associated Windows login and password (IUSR_Machinename by default). The Sscesa20.dll will impersonate this user when connecting to SQL Server. SQL Server must have an associated login and password for any user requesting a connection. For example, if an IIS server uses Anonymous access, by default the SQL Server CE Server Agent will run under the IUSR_Machinename account. If the SQL Server is using Windows Authentication, the SQL Server administrator must add the IUSR_Machinename to SQL Server, giving permission to any databases used. For more information about SQL Server logins, see SQL Server Books Online. The following table describes which logins the SQL Server CE Server Agent and SQL Server use.

IIS Authentication	SQL Server Authentication	Windows Authentication
Anonymous Access	Login and password passed by SQL Server CE Client ActiveX® control.	Anonymous Guest Account (IUSR_Machinename) by default.
Basic Authentication with SSL	Login and password passed by SQL Server CE Client ActiveX control.	Authenticated IIS user.
Windows Authentication	Login and password passed by SQL Server CE Client ActiveX control.	Authenticated IIS user.

SQL Server CE Security Overview

SQL Server CE supports file-level passwords on the local database and 128-bit encryption. SQL Server CE is does not support multiple concurrent users on a single database, and therefore has no concept of logins. SQL Server CE supports a password to secure the database alone. A database can be secured by setting the SSCE:Database Password property in the SubscriberConnectionString property of the SQL Server CE Replication object, or in the LocalConnectionString property of the SQL Server CE RDA object.

The following methods exist in the SQL Server CE Replication object and the SQL Server CE RDA object to authenticate IIS server.

InternetURL: Specifies the URL used to connect to the SQL Server CE Server Agent ISAPI DLL.
InternetLogin: Specifies the login name authenticated by IIS when connecting to the SQL Server CE Server Agent ISAPI DLL. This is the login used for Windows Authentication on the SQL Server.
InternetPassword: Specifies the password authenticated by IIS when connecting to the SQL Server CE Server Agent ISAPI DLL. This is the password used for Windows Authentication on the SQL Server.
InternetProxyServer: Specifies the proxy server or firewall used. It can also specify the computer running Microsoft ActiveSync® and SQL Server CE Relay port by passing ppp_peer:<clientport number>.
InternetProxyLogin: Specifies the login name used when connecting to a proxy server, defined in the InternetProxyServer property, that requires authentication.
InternetProxyPassword: Specifies the password used when connecting to a proxy server, defined in the InternetProxyServer property, that requires authentication.

Security Properties of SQL Server CE Replication ActiveX Control

SQL Server CE replication uses a SubscriberConnectionString property to specify the connection to the database on the device. This string supports many optional flags including: SSCE:Database Password and SSCE:Encrypt Password.

SubscriberConnectionString: Specifies the OLE DB connection string for the SQL Server CE database on the Windows CE device in the following format:
"Data Source=\mydatabase.sdf; SSCE:Database Password=mypassword;"
Note The SubscriberConnectionString does not require a provider value be passed. It is implied by the SQL Server CE Client Agent.

For connectivity to SQL Server, SQL Server CE replication passes security information (logins and passwords) through the following SQL Server CE Replication ActiveX control properties:

DistributorSecurityMode

Specifies the security mode used when connecting to the SQL Server Distributor. DistributorSecurityMode takes the following enumerator values:

DB_Authentication ( 0 ): Corresponds to SQL Server Authentication.

NT_Authentication ( 1 ): Corresponds to Windows Authentication on the SQL Server Distributor.

DistributorLogin

Specifies the login name used when connecting to the Distributor.

DistributorPassword

Specifies the login password used when connecting to the Distributor.

PublisherSecurityMode

Like the DistributorSecurityMode, this property specifies the security mode used when connecting to the Publisher. This property takes the same values as DistributorSecurityMode.

DB_Authentication ( 0 ): Corresponds to SQL Server Authentication.

NT_Authentication ( 1 ): Corresponds to Windows Authentication on the SQL Server Publisher.

PublisherLogin

Specifies the login name used when connecting to the Publisher.

PublisherPassword

Specifies the login password used when connecting to the Publisher.

Note SQL Server ignores DistributorLogin, PublisherLogin, DistributorPassword, and PublisherPassword when Windows Authentication is specified. In this case, the SQL Server CE ISAPI DLL runs under the IIS processes login and password, passed in the InternetLogin and InternetPassword properties.

SubcriberConnectionString

Specifies the OLE DB connection string for the SQL Server CE database on the Microsoft Windows CE-based device.

Security Properties of SQL Server CE Remote Data Access (RDA)

SQL Server CE RDA uses a LocalConnectionString property to specify the connection to the database on the device. This string supports the optional flag: SSCE:Database Password.

LocalConnectionString: Specifies the OLE DB connection string for the SQL Server CE database on the Windows CE device in the following format:
"Data Source=\mydatabase.sdf; SSCE:Database Password=mypassword;"
Note The LocalConnectionString does not require a provider value be passed. It is implied by the SQL Server CE Client Agent.

The RDA methods Push(), Pull(), and SubmitSQL() require an OLEDBConnectionString to be passed when the methods are called. For SQL Server Authentication, the OLEDBConnectionString should contain user id and password properties.

Example

provider=SQLOLEDB; data source=ServerA; Initial Catalog=Northwind; user id=SA; password= "MyPassword#1";

When using Windows Authentication on SQL Server, no login or password is included in the ConnectionString, because the IIS user will be used. The property, INTEGRATED SECURITY = SSPI should be passed instead.

Example

provider=SQLOLEDB; data source=ServerA; Initial Catalog=Northwind; INTEGRATED SECURITY = SSPI;

Windows Security Overview

When using the NTFS file system, you must grant specific privileges to the physical folder (content folder) that contains the SQL Server CE Server Agent (Sscesa20.dll) as well as to the server agent file itself. When using replication, be sure to grant privileges to the snapshot folder as well.

NTFS Security Overview

NTFS provides security at the file, directory, and drive levels. While you are probably already familiar with NTFS security, there are some specific requirements for NTFS security with SQL Server CE:

When using Anonymous access on the IIS Server, the Internet guest account (IUSR_Machinename by default) must have Read & Execute permission on the SQL Server CE Server Agent DLL (Sscesa20.dll).
When using Basic or Windows Authentication on the IIS server, the client's Windows user account must have Read & Execute permission on the SQL Server CE Server Agent DLL (Sscesa20.dll).
The IIS user account (whether it be Anonymous or the client user's account from Basic or Windows Authentication) must have Read and Write permission on the folder (content folder). This allows the Sscesa20.dll permission to create a log file as well as create and read the message files.
For replication, the IIS user must have Read permissions to the NTFS file system directory that is specified as the Snapshot folder.
Note Because the Snapshot folder will include individual folders for each snapshot that is created when the snapshot agent is run, you will need to select the Allow inheritable permissions from parent to propagate to this object check box.

Single Server Scenarios

The following scenarios demonstrate how to set up IIS and SQL Server security on a single server (called Server A for purposes of example). After showing the configuration of this single server, this paper discusses multiserver scenarios in the next section. Figure 1 shows the single server configuration.

Figure 1. The single server configuration

Setting up a SQL Server Replication Snapshot Folder

SQL Server merge replication requires a share to store the snapshot files used to initialize subscriber schema. This share requires Read permission by the Windows account the SQL Server CE Server Agent is running under, and Read and Write permissions on the Windows account that the SQL Server Snapshot Agent is running under. Complete these steps before publishing a database or running the Connectivity Management utility.

Using Windows Explorer, create a folder called snapshot.
Right-click on snapshot and choose properties to view the folder properties.
Choose the sharing tab.
Select the Share this folder check box to make snapshot shared.
Share c:\snapshot as \\ServerA\snapshot.
Note Because the Snapshot folder will include individual folders for each snapshot that is created when the snapshot agent is run, you must select the Allow inheritable permissions from parent to propagate to this object check box from the Security tab.

Setting up SQL Server CE Server Agent Directory

This section describes how to set up a folder for the SQL Server CE Server Agent (Sscesa20.dll). You will need to complete this section in order to make use of the server scenario examples later in the paper. Use Server A for this directory setup; Server B will not be used as an IIS Server, and will not need the SQL Server CE Server Agent installed.

Manually Create a Windows Directory for SQL Server CE Server Agent (optional)

Use Windows Explorer to create an alternate folder on your IIS system to contain the SQL Server CE Server Agent ISAPI DLL, as well as the input and output SQL Server CE replication or RDA message files.
Use Windows Explorer to copy the SQL Server CE Server Agent ISAPI DLL (Sscesa20.dll) to the folder you created in the previous step. Sscesa20.dll is installed in the \Program Files\Microsoft SQL Server CE 2.0\Server directory by default.
Note If you wish to use another folder, you must register the DLL by typing Regsvr32 Sscesa20.dll at the command prompt. For example:

regsvr32 C:\SQLCE\sscesa20.dll

The default server folder ("c:\program files\Microsoft SQL Server CE 2.0\Server") will be referenced throughout the remainder of the paper as the folder for the virtual directories. The steps to create the virtual directories are described later. After you have the directory created, you will need to follow these steps to grant your default Anonymous Internet Guest account Read and Write permissions on the system folder.

To secure the file system directory manually on Windows 2000

Start Windows Explorer.
Right-click the C:\program files\Microsoft SQL Server CE 2.0\Server directory. Click Properties, and then click the Security tab. If you do not see the Security tab, your server has a FAT file system and you will need to convert it to NTFS. SQL Server CE replication or RDA works with FAT file systems, but it is recommended that you use NTFS for greater security. For information about converting the file system to NTFS, see the Windows documentation.

Click Add to set the file system directory permissions as follows:

User	Required permissions
For Administrators, always grant full control.	Full Control
For System, always grant full control.	Full Control
For Anonymous access, grant ServerA/IUSR_ServerA or the configured IIS Internet Guest account Read and Write permission.	Read and Write
For Basic Authentication or Windows Authentication, grant the client's Windows user account Read and Write permission.	Read and Write

Note You should not allow Read & Execute or Modify permissions to the folder to any user connecting through the IIS server. This may allow a user to insert a potentially harmful executable and execute the file.

If the Everyone group has been granted access to the SQL Server CE Server Agent DLL, use the Remove button to eliminate access by the Everyone group.

Note You will need to repeat this for any Windows users to whom you give permission when using Basic Authentication or Windows Authentication.

Note The SQL Server CE Server Agent ISAPI DLL should not allow Write or Modify permissions to any user connecting through the IIS server. This may allow a user to insert a potentially harmful executable in place of Sscesa20.dll.

Anonymous Access (IIS) and SQL Server Authentication

This scenario should not be considered secure, because it relies on SQL Server Authentication only. Do not choose these authentication methods for an actual production deployment; this scenario is only useful for debugging and testing applications. The first step is to create a virtual directory in IIS. In this scenario, SQL Server will rely on the login and password passed from the SQL Server CE ActiveX controls.

Manually Configure a Virtual Directory for Anonymous Access on Windows 2000

Start the IIS snap-in by clicking the Start button, pointing to Settings, and then clicking Control Panel. In Control Panel, double-click Administrative Tools. In Administrative Tools, double-click Internet Services Manager. If the Internet Services Manager option is not present in Administrative Tools, you must install IIS. For more information, see the Windows 2000 documentation.
1. In the left pane, expand the computer containing your site. Right-click the Web site where you want to install SQL Server CE replication or RDA, point to New, and then click Virtual Directory to start the Virtual Directory Creation Wizard. For example, right-click Default Web Site.
2. On the Welcome page of the Virtual Directory Creation Wizard, click Next.
3. On the Virtual Directory Alias page, enter the name you want to assign to your virtual directory. For this example, use Anon, to signify the anonymous authentication you are using. Click Next.
4. On the Web Site Content Directory page, enter the full path for the default server directory. Click Next.
5. On the Access Permissions page, enter the access permissions for the virtual directory. Be as restrictive as possible because doing so makes your Web site more secure. You must select Execute permissions. This permits Sscesa20.dll to run. You can disable the Read, Run scripts, Write, and Browse permissions. Click Next.
6. Click Finish to complete the Virtual Directory Creation Wizard.
In the left pane, expand your Web site. From the list of Virtual Directories, right-click your virtual directory and then click Properties.
Click the Virtual Directory tab. In the Execute Permissions list, ensure that the Scripts and Executables check box is selected. In the Application Protection list, select the form of protection you want: High (Isolated), Medium (Pooled), or Low (IIS Process). SQL Server CE replication or RDA will work in any of the application protection modes. The default application protection mode is Medium (Pooled). Refer to the IIS documentation for more information.
Important Never grant scripts and executables permission to a virtual directory that also has write permissions. This will allow potentially harmful executables to be uploaded to your IIS server and executed remotely.

Create a Virtual Directory for Anonymous Access using the Connectivity Management utility:

On the Start menu, point to Programs, Microsoft SQL Server CE, and then click Configure Connectivity Support in IIS.
In the right pane of the utility, double-click Create a Virtual Directory to launch the Virtual Directory Creation Wizard.
Click Next.
Enter the alias Anon in the text box (see figure 2).

Figure 2. SQL Server CE Virtual Directory Creation Wizard
Click Next.
Choose the Anonymous access check box.
If you wish to change your Anonymous user, click Edit, enter a Windows login, and then click Next.

Figure 3. SQL Server CE Administration window
Choose one or more applications requiring SQL Server merge replication.
Click Next.
Enter the UNC path of the Snapshot Folder (\\ServerA\Snapshot) created above. Click Next.
Figure 4. NTFS Permissions: Snapshot folder
The final page of the wizard details all of the settings. Click Finish to complete the wizard.

Figure 5. The Completion window

After you have completed these steps, you should have a working virtual directory, and you should be able to perform any of the connectivity methods of RDA Push(), Pull(), or SubmitSQL(), provided you have configured SQL Server security properly. You can also verify the connection from your device to the Internet server by entering the URL (http://ServerA/Anon/Sscesa20.dll) in Pocket Internet Explorer. You should see the phrase "SQL Server CE Server Agent" in the Pocket Internet Explorer client window.

Note If you are using SQL Server CE Relay, you will not be able to confirm your connection with Pocket Internet Explorer.

If you have published a database on Server A, you will be able to perform replication as well. For more information, see "Creating the Publication" in SQL Server CE Books Online. You can run the setup for the Northwind replication sample application and use the "sa" SQL Server Login to set up replication. For more information, see "SQL Server Requirements" and "Sample Applications" in SQL Server CE Books Online.

Because you are using SQL Server Authentication on the SQL Server, you do not have to change the IIS Anonymous user, or give the Anonymous user (should be IUSR_Machinename) permission on SQL Server. The next section will describe the relationship between the Anonymous User and Windows Authentication on the SQL Server.

Anonymous Access (IIS) and Windows Authentication (SQL Server)

Windows Authentication offers optimal security. However, this paper is based on the use of Mixed Mode Authentication, which allows you to use either Windows Authentication or SQL Server Authentication. Because you are going to connect to SQL Server through Windows Authentication, you will need to add permissions for your Anonymous IIS User (typically IUSR_Machinename) to your SQL Server and any databases you wish to connect to. For more information, including specific permissions required, see SQL SQL Server Books Online.

Basic Authentication (IIS) with SSL and SQL Server Authentication

The next level of security is Basic Authentication. Because Basic Authentication transmits logins and passwords in easily read way, always use SSL with Basic Authentication for increased security. This paper begins without SSL and adds it only after Basic Authentication is configured.

To manually create a virtual directory for Basic Authentication on Windows 2000

Follow these steps to create a new virtual directory called "Basic." Use the same folder as you used for "Anon." (You can create a new directory if you wish.)

Start the IIS snap-in by clicking the Start button, pointing to Settings, and then clicking Control Panel. In Control Panel, double-click Administrative Tools. In Administrative Tools, double-click Internet Services Manager. If the Internet Services Manager option is not present in Administrative Tools, you must install IIS. For more information, see the Windows 2000 documentation.
In the left pane, expand the computer containing your site. Right-click the Web site where you want to install SQL Server CE replication or RDA, point to New, and then click Virtual Directory to start the Virtual Directory Creation Wizard. For example, right-click Default Web Site.
On the Welcome page of the Virtual Directory Creation Wizard, click Next.
On the Virtual Directory Alias page, enter the name you want to assign to your virtual directory. This example uses Basic, to signify the basic authentication used. Click Next.
On the Web Site Content Directory page, enter the full path for the default directory. Click Next.
On the Access Permissions page, enter the access permissions for the virtual directory. Be as restrictive as possible because doing so makes your Web site more secure. You must select Execute permissions. This permits Sscesa20.dll to run. You can disable the Read, Run scripts, Write, and Browse permissions. Click Next.
Click Finish to complete the Virtual Directory Creation Wizard.

Now you can change the authentication method on the virtual directory to Basic Authentication by following these steps.

To configure Basic Authentication

Start the IIS snap-in by clicking the Start button, pointing to Settings, and then clicking Control Panel. In Control Panel, double-click Administrative Tools. In Administrative Tools, double-click Internet Services Manager.
In the left pane, expand the computer containing your site and then expand your Web site.
From the list of virtual directories, right-click the Basic virtual directory, click Properties, and then click the Directory Security tab.
In Anonymous access and authentication control, click Edit to display the Authentication Methods sheet.
Select the Basic authentication check box. Clear all others.
Clients who log in using Basic Authentication must belong to a Windows domain. Clients who log in without specifying a domain are assumed to be members of the local domain of your Web server by default. You can configure a different default domain name by clicking Edit and entering a domain name in the Domain Name box. If your Web server does not belong to a network, the Web server's default local domain is the name of your computer.
Click OK to close the Authentication Methods page.
Click OK to close the virtual directory pages.

At this point you should be able to perform RDA or replication with the server using the virtual directory Basic in your InternetURL property. You will need to pass a valid Windows login and password to the InternetLogin and InternetPassword properties of the SQL Server CE ActiveX control. Try this with both valid and invalid values for the InternetLogin and InternetPassword. Invalid values for InternetLogin or InternetPassword will return this error message: "28011 – Authentication failed on server." If you see other failures, try connecting through the Anonymous virtual directory. If that works, verify that you have Scripts and Executables permission on the Basic virtual directory and try again. If you are unsure of your connection, try using Pocket Internet Explorer. For more information, see "Using Internet Explorer to Check the IIS Configuration" in SQL Server CE Books Online. Pocket Internet Explorer should request a login and password when you attempt to connect to a Web site that uses Basic Authentication.

Note If you are using SQL Server CE Relay, you will not be able to confirm your connection through Pocket Internet Explorer.

To create a virtual directory for Basic Authentication using the Connectivity Management utility:

Using the Connectivity Management utility, it is easy to configure the IIS Server and the SQL Server CE Server Agent for RDA and merge replication.

On the Start menu, point to Programs, Microsoft SQL Server CE 2.0, and then click Configure Connectivity Support in IIS.
In the right pane of the utility, double-click Create a Virtual Directory. Click Next
Enter the name Basic. Click Next.
Choose the Basic Authentication check box. This will open a warning dialog box.
Click Yes.

Figure 6. SQL Server CE Administration warning dialog box
Should you wish to choose a Domain other than the default domain, choose Edit and enter the domain name. Click Next.
Enter the name of a Domain account or Group that you want to have permissions to perform SQL Server CE replication and Remote Data Access (see figure 7). Click Next.

Figure 7. NTFS Permissions: User window
Choose one or more applications requiring SQL Server merge replication. Click Next.
Enter the UNC path of the Snapshot Folder (\\ServerA\Snapshot) created above. Click Next.
The final page of the wizard details all of the settings for the User you configured for Basic Authentication. Click Finish to complete the wizard.

Figure 8. The completion window

Now that you have Basic Authentication configured for one user or group, you will need to add all of the users/groups you wish to use SQL Server CE Connectivity.

To Configure Additional Windows Logins for Basic Authentication Using the SQL Sever CE Connectivity Management Utility

The SQL Server CE Connectivity Management utility allows you to change existing virtual directory settings and add additional NTFS permissions.

On the Start menu, point to Programs, Microsoft SQL Server CE 2.0, and then click Configure Connectivity Support in IIS.
On the left pane, choose the "Basic" Virtual Directory to open the properties pane (see the following screenshot).
Figure 9. The properties pane
Choose the NTFS Permissions tab (see the following screenshot).
Figure 10. The NTFS Permissions tab
Click Add/Modify NTFS Permission for a User…
Enter the name of a user or group you wish to have permission to the IIS Server. Click Next.
Note You can create a new group consisting of the users you wish to give permission to the IIS server. Once this group is created, you would only need to run the NTFS Permission wizard once to grant permission to the entire group.
The wizard will display the default permissions the user will require for the virtual directory and SQL Server CE Server Agent (see the following screenshot). Additional permissions can be set, but are not recommended. Click Next.
Figure 11. NTFS Permission wizard
Choose one or more applications requiring SQL Server merge replication. Click Next.
Enter the UNC path of the Snapshot Folder. This is the same folder as in the Anonymous setup. Click Next.
The wizard now displays the Read permissions on the Snapshot Folder for the selected user/group (see the following screenshot). Additional permissions can be set, but are not recommended. Click Next.
Figure 12. NTFS Permission wizard Snapshot folder
The final page of the wizard details all of the settings for the User/group you configured. Click Finish to complete the wizard (see the following screenshot).
Figure 13. Completion of NTFS Permission wizard

After you have completed these steps, you should have a working virtual directory using Basic Authentication, and you should be able to perform any of the connectivity methods of RDA Push(), Pull(), or SubmitSQL(), provided you have configured SQL Server security properly. You can verify the connection from your device to the Internet server by entering the URL (http://ServerA/Basic/Sscesa20.dll) in Pocket Internet Explorer. Pocket Internet Explorer will request a user name and password. Once you enter these correctly, you should see the phrase "SQL Server CE Server Agent" in the Pocket Internet Explorer client window.

Note If you are using SQL Server CE Relay, you will not be able to confirm your connection with Pocket Internet Explorer.

Configuring SSL on Windows 2000

Because the Base64 encoding that is performed automatically by the Basic Authentication method is not highly secure, you should use SSL encryption to further secure your virtual directory. You will either need an agreement with an online certificate authority, or you will need to install and run Certificate Services on another computer. You can set up Server B as a certificate authority as follows.

To install and configure Certificate Services on Windows 2000

Click the Start button, point to Settings, and then click Control Panel.
Double-click Add/Remove Programs, and then click Add/Remove Windows Components.
In the Windows Components Wizard, select the Certificate Services check box. A dialog box will appear to inform you that the computer cannot be renamed, and the computer cannot be joined to or removed from a domain after Certificate Services is installed. Click Yes, and then click Next.
Click Stand-alone root CA.
(Optional) Select the Advanced options check box to specify the hash algorithm.
Click Next.
Type the name of the certification authority and other necessary information. None of this information can be changed after the CA setup is complete.
Specify the storage locations of the certificate database, the certificate database log, and the shared folder. Click Next.
If the World Wide Web Publishing Service is running, you will receive a request to stop the service before proceeding with the installation. Click OK.
If prompted, type the path to the Certificate Services installation files.

Now you will have to request a new server certificate for Server A, and install the Root Certificate on Server A as well as on any devices you will be using in the following steps.

Requesting a Server Certificate on Windows 2000

The following several procedures will establish your server certification on Windows 2000. Perform the following steps on Server A:

To request a server certificate for IIS 5.0 on Windows 2000 from a stand-alone certification authority

Start the IIS snap-in by clicking the Start button, pointing to Settings, and then clicking Control Panel. In Control Panel, double-click Administrative Tools. In Administrative Tools, double-click Internet Services Manager.
In the left pane, expand the computer containing your Web site. Right-click the Web site containing your virtual directory. Click Properties, and then click the Directory Security tab.
Under Secure Communications, click Server Certificate to start the Web Server Certificate Wizard. If this button is not available, you have right-clicked a virtual directory, directory, or file rather than the Web server. In this case, close the dialog box and right-click the Web site.
Read the opening page of the IIS Certificate Wizard. It introduces you to the wizard and informs you of the status of any enrollments you performed in the past. Because the wizard knows the current state of your certificate requests, it displays only the appropriate options and warns you if you try to try to do something that might invalidate a pending request. Click Next.
On the Server Certificate page, click Create a new certificate and click Next.
On the Delayed or Immediate Request page, click Prepare a request now but send it later and click Next.
On the Name and Security Settings page, enter the name of your Web site in the Name box. The wizard automatically extracts the friendly name of the Web site defined when you initially configured the site. This property is not used in the certificate; it is just a friendly name to help the administrator.
Enter the public key length of the certificate. Server Gated Cryptography (SGC) is an extension of SSL/TLS that allows financial institutions with exported versions of Windows 2000 Server to use 128-bit encryption. However, with new cryptographic export laws that allow much stronger encryption than previously available outside North America, this capability is no longer required. You can leave the check box cleared. Click Next.
Enter information for Organization and Organization Unit. This information will go into the certificate, so make sure it is accurate. The names must contain only the uppercase or lowercase ASCII letters A through Z, the digits 0 through 9, the space character, and the following special characters: ' ( ) + - ., / : = and ?. Names cannot contain underscores or other special characters. This limitation exists because Windows CE 3.0 and earlier cannot accept server certificates containing Unicode character strings. For more information, see Microsoft Knowledge Base article Q216947 (available on the Microsoft Web site). Click Next.
In Common Name, type Server A. This information will go into the certificate, and is the most important information in the certificate, so make sure it is correct. When the Windows CE device attempts to connect to your Web site, it will compare the name contained in the server certificate with the name of the system where your Web server is actually running. If these names are not identical, SQL Server CE replication or RDA will not be able to communicate using SSL. By default, the wizard will select either the NetBIOS or DNS name of the server. If the computer is to be used as an intranet server, you can use either name. If the server is to be used on the Internet, the name must be the name of the Web server as it appears on the Internet. For example, the computer might have a NetBIOS name of WebServer and an internal DNS name of webserver.mycompany.com. However, because it will be used as a Web server on the Internet, its Internet DNS name might be www.mycompany.com. This is the name you should enter into the Common Name box. Use the naming limitations described earlier in step 9. Click Next.
Enter information for Country/Region, State/Province, and City/Locality. This information will be included in the certificate. When entering the state/province, enter the entire name of the state or province, not its abbreviation. Names can only contain the characters described in step 9. Click Next.
On the Certificate Request File Name page, enter the fully qualified name of the certificate request file. By default, the file name is C:\Certreq.txt. The certificate request will be stored as a PKCS#10 certificate request file. Click Next.
On the Request File Summary page, review the certificate request information you have entered and click Next.
On the Completing the Web Server Certificate Wizard page, click Finish to generate the certificate request.
Copy the Certreq.txt file created to Server B.

To obtain a server certificate from a Windows 2000 stand-alone certification authority

Submit the certificate request file to your stand-alone certification authority by running the CertReq command prompt utility. At the command prompt, run CertReq.exe and specify the name of the certificate request file. For example:
```
CertReq ServerACertReqFile.txt
```
CertReq displays the Select Certification Authority dialog box containing a list of certification authorities. Select your stand-alone certification authority from the list and click OK.
CertReq informs you that your certificate request is pending, and it displays the numeric RequestId for your request.
Start the Certification Authority snap-in. In Windows 2000, do this by clicking the Start button, pointing to Programs and Administrative Tools, and then clicking Certification Authority.
In the left pane, expand the list of items under the computer containing your certification authority. Right-click Pending Requests to display the list of pending certificate requests.
In the right pane, right-click the RequestId for your certificate request; click All Tasks and Issue.
Close the Certification Authority snap-in.
Retrieve the newly issued server certificate by running the CertReq command prompt utility. At the command prompt, run CertReq.exe specifying the retrieve option and the RequestId for your certificate request. For example:
```
      CertReq –retrieve 3
```
CertReq displays the Select Certification Authority dialog box containing a list of certification authorities. Select your stand-alone certification authority from the list and click OK.
CertReq displays the Save As Outfile Name dialog box. Enter the name you want to give your certificate file and click OK. Specify .cer as the file extension. The Certification Authority will package your certificate in a PKCS#7 certificate file.
Copy this certificate file to Server A.

To install the server certificate in IIS 5.0 on Windows 2000

On Windows 2000 Server, start the IIS snap-in by clicking the Start button, pointing to Programs and Administrative Tools, and then clicking Internet Services Manager.
In the left pane, expand the computer containing your Web site. Right-click the Web site containing your replication or RDA virtual directory. Click Properties, and then click the Directory Security tab.
Within Secure Communications, click Server Certificate to start the Web Server Certificate Wizard. If this button is not available, you have right-clicked a virtual directory, directory, or file rather than the Web server. In this case, close the dialog box and right-click the Web site.
Read the Welcome to the Web Server Certificate Wizard page. It should indicate that you have a pending certificate request. Click Next.
On the Pending Certificate Request page, click Process the pending request and install the certificate. Click Next.
On the Process a Pending Request page, enter the full name of the file containing the server certificate in the Path and file name box. Typically, the file extension for the certificate file is .cer. Click Next.
On the Certificate Summary page, verify the information is correct. Click Next.
On the Completing the Web Server Certificate page, click Finish to return to the IIS properties page.
On the Directory Security tab, under Secure Communications, click View Certificate.
Click the Certification Path tab.
The properties of the server certificate are displayed on the Certificate Summary page.
Click on the highest certificate name in the path (the root certificate).
Note If Certificate Status reads, "This CA Root certificate is not trusted because it is not in the Trusted Root Certification Authorities store," you will need to update the Database Of Trusted Certificate Authorities.
Click View Certificate.
On the General tab of the Certificate Properties dialog box, click Install Certificate to start the Certificate Import Wizard. Click Next.
In the Certificate Store dialog box, automatically select the certificate store based on the type of certificate should be the default. If it is not, select it. Then click Next.
Click Finish to complete the Certificate Import Wizard.
You will be asked if you want to add the certificate to the Root Store. Click Yes.
To acknowledge the successful update of the Root Store, click OK.
Note You will not see the error removed from the certificate until you close the Web site properties and open it again.

At this point you have the server certificate installed. The next step is to enable encryption on the Basic virtual directory to request encryption for all connections. Follow these steps to set up SSL encryption on the Basic virtual directory.

To enable SSL encryption for your IIS virtual directory on Windows 2000 with IIS 5.0

Start the IIS snap-in by clicking the Start button, pointing to Settings, and then clicking Control Panel. In Control Panel, double-click Administrative Tools. In Administrative Tools, double-click Internet Services Manager.
In the left pane, expand the computer containing your Web site, and then expand the list of virtual directories for your Web site.
From the list of virtual directories, right-click the IIS virtual directory you configured for SQL Server CE replication or RDA. Click Properties and click the Directory Security tab.
Under Secure Communications, click Edit to display the Secure Communications page.
Select the Require secure channel (SSL) check box to enable SSL encryption. You can select Require 128-bit encryption if this option is provided. Export restrictions and national laws might limit the availability of this option.
Note If you choose to require 128-bit encryption with Pocket PC, you will need to install the 128-bit Encryption Pack. The 128-bit Encryption Packs for all Windows CE devices is available at the Pocket PC home page.
Click OK to close the Secure Communications dialog box.
Click OK to close the Directory Security dialog box.
Close Internet Services Manager.

If you have followed these steps to use a Stand-Alone Certificate Authority, you will need to update the Database of Trusted Certificate Authorities on your devices. If you have used a trusted Stand-Alone Authority, you can skip the next procedure. In order to update the database on your devices, you will first need to export the root certificate from your IIS Server (Server A).

To export the Root Certificate:

On your Stand-Alone Certificate Authority (Server B), start the Certificate Services snap-in by clicking the Start button, and then pointing to Programs, Administrative Tools, and Certificate Authority.
In the left pane, right-click on the computer containing your certificate authority and then click Properties.
In the Properties dialog box, click View Certificate.
Click the Details tab.
On the Details tab, click Copy to File to start the Certificate Export Wizard. Click Next.
On the Export File Format page, click DER encoded binary X.509 (.CER). Click Next.
On the File to Export page, enter Rootcert.cer. Click Next.
Note The Rootcert.exe uses the default name Rootcert.cer for the certificate if it is run with no certificate passed at the command line. Exporting your root certificate as Rootcert.cer will make this process easier. Especially on Pocket PCs, because you may not have a command line, you will only need to tap the rootcert.exe in the Pocket Internet Explorer.
On the Completing the Certificate Export Wizard page, click Finish to complete the wizard.
The Certificate Export Wizard displays a message stating, The export was successful." Click OK to close the message box.
Click OK to close the root Certificate pages.
Click OK to close the server Certificate pages.
Important The root certificate is your trust mechanism between your server and clients. Do not share this file with others. If your root certificate is compromised, anyone with it can impersonate your users or decode your encrypted IIS user logins and passwords.

Now that you have your root certificate saved as a file, you can update the Database of Trusted Root Authorities on your IIS servers and your devices. You will need the rootcert.exe that ships with the SQL Server CE Development Tools.

To install the root certificate on the Windows CE device

Copy the Rootcert.exe that matches the processor type of your device from the SQL Server CE installation directory (by default, Program Files\Microsoft SQL Server CE\Device\processor family\processor type) to the \Windows directory of the Windows CE device.
Copy the Rootcert.cer file that you created in the previous step to the root directory of the Windows CE device.
Run Rootcert.exe from File Explorer.
The root certificate utility program (Rootcert.exe) does the following:
- It opens and reads the Rootcert.cer file contained in the root directory of the Windows CE device.
- It creates the registry key HKLM\Comm\SecurityProviders\SCHANNEL\CAs if this key does not already exist in the registry on the Windows CE device.
- It creates the registry key HKLM\Comm\SecurityProviders\SCHANNEL\CAs\filename, where filename is the name of the input Rootcert.cer file.
- It creates the following registry values under the HKLM\Comm\SecurityProviders\SCHANNEL\CAs\filename key:
  DWORD:Enabled = 1
  DWORD:Type = 1
  BINARY:CACert = X509 certificate bytes obtained from the .CER file
- It displays the message "Root certificate installed successfully."

You should now be able to connect from your device to your IIS server setting the InternetURL property of the SQL Server CE ActiveX control to HTTPS://ServerA/Basic/sscesa20.dll. You can verify that your IIS server is requesting a secure connection by attempting the connection with HTTP instead of HTTPS in the InternetURL property.

Basic Authentication with SSL (IIS) and Windows Authentication (SQL Server)

After you have completed the previous steps to set up IIS with Basic Authentication, you can try using Basic Authentication with Windows Authentication on your SQL Server.

Note When using Basic Authentication (with or without SSL), Sscesa20.dll will run under the client's login. Therefore, when using Windows Authentication on SQL Server, the permitted IIS user (the username and password passed to the InternetLogin and InternetPassword ActiveX control properties, respectively) must also have the proper permissions at SQL Server.

Windows Authentication (IIS) and SQL Server Authentication

Windows Authentication allows you to use Windows Domain Security to secure access to sscesa20.dll. While this is not the most secure solution, it can be useful. For example, an administrator may want to administer only one IIS server, but may have several SQL Server databases or publications. Unlike the following scenario, using SQL Server Authentication with Windows Authentication on the IIS server allows the administrator to secure the SQL Server databases separately from the IIS server. Again, make another virtual directory, like "Anon" and "Basic"; call this one "NTLM" and point it to the same folder (c:\Program Files\Microsoft SQL Server CE 2.0\Server). After the directory is created, change the authentication to Windows Authentication.

To manually create a virtual directory for Windows Authentication (formerly called NTLM or Windows NT Challenge/Response Authentication)

On Windows 2000 Server, start the IIS snap-in by clicking the Start button, pointing to Programs and Administrative Tools, and then clicking Internet Services Manager.
In the left pane, expand the computer containing your site and then expand your Web site.
From the list of virtual directories, right-click your virtual directory, NTLM, click Properties, and then click the Directory Security tab.
In Anonymous access and authentication control, click Edit to display the Authentication Methods page.
Select the Integrated Windows authentication check box, and clear all other check boxes.
Important Integrated Windows authentication cannot operate over a proxy server or firewall. As a result, it can be used for intranet applications but is seldom, if ever, used for Internet applications.

Note Integrated Windows authentication is supported on Windows CE 3.0 or later. It is not supported on earlier versions of Windows CE.
Click OK to close the Authentication Methods page.
Click OK to close the virtual directory pages.

To create a virtual directory for Windows Authentication (formerly called NTLM or Windows NT Challenge/Response Authentication) using the SQL Server CE Virtual Directory Creation Wizard

On the Start menu, point to Programs, Microsoft SQL Server CE 2.0, and then click Configure Connectivity Support in IIS.
In the right pane of the utility, double-click Create a Virtual Directory to launch the Virtual Directory Creation Wizard. Click Next.
Enter NTLM for the Virtual Directory name. Click Next.
Choose Integrated Windows authentication (see the following screenshot). Click Next.
Figure 14. Virtual directory authentication
Enter the name of a domain account or group that you want to have permissions to perform SQL Server CE replication and Remote Data Access. Click Next.
Choose one or more applications requiring SQL Server merge replication, and then click Next.
Enter the UNC path of the Snapshot Folder (\\ServerA\Snapshot) you created above, and then click Next.
The final page of the wizard details all of the settings for the user you configured for Basic Authentication. Click Finish to complete the wizard.

Your NTFS virtual directory will require that the InternetLogin and InternetPassword properties of the SQL Server CE ActiveX control are set to a valid values.

Note When Integrated Windows authentication is used, the InternetLogin and InternetPassword properties are not passed across the network.

Windows Authentication (IIS) and Windows Authentication (SQL Server)

Using Windows Authentication throughout the server path is the most secure method of connecting from the device to the SQL Server in an intranet scenario using a single server. As described earlier, the login and password passed to IIS will be the login and user that the Sscesa20.dll is run under. This login and password is also passed to SQL Server.

Therefore, in this scenario, for replication the authenticated IIS user must have the appropriate access permissions to the published database and be included in the publication access list. For more information about replication permission requirements, see SQL Server Books Online.

Multiserver Scenarios

Figure 15. Multiserver Scenarios

Consider a few important details when you are using a multiserver scenario, particularly for replication. First, the IIS server user must be a domain user in order to have permission at the SQL Server. If you are using replication, the IIS user must have Read permissions to the Snapshot folder on the SQL Server.

Note You should configure the snapshot folder on Server B before creating the publication.

If you want to use Windows Authentication on the IIS server, you will need to create the snapshot folder on the IIS server (Server A) instead. This is required to allow the SQL Server CE Server Agent ISAPI DLL access to the snapshot folder to get the schema from the SQL Server for the subscription. The schema is stored in .sch files in the snapshot folder. These files must be read to initialize the subscription with the AddSubscription() or ReInitializeSubscription() methods of the SQL Server CE ActiveX control. For more information, see Applying the Initial Snapshot in SQL Server Books Online.

Note SQL Server CE 2.0 supports alternate snapshot locations, which allows you to use a snapshot folder location other than the default placement. The folder that you configure as your snapshot location the first time you run the Create Publication Wizard will be considered the default location. This is the @working_directory property of the sp_adddistpublisher stored procedure. For more information, see SQL Server Books Online.

To set up a shared snapshot folder on Server B

Use Windows Explorer to create a shared folder on Server B for your snapshot files. Create the NTFS folder C:\snapshot.
Right-click the C:\Snapshot directory. Click Properties and click the Sharing tab. Choose Share this folder. In the Share Name box, enter snapshot.
Click Permissions to set permissions for your Windows Domain Users.

Click Add to set the shared directory permissions as follows.

User	Required permissions
For Administrators, always grant Full Control.	Full Control
For System, always grant Full Control.	Full Control
For Anonymous access, grant the configured IIS anonymous user account Read permission.	Read
For Basic Authentication or Windows Authentication, grant the client's Windows user account Read permission.	Read

Note You will need to add any Windows users you give permission to when using Basic IIS Authentication. You will not be able to use Windows Authentication in this configuration.

In the multiserver scenarios, you will need to make sure that your IIS user (whether an anonymous user or an authenticated user) has permissions. As long as the IIS Server and SQL Server are both on the same domain, using a domain user account will allow for this. If the IIS Server and SQL Server exist on separate or non-trusted domains, you will have to use SQL Server Authentication on the SQL Server.

Note You cannot use the default anonymous login for multiserver scenarios, as IUSR_Machinename is a local computer account and should not have domain access.

Anonymous Access (IIS) and SQL Server Authentication

This scenario works just like the single server case, except that SQL Server is on a separate computer, and access to SQL Server is verified by the properties passed to the SQL Server CE 2.0 ActiveX control. Because the SQL Server does not rely on the Internet Guest Account, RDA is seamless. If you are using replication, however, and the NTFS file system on Server B (recommended), you will have to grant Read permission to the snapshot folder on Server B for the Internet Guest Account of Server A. This will require the Internet Guest Account to be a domain account, so that permissions can be trusted.

The following steps describe how to change the Anonymous IIS user on Server A to a domain account:

On Server A, start the IIS snap-in by clicking the Start button, pointing to Programs, Administrative Tools, and then Internet Services Manager.
In the left pane, expand the computer containing your site and then expand your Web site.
From the list of virtual directories, right-click your "Anon" virtual directory, select Properties, and then click the Directory Security tab.
In Anonymous access and authentication control, click Edit to display the Authentication Methods page.
Click Edit and enter a Windows domain user account in the Username box.
Note You will also need to grant this user Read and Write permission on the file system directory as described earlier.

Note For replication only: This new user will need Read permission on the Snapshot folder (C:\snapshot) of Server B.

Anonymous Access (IIS) and Windows Authentication (SQL Server)

If you have followed the previous steps to configure your IIS user as a Domain User account, Server B should be prepared to use Windows Authentication. You will only need to grant Server A's Anonymous IIS user permissions on Server B's SQL Server, because SQL Server will rely on the Anonymous IIS user. After that is complete, you should be able to perform RDA and replication.

Note For replication only: Remember to add Server A's Anonymous IIS user to the Publication Access List.

Basic Authentication (IIS) with SSL and SQL Server Authentication

After you have the single server scenarios working on Server A with Basic Authentication, RDA will work in this multiserver configuration.

Note For replication only: Your Basic authenticated IIS user will need Read permission on the Snapshot folder (C:\Snapshot) of Server B.

SQL Server will rely on the login and password passed by the SQL Server CE ActiveX control properties. You will need to grant the proper permissions to the SQL Server databases on Server B for both RDA and replication.

Basic Authentication (IIS) with SSL and Windows Authentication (SQL Server)

After you have your IIS server set up with the proper login and password to work across the domain, you only need to set up the SQL Server with Windows Authentication. In this scenario, you will need to grant the authenticated IIS user permissions on the SQL Server. Your Basic Authentication user will be able to delegate as long as it has Logon locally privilege.

Note For replication only: Your Basic Authentication IIS user will need Read permission on the Snapshot folder (C:\Snapshot) of Server B as well as the Publication Access List.

Windows Authentication (IIS) and SQL Server Authentication

Using Windows Authentication on IIS (Server A) with SQL Server Authentication on Server B is seamless with RDA. However, this account cannot delegate its permissions across the domain from the IIS Server on Server A to access the snapshot folder on Server B. SQL Server CE Replication Provider runs under the identity of the Windows user account corresponding to the login and password supplied by the Subscriber for the InternetLogin and InternetPassword properties of the Replication object. A workaround is to place the snapshot folder on a share on Server A. This would require changing the snapshot folder in the publication properties for the SQL Server on Server B. For more information, see SQL Server Books Online.

Windows Authentication (IIS) and Windows Authentication (SQL Server)

Windows Integrated authentication for IIS is unsupported in many multiserver scenarios. Delegation of trusted accounts cannot occur across a Windows domain without Kerberos, which is unsupported by SQL Server CE.

Conclusion

SQL Server CE uses the security models of SQL Server, Windows, and Internet Information Services to produce a secure, end-to-end connectivity solution for the mobile enterprise. Microsoft recommends that you learn as much as possible about how each of these security models work. This paper provides an overview of these technologies, and you can find additional resources on the Microsoft Web site. In particular, see the Internet Information Services Security What If tool, which can be found at Security Tools and Checklists. While it does not have specific solutions for Windows CE browsers, it does help describe the relationship between the different IIS authentication methods, Active Directory™, and delegation.

References

Howard, Michael, Levy, Marc, and Waymire, Richard. Designing Secure, Web-Based Applications for Microsoft Windows 2000. Microsoft Press, 2000.