Deploying Data Access Pages on the Internet or Your Intranet

Mark Roberts, Office User Assistance
Microsoft Corporation

Created: January 1999
Revised: November 1999

Applies To: Microsoft® Access 2000; Microsoft SQL Server™ version 6.5 or later; Microsoft Internet Information Server version 4 or later; Microsoft Data Access Components (MDAC) version 2.1 or later; Microsoft Jet Database Engine 4.0; Microsoft Data Engine (MSDE) version 1.0 or later

Summary: This article describes how to deploy a secure data-access-page-based Web site on your intranet or the Internet by using Access and IIS to serve data from Access or SQL Server databases. (42 printed pages)

Note   The original version of this article, written by Roy Leban, included sections on working with data access pages in Microsoft FrontPage® and tips on how to use server filters. Those sections have been moved into a separate article titled "Working with Data Access Pages in FrontPage 2000."

Contents

Introduction
Client Software Requirements
Server Software Requirements
Deployment Scenarios
Security Issues Overview
Cross-Domain Data Access Security Issues
Authentication and Database Security Issues
Configuring Database Authentication and Security
Configuring Internet Information Server Authentication Methods
Remote Data Services Security Issues
Summary of Deployment Steps and Options
Next Steps

Introduction

You can use Microsoft® Access 2000 and the capabilities of data access pages to create data-driven Web sites that you can deploy securely on your organization's intranet or on the Internet. Data access pages in Access 2000 allow you to make your data available to anybody who has Microsoft Internet Explorer 5 or later and a Microsoft Office 2000 license. When you combine Access with Microsoft Internet Information Server (IIS) version 4.0 or later, you'll have all the tools you need to create a dynamic Web site that you can deploy securely.

Before you can successfully deploy data access pages, you will need to address the following software and configuration issues on both client and server computers:

This article is divided into one or more sections for each of these sets of issues. Most sections provide an introduction to the concepts and technologies behind each set of issues, followed by one or more procedures that cover the available options for that specific set of issues. To help you sort out the full set of procedures to perform for each deployment strategy, see the "Summary of Deployment Steps and Options" section at the end of this article.

When you're done reading this document, you'll know how to:

What This Paper Isn't

This paper won't make you an expert on IIS, managing a Web site, or database security. For complete information about IIS, consult the documentation that comes with it. If you're new to the Web, you'll probably want to pick up a book about Web-site creation. For details on Access and SQL Server database security, see each product's online documentation. Additional resources are listed in the "Next Steps" section at the end of this article.

Client Software Requirements

To successfully view and use the interactive features of data access pages, you must have the following software installed on client computers:

The features of data access pages depend on a set of ActiveX® controls called the Microsoft Office Web Components. Of this set of controls, the most important for data access pages is the Microsoft Office Data Source control (MSODSC), which is used to manage the page's connection to its data source. To gain full functionality when opening a data access page, a user must be running Internet Explorer 5 or later, and must also have the Office Web Components installed on his or her computer. These components are installed by default when a user installs Microsoft Office 2000. The Office Web Components are installed with Microsoft Office 2000 Standard, Professional, Premium, Developer, and Small Business editions.

When deploying data access pages on an intranet (a local area network—LAN—with Web services and protocols) or a secure extranet (such as virtual private networking [VPN] that uses a Point-to-Point Tunneling Protocol [PPTP] connection), even users who have not yet installed Office 2000 can take advantage of Office Web Components: As long as licensing requirements are met, the feature can be installed automatically when users view the data access page. Because Office Web Components are part of Office 2000, the same licensing requirements apply. Each user who installs Office Web Components must have an Office 2000 license, even if that user does not install the full Office 2000 product.

Important   To automatically install the Office Web Components when opening a data access page, the user opening the page must be able to establish secure access to the Windows® network file share (such as \\MyServer\MyShare) where the installation files reside. It is not possible to automatically install the Office Web Components across an HTTP, HTTPS, or FTP connection. For this reason, a user can't automatically install the Office Web Components from the Web server (or any other HTTP or FTP address) when viewing a data access page that is opened directly from the Internet. However, if the user has previously installed Office 2000, which includes installation of the Office Web Components, he or she will be able to open and use data access pages. Additionally, if the user has an Office 2000 license and doesn't have the Office Web Components installed, but does have a local network installation point configured in the registry, the user will be able to automatically install the Office Web Components when opening a data access page that is opened directly from the Internet.

For more information about configuring Office Web Components to install automatically, see the Microsoft Office Resource Kit Journal article "Sharing Interactive Data Across an Intranet" at www.microsoft.com/Office/ORK/2000/Journ/OWCInstall.htm.

An installation of Microsoft Office 2000 or the Office Web Components also automatically includes installation or updating of the Microsoft Data Access Components (MDAC), which contains OLE DB components that may be necessary to connect the client computer with the databases made available through data access pages.

Server Software Requirements

To successfully deploy data access pages, the following software must be installed on server computers.

Web Server Requirements:

Database Server Requirements:

Web Server Software Requirements

Microsoft Internet Information Server (IIS) 4.0 can be installed on the Microsoft Windows NT® Server 4.0 operating system by using the Windows NT Option Pack 4.0. Microsoft Internet Information Services (IIS) 5.0 is included with all Microsoft Windows 2000 Server editions. Either version of IIS is designed to deliver the highest level of security for corporate intranets and the Internet. Both versions of IIS provide a high-performance implementation of Secure Sockets Layer (SSL) 3.0 for secure communication and authentication with X.509 certificates, RSA public-key cipher, and a broad array of additional security features. IIS is a comprehensive platform for rapidly building Web sites that are secure for both Internet and intranet use.

Note   You can publish data access pages by using Microsoft Personal Web Server (PWS) 4.0 on computers running Microsoft Windows 95 or 98, or Microsoft Windows NT Workstation 4.0. Personal Web Server is designed for low-use Web sites, or for sites on an intranet. Personal Web Server is simpler to install and administer than Internet Information Server but, because it was not designed to support high-volume Web sites, it does not include support for three-tier data access. Personal Web Server also does not offer some of the security features found in IIS. However, if you are deploying data access pages on a computer running Microsoft Windows 2000 Professional, you can install Microsoft Internet Information Services 5.0, which does support three-tier data access and other security features required for the secure deployment of data access pages.

To run data access pages that use three-tier data access, you must have the Remote Data Service (RDS) version 2.1 or later installed on the computer that is running your Web server. RDS 2.1 is part of MDAC 2.1. If you have installed the Office Server Extensions, Office 2000, Access 2000, SQL Server 7.0 or later, or MSDE 1.0 or later on the machine IIS is running from, you should already have the components from MDAC 2.1 or later installed. Additionally, all editions of Windows 2000 include installation of MDAC 2.5 as built-in system components. See the next section for additional details on MDAC installation.

Database Server Software Requirements

To successfully deploy data access pages, the database that is accessed through the pages must reside on a computer that is running either:

For pages that use data stored in an Access (.mdb) back-end database and use three-tier data access, the server computer must have the Microsoft Jet Database Engine 4.0 components installed. These components are installed as part of MDAC 2.1 or later. On Windows NT 4.0, MDAC 2.1 is installed when you install Office Server Extensions, Office 2000, Access 2000, SQL Server 7.0 or later, or MSDE 1.0 or later. Additionally, you can download and install the latest release of MDAC 2.1 from the Microsoft Universal Data Access Web site at www.microsoft.com/data/. MDAC 2.5 is automatically installed on all editions of Windows 2000. If your data access pages are using only two-tier data access (such as simply copying a data access page and database to a network file share), the Microsoft Jet Database Engine 4.0 and MDAC don't have to be installed on the server.

For pages that use data stored in a SQL Server back-end database, the server computer must be running either Microsoft SQL Server 6.5 (with Service Pack 5 or later required), Microsoft SQL Server 7.0 (with Service Pack 1 or later recommended), or Microsoft Data Engine (MSDE) 1.0 (with Service Pack 1 or later recommended). Additionally, if you are using Microsoft SQL Server 6.5 on Windows NT 4.0, it is recommended that you install the latest version of MDAC. You can do this by installing any of the previously mentioned products on your server, or by downloading and installing the latest version of MDAC from the Microsoft Universal Data Access Web site at www.microsoft.com/data/. Installations of Microsoft SQL Server 7.0, MSDE 1.0, and Microsoft Windows 2000 automatically include current versions of MDAC.

Deployment Scenarios

There are a variety of ways of deploying a data access page and the database that is accessed through the page. The method you choose depends in part on whether you are deploying the data access page in a trusted environment, such as an intranet, or in a less secure environment, such as the Internet. The basic scenarios for deploying a data access page and its associated database are as follows:

There are no security or deployment issues with the first scenario, because all files are stored and accessed locally. The rest of this article describes the security issues that can arise when using the second, third, and fourth scenarios, and describes in detail how to address them.

Security Issues Overview

The security issues involved in deploying data access pages can be quite complex. In large part, this is due to the fact that database access via data access pages can pass through some or all of the following security systems or layers:

The following sections describe the data access pages issues and configuration options for each of these security systems.

Cross-Domain Data Access Security Issues

Data access pages use an ActiveX control called the Microsoft Office Data Source control (MSODSC) to connect to their data sources. By default, when a data access page is open in Internet Explorer or in an HTML-capable mail reader that uses Internet Explorer browsing components, such as Outlook 98 or Outlook 2000, the MSODSC on the page is using the identity of the current user to log on to the database. A malicious user could exploit this fact to run script against the MSODSC in an attempt to access other databases, including those on servers other than the one the page was downloaded from.

For example, a malicious user might know that a particular user has permissions to open and modify records in a database that contains sensitive information such as payroll records. The malicious user mails a data access page to that user that functions as a "Trojan horse": When the user opens the data access page, he or she sees data from a particular database, but behind the scenes script is running that employs the current user's identity and permissions to read or modify records in the payroll database.

Such attempts to use the MSODSC and the current user's security context to access databases on servers other than the one the page originated from are referred to as cross-domain data access, and any cross-domain data access is subject to the risk described above. Additionally, because of this risk, Internet Explorer 5 has security settings to either automatically disable or warn the user about cross-domain data access. For more information on these security settings, see "How Internet Explorer Handles Cross-Domain Data Access," later in this article.

Data Access Modes

The mode of data access used by the MSODSC determines whether a data access page is considered inherently safe, or if the page presents the risk of allowing malicious cross-domain data access. The MSODSC can be configured to use two modes of data access: two-tier or three-tier data access.

By default, data access pages are configured to use two-tier data access. Two-tier data access refers to using a direct connection through the file system to a data source, much like a traditional client/server application—the client computer (first tier) opens the page (either through the file system or HTTP) and the MSODSC on the page makes a direct connection to the database server (second tier). To perform two-tier data access, the MSODSC makes a direct connection to its data source through an OLE DB provider registered on the user's computer. For an Access database, two-tier data access uses the local computer's Microsoft Jet Database Engine 4.0 and the Microsoft Jet 4.0 OLE DB provider to connect through the file system to an .mdb file located on a network share. For a SQL Server database, two-tier data access uses the local computer's Microsoft OLE DB provider for SQL Server to connect to a database server available on the local network. In both cases, the current user's identity is being used to establish the security context for accessing the database, which makes the connection vulnerable to malicious scripts that use that user's identity to access other databases.

Three-tier data access refers to using an intermediate agent to handle data access between the client and database components. Three-tier data access is typically used to access data across the Internet or when you want to ensure a secure connection on an intranet. For three-tier data access, this intermediate agent is the ADO Remote Data Service (RDS) component running on an Internet Information Server (IIS). To perform three-tier data access, the MSODSC on the page (the first tier) sends a request via HTTP to IIS, and IIS passes the connection information to the RDS component (the second tier), which then uses the appropriate OLE DB provider (Jet or SQL Server) running on the server computer to connect to the database (the third tier). Once RDS is connected to the database, it uses HTTP to pass the retrieved data back through IIS to the MSODSC on the data access page. When you use three-tier data access, the RDS component running on the IIS server is establishing the security context for accessing the database, and it will not allow cross-domain data access attempts.

Figure 1. Two-tier and three-tier Web sites

Although a two-tier connection can work well for data access pages that are accessed in a trusted environment, such as from a local or network volume, or on an intranet, it's definitely not appropriate when you want to put up your site on the Internet. There are two reasons for this:

In a two-tier architecture, your Web site can be on a Web server or a standard file server; in a three-tier architecture, your Web site must be on a Web server. When you use a three-tier architecture, the only access to the database is that which you specify in your data access pages, and that which is allowed through the Web server's RDS components. Your pages will work over corporate firewalls because all of the data transfer is through standard protocols supported by proxy servers. The extra overhead of the Remote Data Services tier will slow down the data access in your page somewhat, although this effect will usually not be significant compared with Internet transfer times.

Important   If your data access page is connected to multidimensional data through the OLAP Services component of Microsoft SQL Server, you must use two-tier data access because the PivotTable® Service, which uses the OLE DB for OLAP provider to connect to multidimensional data, doesn't support three-tier data access.

To configure the data access method used for a data access page, you set the UseRemoteProvider property of the MSODSC. The default setting is False, which configures the MSODSC to use two-tier data access. Using the default two-tier data access method is appropriate while you are developing the data access page (and, in fact, is the only data access method that will allow you to view data when you open the page from the Database window, and while you are authoring the page in Access), but when you want to deploy the page by using three-tier data access, you must change the UseRemoteProvider property of the MSODSC to True. The simplest method of configuring the UseRemoteProvider property is in the data access page's Design view, as described in the following procedure.

To configure the data access method used for a data access page

  1. Start Access and open the data access page you want work with in Design view.

  2. Click the title bar of the data access page, or click Select Page on the Edit menu (to ensure that no other items are selected on the page), and then click Properties on the View menu.

  3. On the Data tab, set the UseRemoteProvider property to determine how you want to perform data access:
  4. Close the property sheet, and save your changes to the data access page.

Important   Setting the UseRemoteProvider property to True to cause the page to use three-tier data access will work properly only when the page is deployed on a properly configured IIS Web server as described later in this article. Once you set the UseRemoteProvider property to True, data can no longer be viewed if you open the page directly from the file system, from the Database window, or in Design view. You should not set the UseRemoteProvider property to True until you are ready to publish the page from a properly configured IIS Web server.

How Internet Explorer Handles Cross-Domain Data Access

Any data access page that uses two-tier data access is considered by Internet Explorer to have the potential for making a cross-domain access attempt. Depending on the Access data sources across domains security setting in Internet Explorer, when a user opens a data access page by using two-tier data access, one of three things occurs: The page is automatically disabled (the Disable setting), the user is asked whether to allow data access (the Prompt setting), or the page is automatically enabled (the Enable setting).

Internet Explorer 5 defines a different set of security settings for each of its four security zones. To view these settings, click Internet Options on the View menu, click the Security tab, click the zone you want to view settings for, and then click Customize Settings. By default, the Access data sources across domains security setting is set as follows for each security zone.

Zone Setting
Internet Disable
Local Intranet Prompt
Trusted Sites Enable
Restricted Sites Disable

As you can see, if a page is published from a server in the Trusted Sites zone, the cross-domain attempt will be enabled automatically. In a controlled environment, such as a corporate intranet, your data access pages will perform better if you use two-tier data access and make sure that they are published from a server located in the Trusted Sites security zone. This is the simplest way to address the problem of unauthorized access from malicious scripts, but this assumes that you trust all users that are allowed to publish to servers in the Trusted Sites zone, or that an administrator examines the script behind all data access pages before publishing them, to make sure that they don't contain malicious scripts.

This deployment strategy also assumes that users have Internet Explorer configured to include your Web site (the HTTP URL to open your data access page) in the Trusted Sites zone. Users can do this themselves by using the following procedure.

To assign a Web site to the Trusted Sites security zone

  1. On the Tools menu in Internet Explorer, click Internet Options.

  2. Click the Security tab.

  3. Click the Trusted Sites zone.

  4. Click Sites, and then type the Internet address for the Web site that you want to add to this zone.

Alternatively, you can populate the list of Trusted Sites and specify other security settings for all users who install and use Internet Explorer 5, by deploying custom installations of the Internet Explorer with the Internet Explorer Administration Kit (IEAK). This strategy assumes that all users of your data access pages will install Internet Explorer 5 by using the custom installation you create with the IEAK. For more information about the IEAK, see the Internet Explorer Administration Kit Web site at www.microsoft.com/windows/ieak/en/default.asp.

Another strategy for addressing cross-domain security issues is to deploy data access pages that use three-tier data access. A data access page that uses three-tier data access to connect to a database is considered by Internet Explorer to be inherently safe, regardless of what security zone it is published from, and Internet Explorer will not warn the user about cross-domain access attempts when it is opened. However, even though Internet Explorer considers pages that use three-tier to be safe, the Remote Data Services component itself presents additional security risks that need to be addressed. How to address those risks is discussed later in this article, in the "Remote Data Services Security Issues" section.

Authentication and Database Security Issues

The term authentication refers to the process of validating a user's identity. Authentication is most typically performed by prompting a user to enter a name and password, such as when logging on to a network, or opening a database. The name and password entered must be valid before the user is allowed to continue. When working with data access pages, you must work with and coordinate three different authentication systems:

For Access databases, there is the additional factor of allowing access to the database file itself (.mdb) through the file system. Because the Access database itself is a file, users of a data access page connected to an Access database must have sufficient file-system permissions (defined through the network operating system) to open and possibly write to the database file. These factors must be coordinated in order for authentication to work correctly. The following sections describe the available options for each database type.

Configuring Database Authentication and Security

The method you use to prevent unauthorized access differs depending on whether you are using an Access database or a SQL Server database as the back end for your data access page. In either case, the back-end database itself should be secured to control access both from the data access page, as well as from other means.

For the greatest flexibility when using an Access database, you should secure the database by using user-level security. User-level security allows you to define different roles (called "groups" in the product documentation) with different levels of permissions. If this level of flexibility isn't required, you can just use a single database password that is required to open the database. For more information about how to use security in an Access database, see Access Help.

For a SQL Server database, you should secure the database by using native SQL Server security accounts or by integrating Windows NT security accounts to authenticate users. When you are using native SQL security accounts, user and group accounts are defined and stored in the server itself. When using native SQL security accounts, users must log on by using the appropriate user name and password defined on the server, such as the default SA (system administrator) account. When you are using Windows integrated security, user and group accounts defined for a Windows NT network are used to authenticate users for SQL Server database security. This means that users don't have to remember an additional user name and password to log on before they can open a database; they are automatically logged on with their Windows NT user account (or the group account of which that user account is a member). The user or group account's role and permissions still must be defined in the database itself, but you are using the Windows NT user or group accounts instead of using native SQL Server accounts. For more information about how to use security in a SQL Server database, see SQL Server Books Online, which is installed with SQL Server, or can be downloaded from http://support.microsoft.com/download/support/mslfiles/sqlbol.exe (12.1 MB).

Using an Access Database Password

You can author a data access page against an Access database has been secured with a database password. However, a database password only provides control over who can open the database, and requires that all users know a single password to open the database. If you use database password security to control database access for a data access page, you have two options on how to use it. By default, a database password isn't saved with a data access page, so users are prompted to enter the database password when opening the page. (Similarly, if a database password is set for the database after you author the page, users of your page will be prompted to enter the password before they can use the page.)

If you don't want to prompt users for the database password, you can embed the password in the page; however, when you do so, the password is saved in an unencrypted format in the HTML of the page itself, which makes it fairly easy to discover.

Even though you can edit the value of the Jet OLEDB:Database Password property on the All tab of the Data Link Properties dialog box (which is displayed by right-clicking the database name in the field list and then clicking Connection) while authoring a data access page in Access, you aren't allowed to save changes because the database is already in use. To successfully embed the database password, you must edit the data access page in Notepad or some other text editor, as described in the following procedure.

To embed the database password in the page

  1. Open the HTML page in Notepad or a text editor.

  2. Search for Jet OLEDB:Database Password=

  3. Delete "" following the = sign.

  4. Type the password following the = sign.

  5. Save your changes.

Preventing Unauthorized Access When Using Other Database Security Methods

If you are not embedding a database password for an Access database, or are working against a SQL Server or MSDE database, preventing unauthorized database access from a data access page typically consists of performing the following steps:

  1. Preventing the user ID and password (that you use to access the database while authoring a data access page) from being saved with the page.

  2. Determining how users of the page will be authenticated; that is, determining how their logon identities will be entered and validated.

  3. Determining how—or if—you can control the level of access for individual users.

  4. Preventing unauthorized access from malicious scripts hidden in the page.

Preventing the User ID and Password from Being Saved with the Page

For an Access database back end secured with user-level security, the only way to prevent unauthorized access is to prevent the security account user ID and password from being saved with the information used to connect to the database. This will cause a logon dialog box to be displayed that requires a user to enter a correct logon account name and password before the data access page can connect to the database. This is also appropriate for SQL Server back-end databases, regardless of whether you are using native SQL Server security accounts or Windows NT integrated security for authentication. To prevent a logon password from being saved with a data access page's connection information, you must use the Data Link Properties dialog box, as described in the following procedure.

To prevent the password used to connect to a database from being saved with a data access page

  1. Start Access and open the data access page you want to work with in Design view.

  2. On the View menu, click Field List.

  3. On the Database tab, right-click the database name, and then click Connection.

  4. Under Enter information to log on to the server, make sure the Allow saving of password check box is cleared.

  5. Click OK, and save your changes to the data access page.

Now whenever the data access page is opened, the user must provide the appropriate user name and password to log on to the database, or if you are using integrated security with SQL Server, the user's identity must be passed in from the Windows NT Server network's authentication system.

Authenticating Users of Data Access Pages Connected to an Access Database

Users of secured Access databases must be authenticated against account information stored in a workgroup information file (.mdw). For authentication to work correctly for a secured Access database, you must also make sure that the connection information for a data access page specifies the correct workgroup information file. To do this, you must also make sure that the workgroup information file is in a location, such as a public network share, that can be accessed by all users who need to open the data access page. The following procedure describes how to specify this information for a data access page.

To specify the workgroup information file to use when opening a data access page that is connected to a secured Access database (.mdb)

  1. Start Access and open the data access page you want to work with in Design view.

  2. On the View menu, click Field List.

  3. On the Database tab, right-click the database name, and then click Connection.

  4. On the All tab, double-click Jet OLEDB:System database.

  5. Specify the path to the correct workgroup information file in the Property Value box. For two-tier data access pages, this should be a UNC path (such as \\ServerName\ShareName) to the file on a network share; for three-tier data access pages, this should be a path that is local to the IIS server that is used to publish the page (such as C:\Data\Secured.mdw).

  6. Click OK twice to close the Data Link Properties dialog box, and then save your changes to the data access page.

To determine who can open the data access page, you must configure the authentication method used on the IIS server. For all methods of IIS authentication, once the page is open, if user-level security has been established for the database, the Jet database engine will prompt the user to enter a user account name and password before connecting to the database. Once the user is connected, he or she will have the level of permissions defined for that user account.

If you would rather not prompt users for name and password, you can define a single set of permissions for the default Admin account and not require users to log on (by not setting a password for the Admin user). However, if you do this, all users of the data access page will have the same set of permissions. For information about how to establish user-level security for the Access back-end database, search Access Help for "Protect a database with user-level security." For information about configuring IIS authentication, see "Configuring Internet Information Server Authentication Methods," later in this article.

Authenticating Users of Data Access Pages Connected to a SQL Server Database

How users of secured SQL Server databases are authenticated depends on how security accounts are defined for the database. If the database is configured to use native SQL Server security accounts, users are authenticated against information stored on the database server itself. As long as you haven't stored the user ID and password you used while authoring the page, users will be prompted to enter a user ID and password when they open the page, and that logon information will be passed to the server for authentication.

If it's acceptable to specify a single level of access for all users who open the database through a data access page, you can add the IIS Anonymous User account as a logon to the back-end database by using SQL Server Enterprise Manager, and then use Anonymous Access authentication on the IIS server. In this configuration, users will not be prompted to log on to the database after the page is opened. For information about configuring Anonymous Access authentication, see "Using Anonymous Access Authentication," later in this article.

If you are using Windows NT integrated security to connect to a SQL Server database from a data access page, only authorized Windows NT accounts will be allowed to open or work with the database—their Windows NT logon identity will be passed to the database. To use Windows NT authentication to connect to a SQL Server database from a data access page, you must use the Data Link Properties dialog box as described in the following procedure.

To use Windows NT authentication to connect to a SQL Server database from a data access page

  1. Start Access and open the data access page you want to work with in Design view.

  2. On the View menu, click Field List.

  3. On the Database tab, right-click the database name, and then click Connection.

  4. Under Enter information to log on to the server, click Use Windows NT Integrated security.

  5. Click OK, and save your changes to the data access page.

Whenever the data access page is opened, only Windows NT logon accounts that have been authorized to open and work with the SQL Server database will be allowed to log on to the database. For more information about configuring Windows NT Challenge/Response authentication on the IIS machine used to publish the page, see "Using Windows NT Challenge/Response Authentication," later in this article.

Important   For the highest level of security when you are using IIS to share a data access page across a network, your data access page should use three-tier data access against secured Access and SQL Server databases. Using three-tier data access against a secured database requires you to perform additional steps to configure authentication. For information about how to do this, see "Deploying Three-Tier Pages," later in this article.

Controlling the Level of Access

The strategies for controlling the level of access once a database is opened from a data access page are similar to those used in desktop solutions. For Access databases, you can use either of the following methods after establishing user-level security for the database:

For SQL Server databases, the methods you can use are similar to those used with Access databases; in addition, you have the option of using Windows NT authentication:

When you use both Access databases and SQL Server databases with data access pages, if you want to allow users to update information in the database, you must grant the appropriate permissions (such as Update, Insert, and/or Delete permissions) on the tables that contain the data you want to update. Granting permissions on a query (Access) or view (SQL Server) used by the data access page is not sufficient.

Important    Depending on (1) the data access method you choose for the data access page, (2) where the database is located on the network relative to the Internet Information Server used to publish the page, and (3) how authentication is defined on that server, you may not be able to control the level of access for individual users. That is, you may only be able to control access based on a single account used for all users who open the page. For more information, see the next section, "Configuring Internet Information Server Authentication Methods."

Configuring Internet Information Server Authentication Methods

There are three forms of authentication you can use for HTML pages published with Microsoft Internet Information Server:

When used with three-tier data access pages, each method has certain advantages and disadvantages, and each requires additional configuration to work correctly.

After configuring the data access page to use three-tier data access, you can determine what form of authentication you want to use with the page. You can configure an authentication method for an individual page or for the entire folder where you are publishing the page on your IIS computer. The following procedure describes how to configure an authentication method.

To configure an authentication method used for a data access page

  1. On the computer running Internet Information Server, start the Internet Service Manager.

  2. Navigate to the folder where you are publishing your page.

  3. Do one of the following:
  4. Select the authentication method(s) you want to use: Anonymous Access, Windows NT Challenge/Response, or Basic.

    Figure 2. Setting up authentication methods

The following sections describe the options available when using each method of authentication.

Using Anonymous Access Authentication

If you select Allow Anonymous Access for the page or the folder the page is in, by default, IIS will use the IIS Anonymous Access user account (Internet Guest Account) to access the page's data source. You can view this account in Windows NT 4.0 by running the User Manager for the IIS computer's local domain. In Windows 2000, you can view this account by running the Computer Management snap-in (under System Tools | Local Users and Groups | Users). The default account name will have this format: IUSR_ComputerName. For example, if the name of the computer your IIS server is running on is OurWebServer, the IIS Anonymous Access user account will be named IUSR_OURWEBSERVER.

However, in most cases, the default IIS Anonymous Access account won't be able to connect to a database that's located on another computer. In this case, you must specify a new Anonymous User account that is a domain account, so that both computers running IIS and the database server will recognize it. IIS allows you to specify an Anonymous Access account for the particular Web site or folder that contains the data access page, or for the page file itself. In both versions of Windows NT, you do this by running the Internet Service Manager, which is available from the Start menu under Programs | Administrative Tools | Internet Service Manager. To specify a new Anonymous Access account, navigate to the Web site, folder, or page file you want to work with, right-click the item, and then click Properties. On the Directory Security tab, in the Anonymous access and authentication control group, click Edit to specify the Windows NT user account to use. If an appropriate existing Windows NT user account isn't available, you will need to contact your network administrator to create a new account for this purpose. Depending on the kind of database your page is connecting to, the Anonymous User account must meet the following requirements:

An advantage of this method is that it is secure and simple to implement, but because only one user account is being used to open the database, all users of the data access page must have the same level of security permissions. Additionally, if password aging is in effect for the Anonymous Access account, you will need to periodically update the password on the account.

Using Windows NT Challenge/Response Authentication

If you clear the Allow Anonymous Access option for the page or the folder the page is in, and select the Windows NT Challenge/Response Authentication option, the Internet Information Server will attempt to connect to the database by using the account of the user who opens the page in a browser. An important restriction in Windows NT Server 4.0 is that this connection appears to another computer as a "Null" session attempt with no security context. This is because a connection's security context can't be delegated (passed on) to another computer under Windows NT Server 4.0. This means that, by default, if the SQL Server or Access database is located on a different computer than the Internet Information Server that was used to publish the page, the connection will usually fail.

There are two ways to address this limitation when publishing three-tier pages under Windows NT Server 4.0:

If IIS and your SQL Server database are running on computers that are running Windows 2000 Server, either of the above approaches will work, but Windows 2000 includes new Kerberos Version 5 authentication features, which allow you to delegate (pass on) a user's security context from one computer to another computer. By using this Windows 2000 feature, you can establish a secure connection when IIS and a database server are running on separate computers. All three methods are described in the following sections.

Configuring Windows NT Challenge/Response Authentication When Running IIS and the Database Server on the Same Computer

One method to avoid the Windows NT Server 4.0 "Null session" restriction for three-tier pages is to put the database on the same computer as the one that is running IIS to publish the data access page. For a SQL Server database, SQL Server must be installed and running on that computer. For an Access database, the database must reside in a local folder on that computer. Next, use a local computer address for the connection's data source. For example, for a SQL Server database, you need to set the connection's data source to (local), and for an Access database, you must set the data source to use a path on the IIS server's local drive.

When you initially author a data access page by using a SQL Server database as a data source, the server the database resides on is identified by its network name, such as DATASERVER1. Before you publish the page, you must reset the page's data source to (local) so that the MSODSC control treats the database as local to the IIS server computer. Similarly, when you initially author a data access page against an Access database, you may be working against either a local copy of the database or a copy in a public share before you publish it. If this is the case, after you finish authoring the page, create a folder on the computer that is running the copy of IIS that will be used to publish the page, and put a copy of the database in that folder. Then reset the page's data source to the path that is local to the computer running IIS. (For example, "C:\Databases\MyDatabase.mdb"; not "\\MyServer\Databases\MyDatabase.mdb".) The following procedure describes how to do this for both SQL Server and Access data sources.

To set a data access page to use the copy of the database that is local to the computer running IIS to publish the page

  1. Start Access and open the data access page you want to work with in Design view.

  2. On the View menu, click Field List.

  3. On the Database tab, right-click the database name, and then click Connection.

  4. Do one of the following:
  5. Click OK, and save your changes to the data access page.

When using this authentication configuration, if you want to define security permissions for a data access page connected to a SQL Server database, you must define permissions on the server by specifying the Windows NT users or groups that will be using the page. This allows you to define different levels of permissions for different users or groups. However, because Access user-level security can't recognize Window NT logon accounts, you can't define different levels of security. If you define user-level security at all, you must define permissions for the default Admin user account.

The advantages of this approach are that NT Challenge/Response Authentication is a secure method of authenticating users, and when using SQL Server databases it is easy to give different users different levels of permissions. The disadvantage of this method is that locating both IIS and SQL Server on the same computer limits scalability.

Allowing the Null Session to Access SQL Server by Using the TCP/IP Network Library

Another method to avoid the Windows NT Server 4.0 "Null session" restriction for three-tier pages that connect to SQL Server is by using the TCP/IP Client Network Library, which does not depend on session security. How to do this is described in the following procedure.

  1. Start Access and open the data access page you want to work with in Design view.

  2. On the View menu, click Field List.

  3. On the Database tab, right-click the database name, and then click Connection to display the Data Link Properties dialog box.

  4. Click the All tab, and then double-click the Network Library property.

  5. In the Property Value box, enter DBMSSOCN. (This is the name of TCP/IP SQL Server Client Network Library [dbmssocn.dll], which by default is installed in the c:\winnt\system32\ folder or the c:\windows\system folder.)

  6. Click OK twice, and save your changes to the data access page.

When using this authentication configuration, SQL Server must be able to use its native security (that is, SQL Server and Windows NT must be specified on the Security tab of the server's property sheet in Enterprise Manager). To define per-user security permissions, you must define permissions by using native SQL Server account names, and users will be prompted to enter their account names and passwords when opening the page. To define per-page security permissions, you must also use a native SQL Server account name, but you can either save the name and password in the page (by displaying the Data Link Properties dialog box, as described in steps 1 through 3 above, and then specifying the user name and password and selecting the Allow saving password check box), or you can add the name and password to the MSDFMAP.ini file, as described in the "Remote Data Services Security Issues" section.

The advantages of this method are that it is easy to implement and that it allows you to put IIS and SQL Server on different computers. A disadvantage of this method is that it is not very secure. When using per-page security, the name and password are stored in the page in an unencrypted format, and when using per-user security, the name and password are sent across the wire. In either case, the name and password are transmitted in HTTP packets, which are potentially accessible to a malicious user using a "packet sniffing" utility. If you want to avoid this risk, you can set up your server to publish the data access page by using a Secure Sockets Layer (SSL) encrypted connection (HTTPS). For information about how to enable an encrypted connection, see the documentation for Microsoft Internet Information Server. An additional disadvantage when using per-user security is that users must be prompted to enter their name and password.

Allowing the Null Session to Access an Access Database by Using the NullSessionShares Registry Value

Another method to avoid the Windows NT Server 4.0 "Null session" restriction for three-tier pages that connect to Access databases is to edit the NullSessionShares value in the registry on the computer running IIS and add the name of the share where the database is located. How to do this is described in the following procedure.

  1. Click Run on the Windows Start menu, type regedt32.exe, and then click OK. (Important:  Do not use regedit.exe or you will be unable to edit the NullSessionShares value.)

  2. Navigate to the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\
    Parameters key.

  3. In the right pane, double-click the NullSessionShare value to display the Multi-String Editor dialog box.

  4. At the end of the list of any other values, enter the path to the share that contains the database file: for example, \\MyServer\Databases\MyDatabase.mdb.

  5. Click OK, and then close the Registry Editor.

The primary advantage of this method is the same as allowing the null session to access SQL Server by using the TCP/IP Network Library: It allows you to put IIS and the Access database on different computers. Additionally, because the database is being opened through the file system, there are no issues with password and connection information being exposed in HTTP packets. The primary disadvantage is that you must edit the registry to use this procedure.

Using Kerberos Version 5 Authentication in Windows 2000

The Windows 2000 Active Directory can use a technology called Kerberos Version 5 (or V5) Authentication. A feature of Kerberos V5 authentication called delegated authentication allows network services running on one computer to delegate (pass on) a user's security context to network services running on another computer. If IIS and SQL Server are running on separate Windows 2000 servers, you can use delegated authentication to avoid the "Null session" problem that occurs when using three-tier pages under Window NT Server 4.0. However, under this scenario, all client computers must also be running Windows 2000 Professional: Although Windows 2000 Server does provide an Active Directory upgrade for client computers running Windows 95/98 or Window NT Workstation 4.0, the Active Directory clients for these versions of Windows don't support Kerberos V5 authentication.

This method works only if the IIS 5.0 computer is configured to use Integrated Windows Authentication (which is equivalent to IIS 4.0 Windows NT Challenge/Response Authentication). Also, both IIS and SQL Server computers must be in the same Windows domain—that is, this will not work if the database computer is across a firewall or proxy server.

To use this method, the computer running IIS 5.0 must be configured to be trusted for delegation in Active Directory, as described in the following procedure. In most situations, a network administrator must perform this procedure.

To configure the IIS computer to be trusted for delegation

  1. On a domain controller, open the Active Directory Users and Computers snap-in. To do this, click Start, point to Programs, point to Administrative Tools, and then click Active Directory Users and Computers.

  2. In the console tree, click Computers.

  3. In the details pane, click the computer running IIS, and then click Properties on the Action menu.

  4. On the General tab, select Trust computer for delegation.

The advantages of this method are that it is easy to implement, that it allows you to put IIS and SQL Server on different computers for better scalability and management, and that it is very secure. Additionally, this method provides the flexibility of providing security permissions based on Windows user or group accounts. The disadvantage of this method is that all tiers—IIS, SQL Server, and client computers—must be running under Windows 2000.

Using Basic Authentication

If you clear the Allow Anonymous Access and Windows NT Challenge/Response Authentication options for the page or the folder the page is in, and select the Basic Authentication option, the Internet Information Server will prompt the user to enter his or her user account and password before opening the page. This method of authentication doesn't use delegation and for this reason doesn't have the restrictions described for Windows NT Challenge/Response authentication. However, Basic Authentication sends user account and password information across the network in an unencrypted format that could be intercepted by a malicious user. If you want to avoid this risk, you can set up your server to publish the data access page by using a Secure Sockets Layer (SSL) encrypted connection (HTTPS). For information about how to enable encryption, see the documentation for Microsoft Internet Information Server.

Remote Data Services Security Issues

A three-tier data access page connects to its database by using the Remote Data Service (RDS) running on your Web server. The DataFactory object of RDS presents certain security issues that need to be addressed, regardless of whether you are publishing data access pages on your Web server or not. In addition to these security issues, you must configure the RDS components in order for three-tier data access pages to work correctly.

MSADC Virtual Directory Settings

Before working with the RDS components, you should confirm that the directory that contains the components on your Web server (which by default is C:\Program Files\Common Files\System\msadc) is correctly mapped to a virtual root directory named MSADC with the correct security permissions. The default configuration for IIS is usually correct, but if these settings have been changed, you may get errors that tell you your server isn't correctly set up for remote access.

To confirm that Virtual Directory and Directory Security settings for RDS components are correctly configured

  1. Start the Internet Services Manager. On the Programs submenu, point to Administrative Tools, and then click Internet Services Manager.

  2. In the right pane, under Default Web Site, there should be an MSADC virtual directory node. If there is no MSADC virtual directory node, you must add one and configure it as described in the following steps.

  3. Click the MSADC virtual directory node, and then on the Action menu, click Properties.

  4. On the Virtual Directory tab, confirm or set Read and Execute permissions, as shown in the following screen shot:

    Figure 3. Read and Execute permissions for the MSADC virtual directory

  5. On the Directory Security settings tab, authentication settings should be set the same as the virtual directories that you've created for your data access pages.

RDS DataFactory Object Security Issues

The Remote Data Service (RDS) includes a component called the RDS DataFactory object. The DataFactory object can potentially expose unsafe methods. When configured to allow access to these methods, the DataFactory object may permit an otherwise unauthorized Web user to perform privileged actions, including the following:

Earlier versions of MDAC (MDAC 1.5 and MDAC 2.0) installed RDS in this "unrestricted" mode by default, and although MDAC 2.1 (which is installed with Microsoft Office and SQL Server 7.0) installs in RDS in "safe" mode if no previous installation of MDAC exists, it does not change RDS configuration when upgrading a previous installation. You should make sure that the RDS configuration on your IIS is configured for "safe" mode. The simplest way to do that is to run this registry entry file:

C:\Program Files\Common Files\System\msadc\handsafe.reg

Important   For additional detailed information on RDS security issues, see Microsoft Security Bulletin MS99-025 at www.microsoft.com/technet/security/bulletin/ms99-025.asp.

When RDS is running in "safe" mode, a default custom handler named MSDFMAP.Handler is used to protect databases from direct exposure to Web clients that might take advantage of unsafe DataFactory methods. Additionally, MSDFMAP.Handler protects servers and services from attacks that use OLE DB providers to access the file system and shell commands. The behavior of this handler is controlled by the MSDFMAP.ini file, which is installed in the Windows directory (C:\WINNT, for example). To allow database access from a three-tier data access page, you (or the Web server administrator) must modify the MSDFMAP.ini file. The method you use depends on whether your page connects to an Access database (.mdb) or a SQL Server/MSDE database.

The MSDFMAP.ini file contains four types of sections:

Section Name Description
Connect Used to specify connection string mappings.
SQL Used to specify command string (typically a SQL statement) mappings.
UserList Used to override default access permissions for a list of users.
Logs Used to specify the name of a file where all errors can be logged.

The techniques described in the following procedures use only the [connect default], [sql default], and user-defined [connect] sections of the MSDFMAP.ini file. For information about using the other sections of the MSDFMAP.ini file and detailed information about using the MSDFMAP.Handler, see "Using the Customization Handler Feature in RDS 2.0" at www.microsoft.com/data/ado/rds/custhand.htm.

Settings in the [connect default] Section

The MSDFMAP.ini file contains a [connect default] section that defines how the MSDFMAP.Handler handles any connection that is not defined in a user-defined [connect] section of the .ini file. By default, this section of the file looks like this:

[connect default]
;If we want to disable unknown connect values, we set Access to
;NoAccess
Access=NoAccess

Important   Do not change this section. As long as RDS is running in safe mode (that is, you have run C:\Program Files\Common Files\System\msadc\handsafe.reg), and the [connect default] section contains the line Access=NoAccess, no unknown connection will be allowed. Only connections you define yourself by creating new [connect] sections will be allowed to open databases by using the RDS component.

Settings in the [sql default] Section

The MSDFMAP.ini file contains a [sql default] section that defines how the MSDFMAP.Handler handles SQL statements that are not already defined in user-defined [sql] sections of the .ini file. By default, this section of the file looks like this:

[sql default]
;If we want to disable unknown SQL values, we set Sql to an invalid
;query.
Sql=" "

In this configuration, only SQL statements that are defined in a user-defined [sql] section will be allowed against the databases specified in user-define [connect] sections of the file. Although this is the most secure way to use the MSDFMAP.Handler, data access pages dynamically generate SQL statements to display data based on user actions on the page. And although it is theoretically possible to use a trace utility to determine what those statements are for a data access page, and to define each of them in user-defined [sql] sections of the file, it is beyond the scope of this article to describe how to do so. For this reason, the procedures defined in the next section depend on disabling this section by commenting out the Sql=" " with a semicolon, like this:

[sql default]
;If we want to disable unknown SQL values, we set Sql to an invalid
;query.
;Sql=" "

Important   Disabling the Sql=" " line in the [sql default] section allows any connection defined in the user-defined [connect] sections to send any SQL statement against that connection. However, this doesn't allow the RDS component to override security settings defined for the database itself. For example, if the user of a data access page or malicious script in the page sends a SQL statement that performs an action that is disallowed by the database security system for the current user's security context (user, role, or group), that action will still be prevented.

Creating a User-Defined [connect] Section for a Page That Connects to an Access Database

The techniques you use to create a user-defined [connect] section differ somewhat depending on what kind of database your data access page is connecting to. The following procedure describes how to modify the MSDFMAP.ini file for a page that connects to an Access database (.mdb).

To modify the MSDFMAP.ini file to allow three-tier data access to an Access database (.mdb)

  1. Make sure that RDS is running in safe mode by running the handsafe.reg registry entry file. You can do that by navigating to the C:\Program Files\Common Files\System\msadc folder on the computer running IIS, and then double-clicking the handsafe.reg file.

  2. On your IIS computer, start Notepad and open MSDFMAP.ini. By default MSDFMAP.ini is located in C:\Winnt.

  3. Confirm that the [connect default] section is set to Access=NoAccess, as described in "Settings in the [connect default] section," earlier in this article, and that the [sql default] section will allow any SQL statement against allowed connections, as described in "Settings in the [sql default] section," earlier in this article.

  4. Create a new [connect] section that contains the path to the Access database file used by your page. In all cases, unless all users are running Windows 2000 with Kerberos delegation, the database must be located on the same computer as the IIS. For example, if the local path to the database that is on the IIS computer is C:\Data\MyDB.mdb, the first line of the [connect] section will look like this:
    [connect C:\Data\MyDB.mdb]
    
  5. The second line in the section defines what sort of access you want to allow to the database. If you want to allow both read and write access to your database, set the Access= parameter to ReadWrite, like this:
    [connect C:\Data\MyDB.mdb]
    Access=ReadWrite
    

    If you want to allow read-only access to your database, set the Access= parameter to ReadOnly, like this:

    [connect C:\Data\MyDB.mdb]
    Access=ReadOnly
    

    You can use this method to connect three-tier data access pages to databases that have been secured by user-level security, a database password, or no database security. If the database you are connecting to has either user-level security or a database password defined (and the database password has not been embedded in the page, as described in the "Using an Access Database Password" section earlier in this article), when a user opens the page, he or she will be prompted to enter the appropriate user ID and password, or database password, before the data will be displayed on the page. If this level of security is satisfactory, you can save these changes to the MSDFMAP.ini file and stop here.

  6. If you want to use database security, but don't want users to be prompted, you can safely embed the appropriate user ID and password, or database password, in the MSDFMAP.ini file by defining an additional Connect= parameter that contains the full connection string required to connect to the database. If the database is secured with user-level security, the connection string must include the user name, password, and the path to the workgroup information file (.mdw) that was used to secure the database. For example, if you have established user-level security and defined a DAPUser account with a password of OpenDAP, you need to add a line like this to the file:
    [connect C:\Data\MyDB.mdb]
    Access=ReadWrite
    Connect="Provider=Microsoft.Jet.OLEDB.4;User ID=DAPUser;Password=OpenDAP;Data Source=C:\Data\MyDB.mdb;Jet OLEDB:System database=C:\Data\Secured.mdw;"
    

    Note   Even though the header for the [connect] section contains the path to the database, you must also specify the path in the Data Source parameter of the connection string. This is because the Connect= parameter of the [connect] section requires an entire connection string which the MSDFMAP.Handler will use to connect to the database instead of the connection information stored in the data access page.

    Similarly, if the database is secured with a database password of OpenSesame, you need to add a line like this to the file:

    [connect C:\Data\MyDB.mdb]
    Access=ReadWrite
    Connect="Provider=Microsoft.Jet.OLEDB.4;Data Source=C:\Data\MyDB.mdb;Jet OLEDB:Database Password=OpenSesame;"
    
  7. Save your changes to the file and test your data access page by opening it in Internet Explorer from a client computer.

Embedding security information in the MSDFMAP.ini Connect= parameter is relatively secure, because as long as the file system of the IIS computer is secured, users can't easily gain access to this information. In this case, users will not be prompted to enter database security information before data can be displayed on the page, but if your database is secured with user-level security, only one user account and its set of permissions will be used for all users opening the page.

However, whether you embed database security information in the MSDFMAP.ini file, or prompt users to enter this information when they open the page, the database security information is sent in unencrypted format (plain text) to the database through HTTP packets. This presents the risk of a malicious user retrieving the security information from the network stream by using a network monitoring program (such a program is often called a "packet sniffer" or "protocol analyzer"). If you want to eliminate this security risk, you must additionally use a Secure Sockets Layer (SSL) encrypted connection (HTTPS) to communicate between IIS and the database. For information about how to enable encryption, see the documentation for Microsoft Internet Information Server.

Creating a User-Defined [connect] Section for a Page That Connects to a SQL Server or MSDE Database

This section describes two methods for connecting a three-tier data access page to a SQL Server or MSDE database.

Method One

The following procedure describes how to modify the MSDFMAP.ini file for a page that connects to a SQL Server or MSDE database.

Important   Because this method allows you to specify only the database server name (and not a specific database) in the MSDFMAP.ini file, if you are using this method with more than one data access page that connects to the same server, all pages must share the same Access= setting (ReadOnly or ReadWrite).

To modify the MSDFMAP.ini file to allow three-tier data access to a SQL Server or MSDE database

  1. Make sure that RDS is running in safe mode by double-clicking the handsafe.reg file (C:\Program Files\Common Files\System\msadc) on the computer running IIS.

  2. On your IIS computer, start Notepad and open MSDFMAP.ini. By default, MSDFMAP.ini is located in C:\Winnt.

  3. Confirm that the [connect default] section is set to Access=NoAccess, as described in the "Settings in the [connect default] section" section of this article, and that the [sql default] section will allow any SQL statement against allowed connections, as described in the "Settings in the [sql default] section" section of this article.

  4. In all cases, unless all users are running Windows 2000 with Kerberos delegation, the database server must be located on the same computer as IIS. When using a local SQL Server or MSDE, create a new [connect] section that uses (local) to reference the server. For example, the first line of the connect section should look like this:
    [connect (local)]
    

    If all users are using Windows 2000, and the SQL Server or MSDE is on a separate computer that is configured to use Kerberos delegation (as described in the "Using Kerberos Version 5 Authentication in Windows 2000" section earlier in this article, you can create a new [connect] section that references the server by name. For example, if the SQL Server or MSDE is named DataServer1, the first line of the connect section should look like this:

    [connect DataServer1]
    

    Note   All examples in the following steps use [connect (local)], but you can substitute a specific server name if you are using Windows 2000 and Kerberos delegation.

  5. The second line in the section defines what sort of access you want to allow to the database. If you want to allow both read and write access to your database, set the Access= parameter to ReadWrite, like this:
    [connect (local)]
    Access=ReadWrite
    

    If you want to allow read-only access to your database, set the Access= parameter to ReadOnly, like this:

    [connect (local)]
    Access=ReadOnly
    

    You can use this method to connect three-tier data access pages to databases that have either database security established or no database security. If the database you are connecting to has been secured by using native SQL Server accounts (and the security information has not been saved in the page's connection string), when a user opens the page, he or she will be prompted to enter the appropriate user ID and password. If this is satisfactory, you can save these changes to the MSDFMAP.ini file and stop here.

  6. If you want to use database security, but don't want users to be prompted, you have two choices: (1) You can use Integrated NT Security so that SQL Server or MSDE uses the users Windows NT logon to define the user's security context, or (2) You can use native SQL Server accounts and embed a single user ID and password in the MSDFMAP.ini by defining an additional Connect= parameter that contains the full connection string required to connect to the database.

    To use option 1, define security on SQL Server or MSDE by using Windows NT Integrated security, and configure the connection information in the page to use Windows NT authentication, as described in the procedure in the "Authenticating Users of Data Access Pages Connected to a SQL Server Database" section of this article. Then modify the MSDFMAP.ini file as described in this procedure.

    To use option 2, you must use the procedure in Method Two (below) instead.

  7. Save your changes to the file and test your data access page by opening it in Internet Explorer from a client computer.

Method Two

The following procedure describes how to embed native SQL Server security logon information in the MSDFMAP.ini file when connecting to a SQL Server or MSDE database. This allows you to create a three-tier page that connects to its data source without prompting the user for security logon information.

If you specify logon information by using a native SQL Server account, all users will be logged on with the same set of permissions. This method is similar to embedding the logon information in the page itself, but is more secure because the logon information is inaccessible to the user. This is because if you embed security information in the page itself, the user could use the View Source command to view the HTML source for the page and discover the logon information. But, if you embed the security information in the MSDFMAP.ini file, that file resides on the server and is inaccessible to the user as long as the server's file system itself is secured.

Just as with all methods that use logon strings in the connection string, this method sends security information as clear text (unencrypted) across the network and can be intercepted by a malicious user using a network monitor program. To eliminate that risk, you must set up SSL to use an HTTPS encrypted connection to communicate with the database.

If you specify to use Windows NT Integrated security in the logon information embedded in the MSDFMAP.ini file, the database will be opened by using each user's Windows logon account and granted permissions defined for that account or group. This assumes that you have defined SQL Server or MSDE database security by using Windows NT Integrated security.

To modify the MSDFMAP.ini file to allow three-tier data access to a SQL Server or MSDE database by embedding security information in the MSDFMAP.ini file

  1. Make sure your IIS is running in safe mode by double-clicking the handsafe.reg file (C:\Program Files\Common Files\System\msadc) on the computer running IIS.

  2. On your IIS computer, start Notepad and open MSDFMAP.ini. By default, MSDFMAP.ini is located in C:\Winnt.

  3. Confirm that the [connect default] section is set to Access=NoAccess, as described in the "Settings in the [connect default] section" section of this article, and that the [sql default] section will allow any SQL statement against allowed connections, as described in the "Settings in the [sql default] section" section of this article.

  4. Create a new connection alias in the file, using a name that's unique to the file. For example, if you want to use the alias "MyDB" the first line of the connection alias will look like this:
    [connect MyDB]
    
  5. The second line in the section defines what sort of access you want to allow to the database. If you want to allow both read and write access to your database, set the Access= parameter to ReadWrite, like this:
    [connect MyDB]
    Access=ReadWrite
    

    If you want to allow only read access to your database, set the Access= parameter to ReadOnly, like this:

    [connect MyDB]
    Access=ReadOnly
    
  6. The third and final line in the section sets the Connect= parameter, which defines the connection string that the MSDFMAP.Handler will use for connections that refer to the alias through the Microsoft OLE DB Remoting Provider (MS Remote).

    For a page that connects to a SQL Server or MSDE database, the connection string defined for the Connect= parameter must include a Provider= reference to the OLE DB Provider for Microsoft SQL Server (SQLOLEDB.1); a Data Source= reference (local), which points to the SQL Server or MSDE that is local to the IIS computer; and an Initial Catalog= reference that points to the database you want to work with (for example, Initial Catalog=Northwind).

    If you are using native SQL Server security accounts, you must also include User ID and Password references for the security account you want all users to log in under. If you are using Integrated NT authentication, you must specify that in the connection string.

    To connect the page by using a native SQL Server account, the complete section should look something like this:

    [connect MyDB]
    Access=ReadWrite
    Connect=Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=Northwind;User ID=DAPUser;Password=OpenDAP;
    

    To connect the page by using Integrated NT authentication, the complete section should look something like this:

    [connect MyDB]
    Access=ReadWrite
    Connect=Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI;
    
  7. Save these changes to the MSDFMAP.ini file.

  8. The connection information in the data access page must be modified to use the [connect MyDB] section defined in the MSDFMAP.ini file. To do that, locate the page's .htm file and open it in Notepad.

  9. Press CTRL+F to open the Find dialog box, type ConnectionString in the Find What box, and then click Find Next. This should locate the XML (Extensible Markup Language) tag at the beginning of the page's connection information: <a:ConnectionString>. You will be replacing all of the connection information between this tag and the closing </a:ConnectionString> tag. The new connection string must contain the following three parameters:
    Parameter Value
    Provider= This must be set to MS Remote.1.
    Data Source= This must be set to the name of the connection alias you created in step 4. Using the example from step 4, this would be:
    Data Source=MyDB
    Remote Server= This must be set to the HTTP address for the server where the page is published. For example, if the HTTP address for your IIS server is http://MyServer1, this would be:
    Remote Server=http://MyServer1

    Using the examples in the table above, the modified connection information in the page file would look like this:

    <a:ConnectionString>Provider=MS Remote.1;Data Source=MyDB;Remote Server=http://MyServer1; </a:ConnectionString>
    
  10. Save these changes to the data access page's .htm file and test your data access page by opening it in Internet Explorer from a client computer.

    Note   If a user opens a data access page that is configured to use three-tier data access through an Internet firewall, that user will be authenticated twice: once as the page is initially opened, and again as the MSODSC binds data to the page. As a result, if the server is configured to use Basic Authentication, the user will have to enter his or her user name and password twice. This occurs because two separate HTTP connections are being made: (1) the connection from Internet Explorer to the Web server to retrieve the page, and (2) the connection from the Microsoft OLE DB Remoting Provider (MS Remote) to the Web server to bind the data.

For more information on using the DataFactory custom handler (MSDFMAP.Handler), see "Using the Customization Handler Feature in RDS 2.0" at www.microsoft.com/data/ado/rds/custhand.htm.

Strategies for Maintaining Data Access Pages That Use Three-Tier Data Access

There are several ways to manage updates to pages that use three-tier data access:

If you don't use one of these approaches, you'll have to modify the connection strings in a text editor, such as Notepad, whenever you repost your data access pages.

Troubleshooting Problems with Three-Tier Access

If you've made an error in setting up your server, you'll see the following error message when you try to use your Web page:

Figure 4. Server setup error message

To correct this problem, make sure you have followed all of the appropriate procedures described in the "Remote Data Services Security Issues" section.

If you try to open a three-tier data access page by connecting to it with a file system path name (or a file: URL) instead of an HTTP URL, you'll see the following error message:

Figure 5. Connection path error message

To correct this problem, only set the UseRemoteProvider property to True when you are ready to publish the page. Once the UseRemoteProvider property is set to True, the page can only be opened by using an HTTP URL. If you need to do additional work on the page, you should temporarily set the UseRemoteProvider property back to False.

If you haven't set the UseRemoteProvider property to True—that is, if the page is using the default two-tier data access method, and the server used to publish the page is not included in the Internet Explorer Trusted Sites security zone—users will see the following message when they open your page:

Figure 6. UseRemoteProvider property not set to True (two-tier data access)

To correct this problem, you can either (1) configure your server to support a three-tier data access page, as described in this article, and then set the UseRemoteProvider property to True, or (2) you can use two-tier data access and make sure the server used to publish the page is included on the Internet Explorer Trusted Sites security zone, as described in the "How Internet Explorer Handles Cross-Domain Data Access" section of this article.

Summary of Deployment Steps and Options

This article has presented many alternatives for each step in the process of deploying data access pages. Because the choices you make depend on what resources are available to you, and how you and the users of your pages want to work, you will need to experiment a bit to find the approach best suited to your application and work environment. The contents of this section are meant to serve as general guidelines to help you narrow your choices.

If you are deploying data access pages on an intranet, you have two main sets of alternatives:

If you are deploying data access pages on the Internet, you have only one main alternative: You must deploy data access pages that use three-tier data access. As described in the "Data Access Modes" section of this article, this is because (1) You should not allow the direct physical access required for two-tier data access on an Internet Web server, because to do so totally compromises the security of the server; and (2) most users on the Internet are restricted to allow only HTTP access by security firewalls and proxy server software, and only three-tier pages establish the connection to the database by using HTTP via the RDS components.

Deploying Two-Tier Pages

Following is a high-level summary of the steps required to deploy two-tier pages on an intranet.

  1. Confirm that all users of your pages have Internet Explorer 5 and have the ability to install the Office Web Components if they have not already done so. For details on client software, see "Client Software Requirements." If you have control over how users install Internet Explorer, you may want to consider creating a custom installation using the IEAK that specifies the servers you will use to publish two-tier data access pages registered on the Trusted Sites security zone. For details on these Internet Explorer security issues, see "How Internet Explorer Handles Cross-Domain Data Access."

  2. Confirm that you have the appropriate Web server and database software available. For details, see "Server Software Requirements."

  3. Define security and authentication for your database, or coordinate with a database administrator to determine how the database you are working with is secured. For details, see "Authentication and Database Security Issues."

  4. Create the page as described in Access 2000 Help, leaving the setting of the UseRemoteProvider property at its default setting of False. Set the appropriate security-related connection settings for the page as described in the "Authentication and Database Security Issues" section.

  5. Copy the page to the IIS computer.

    If you authored the page against an Access database (.mdb) that was defined by using a local path to the database file, you must copy the database to a public share that is accessible to all users of the page and update the connection information in the page to point to that public share (preferably with a UNC path) before copying the page to the server.

    Important   If the IIS machine is being used to publish pages from a number of authors, you should consider having someone with scripting experience examine the pages before they are published to confirm that they don't contain malicious scripts. For details on scripting in data access pages, see "Programming Data Access Pages."

  6. Define the authentication for the page on IIS, or coordinate with a Web server administrator to do this. For details, see "Configuring Internet Information Server Authentication Methods."

  7. Provide information to users about how to connect to the page.

    If the database authentication method you used in steps 3 and 4 requires users to enter a database password, or user name and password, provide that information to users.

    If the server used to publish the page isn't registered as being in the Trusted Sites security zone in user's installations of Internet Explorer, they will see the error message shown in Figure 6 of the "Troubleshooting Problems with Three-Tier Access" section. You can instruct users to add the IIS machine to their Trusted Sites zone by using the procedure described in the "How Internet Explorer Handles Cross-Domain Data Access" section.

Deploying Three-Tier Pages

The steps to deploy three-tier data access pages are the essentially the same whether you are deploying pages on an intranet or the Internet. Following is a high-level summary of the steps required for three-tier data access pages.

  1. Confirm that all users of your pages have Internet Explorer 5 and have the ability to install the Office Web Components if they haven't already done so. If publishing your pages on the Internet, keep in mind that in most cases users must already have Office 2000 installed before they can open data access pages. For details on client software, see "Client Software Requirements."

  2. Confirm that you have the appropriate Web server and database software available. For details, see "Server Software Requirements."

  3. Define security and authentication for your database, or coordinate with a database administrator to determine how the database you are working with is secured. For details, see "Authentication and Database Security Issues."

  4. Create the page as described in Access 2000 Help, leaving the setting of the UseRemoteProvider property at its default setting of False until you are ready to publish the page. Set the appropriate security-related connection settings for the page as described the "Authentication and Database Security Issues" section. To make creating and maintaining the page simpler, consider using one of the strategies described in the "Strategies for Maintaining Data Access Pages That Use Three-Tier Data Access" section.

  5. Set the page to use three-tier data access by setting the UseRemoteProvider property to True, as described in the "Data Access Modes" section. If you aren't authoring the page on the server itself, copy the page to the IIS machine.

  6. Define the authentication for the page on IIS, or coordinate with your Web server administrator to do this. For details about the available options, see "Configuring Internet Information Server Authentication Methods."

  7. Configure Remote Data Service settings on IIS, or coordinate with your Web server administrator to do this. For details about the available options, see the "Remote Data Services Security Issues" section.

  8. Provide information to users about how to connect to the page. If the database authentication method you used in steps 3 and 4 requires users to enter a database password, or user name and password, provide that information to users.

Next Steps

For more information about working with data access pages, see "Programming Data Access Pages," "Creating Secure Data Access Pages," "Connecting Data Access Pages Together," and "Working with Data Access Pages in FrontPage 2000."

For more information about working with Web sites, see the Microsoft Web site at www.microsoft.com/ms.htm. In addition to a great deal of information about HTML and DHTML (including new behaviors available in Internet Explorer 5), you'll find information about creating and managing Web sites, including dealing with issues such as deployment and security. Below is a sampling of white papers available on the Microsoft Web site.

Finally, don't forget about consulting your Web site and database administrators and the many books about creating Web sites available in your local bookstore.

--------------------------------------------

© 1999 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, this paper should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft, ActiveX, FrontPage, Outlook, PivotTable, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.