Publishing a Database Through an IIS Virtual Directory

The tool used to manage SQL Server HTTP publishing is a Microsoft Management Console (MMC) snap-in named Configure SQL XML Support In IIS. This tool can be found in the Microsoft SQL Server program group on the Start menu. The unimaginative (and cumbersome) name does at least manage to accurately describe what the tool does. With this snap-in, you can create and manage IIS virtual directories that use the SQLISAPI application to publish XML data from SQL Server.

After you launch the snap-in and expand the server, each of the Web sites configured in IIS on the local computer appears in the left-hand pane. You can manage remote servers by selecting IIS Virtual Directory Management For SQL Server at the root of the tree and choosing Connect from the Action menu. Figure 4-2 shows the Configure SQL XML Support In IIS tool.

Figure 4.2 - The Configure SQL XML Support In IIS MMC snap-in tool

To publish a new virtual directory, you need to select the Web site on which you want to publish the directory, choose New, and then chooseVirtualDirectory from the Action menu to open the New Virtual Directory Properties dialog box. (Or right-click the Web site and use the shortcut menu.) Then you use this dialog box to configure the properties of the new virtual directory.

Specifying a Name and a Path for the Virtual Directory

The General tab of the New Virtual Directory Properties dialog box is shown in Figure 4-3. You use this tab to configure the name and location of the virtual directory.

Figure 4.3 - Specifying a name and a path for the virtual directory

In the Virtual Directory Name section, specify the name of the virtual directory that will be used as part of the URL to access the data it publishes. The URL takes the form http://domain/virtualdirectory. In intranet environments, the name of the IIS server hosting the virtual directory is usually specified in the domain part of the URL, so, for example, a virtual directory named northwinddata on an IIS server named webserver1 would be accessed using the URL http://webserver1/northwinddata. On the Internet, it’s customary to use the fully qualified DNS domain name to identify a particular Web server, so you’re likely to see a URL like http://www.northwindtraders.com/northwinddata. In either case, you use the name of the virtual directory to access the application, so you should choose a meaningful name that reflects the data being published.

In the Local Path section, you can specify the full path to the physical folder on the file system that the virtual directory relates to. In a conventional Web application, the HTML and ASP files used by the application would be stored here. In a SQLISAPI application, however, the folder doesn’t need to contain anything (although it must still be specified). If the folder is stored on an NTFS volume, you must ensure that any Microsoft Windows user accounts used to access the application (as configured on the Security tab of the dialog box) have been granted read permission.

Securing the Virtual Directory

You make your choices on the Security tab in the New Virtual Directory Properties dialog box to specify the type of security you want to use for the virtual directory, or more specifically, the authentication mechanism you want to use. This tab is shown in Figure 4-4.

Figure 4.4 - Specifying virtual directory security

Choosing an Authentication Model

You can choose from a number of ways to authenticate users when they’re accessing SQL Server. In a three-tier application (such as a SQL Server IIS application), you can use one of two basic models: the trusted server model or the impersonation/delegation model. In the trusted server model, the end users are anonymous to SQL Server and a specified account is used by the Web server to connect to the database on the user’s behalf. In the impersonation/delegation model, the user supplies a user name and password and the Web server uses the supplied credentials to "impersonate" the user when the Web server is accessing the database.

Strictly speaking, you use the term impersonation when the Web server uses the user’s credentials to access resources on the local server. You use the term delegation when the Web server accesses resources on a remote server using the user’s credentials. Whichever term is used, the basic principle is the same.

Of these two models, the trusted server model is the most scalable because it allows every end user to use the same credentials (and therefore the same connection settings) to access the database, making connection pooling more effective and minimizing the amount of security management that needs to be performed in the database itself. However, the impersonation/delegation model allows much more granular database permissions to be granted and enables much finer control of auditing. The approach you choose depends on the security and scalability requirements in your particular circumstances.

Figure 4-5 shows a comparison of these security models:

Figure 4.5 - Trusted server vs. impersonation/delegation authentication

For each of these models, you can choose from two possible configurations when you’re publishing a SQL Server virtual directory. SQL Server supports both integrated security based on a Windows 2000 user account and SQL Server security based on a separate SQL Server login. Because SQL Server authentication isn’t selected in a default in stallation of SQL Server 2000, you might have to use SQL Server Enterprise Manager to enable SQL Server authentication if you want to allow access through a SQL Server login.

Generally, Microsoft advises you to use Windows integrated security wherever possible because it’s more secure and manageable. You should use SQL Server logins only when the user can’t be authenticated by Windows, such as when you’re using the impersonation/delegation model with a browser that doesn’t support Windows integrated authentication.

Using the Trusted Server Model

You can use the trusted server model by selecting the Always Logon As option and specifying the security credentials to be used. You can specify a Windows user account or a SQL Server login to be used for database access on behalf of all HTTP clients.

If a Windows account is specified, you can use any local or domain account. (The default is the local IUSR_ computername account used by IIS for anonymous access.) You can specify the password here or allow IIS to synchronize the password automatically. By default, Enable Windows Account Synchronization is selected. You must, of course, ensure that the specified Windows account has access to the database and appropriate permissions for such database objects as tables, views, and stored procedures.

Using the Impersonation/Delegation Model

You can use the impersonation/delegation model by selecting either the Use Windows Integrated Authentication option or Use Basic Authentication (Clear Text) To SQL Server Account option.

To use Windows integrated authentication, the user must be accessing the virtual directory through an application or browser that supports Windows authentication (such as Microsoft Internet Explorer). If the user is already logged on to Windows, her credentials are encrypted and sent in the HTTP request header. If the user isn’t currently logged on to Windows or her account has insufficient permissions, she’ll be prompted to log on and her credentials will be sent in an encrypted fashion.

Using a SQL Server account for the impersonation/delegation security model involves sending the security credentials in plain text (unencrypted) from the browser to the Web server. This approach should rarely, if ever, be used in a production system without some other form of encryption, such as the Secure Sockets Layer (SSL) protocol.

Encrypting Data using SSL

You’ll encounter many occasions, particularly when building partner integration solutions, for which the data being accessed must remain confidential. To facilitate privacy, you can use the SSL protocol to encrypt the network traffic passed between the Web server and the client application or browser. To take advantage of SSL security, you must use the Internet Services Manager administration tool to install a certificate on the Web server and configure the virtual directory. Users will then be able to access the data in a secure manner using an HTTPS URL.

Specifying the Data Source

You specify the SQL Server database providing data to the SQLISAPI application on the Data Source tab of the New Virtual Directory Properties dialog box. Figure 4-6 shows the Data Source tab.

Figure 4.6 - Specifying the data source

You must specify the server on which the data is stored and the name of the database you want to publish data from. You use this configuration together with the security settings to construct the OLEDB connection string that the SQLISAPI application will use to connect to the database.

Specifying Data Access Settings

You use the Settings tab of the New Virtual Directory Properties dialog box to govern how data can be accessed through the virtual directory. This tab is shown in Figure 4-7.

Figure 4.7 - Specifying settings

Allowing URL Queries

URL queries are Transact-SQL statements specified as query strings in the URL used to access the virtual directory. If URL queries are allowed, the SQL statement is executed and the results are returned to the HTTP client. (I’ll talk about the syntax used to execute URL queries in the section "Using URL Queries to Test a Virtual Directory" later in this chapter.) URL queries give you a convenient way to develop and test a SQL Server application that uses a virtual directory, but because the client application can use a query string to send any Transact-SQL statement to the server, URL queries aren’t generally recommended for production systems for security reasons.

Allowing Template Queries

Template queries are the most commonly used way to publish data in an application through the virtual directory. The templates are stored as XML files on the Web server and encapsulate Transact-SQL queries. When you use a server-side template to execute a query, only the query results are returned to the client application or browser. The details of the actual query are hidden from the caller, making this a more secure approach to publishing data.

I’ll talk about the use of templates for database access over HTTP in Chapter 5, "Using XML Templates to Retrieve Data over HTTP."

Allowing XPath Queries

The XPath language is defined by the World Wide Web Consortium (W3C) as a standard navigation language. XPath queries enable you to use a subset of the XPath language to retrieve data based on an annotated schema. (You’ll learn all about the use of annotated schemas in Chapter 6, "Mapping Data with Annotated Schemas.") The schema is stored on the Web server as an XML file and maps XML elements and attributes to data in the database. You can use XPath to specify which data defined in the schema should be returned.

Allowing POST Queries

A query template can be sent to the Web server as an HTTP POST query. The query will then be executed, and the results will be sent back to the client. The template itself is usually implemented as a hidden form field on an HTML page.

Using the POST method to send templates requires that both the Allow POST and Allow URL Queries options be enabled and raises the same security issues that allowing URL queries does.

Ifyou have installed the XML For SQL Server 2000 tool, the Settings tab will contain an additional option to allow posting of updategrams. I’ll talk about XML For SQL Server 2000 in Chapter 8, "Additional Tools."

Creating Virtual Names

You configure the virtual names defined for the directory on the Virtual Names tab of the New Virtual Directory Properties dialog box. Virtual names are used in URLs to specify templates, schemas or database objects. The Virtual Names tab is shown in Figure 4-8.

You can create virtual names of type template, schema, and dbobject by clicking New on the Virtual Names tab to open the Virtual Name Configuration dialog box, where you can define the virtual name, type, and path for a virtual name.

Figure 4.8 - Creating virtual names

Using Virtual Names for Templates and Schemas

For templates and schemas, you can use virtual names to represent folders containing the XML template or schema files. For example, in our northwinddata virtual directory application, you can assign the virtual name productsdata to a folder that contains a template named catalog.xml. This template would then be accessed using the URL http://webserver1/northwinddata/productsdata/catalog.xml. When you specify a folder or a file as a virtual name, you must ensure that the appropriate NTFS permissions are set to allow the necessary accounts read access.

Using Virtual Names for Database Objects

For database objects, such as binary images, you can create a virtual name with no associated file or folder. This strategy allows you to use a URL to retrieve binary data. For example, you could use the virtual name dbobject to access the Picture field in the Categories table for a particular category in the Northwind database. The XPath necessary to retrieve the data would be included in a URL such as this one: http://webserver1/northwinddata/dbobject/categories[@CategoryID='1’]/@picture.

Although you can specify any name for a database object virtual name, naming it dbobject is a particularly sensible choice because this is the name that binary field references returned by AUTO mode queries include by default.

Specifying Advanced Options

The Advanced tab in the New Virtual Directory Properties dialog box allows you to specify advanced options. You can see the Advanced tab in Figure 4-9.

Figure 4.9 - Setting advanced options

The first option on the Advanced tab allows you to set the location of the SQLISAPI application DLL. Should you move this DLL, you should update the location here.

The Advanced tab also enables you to append custom settings to the OLEDB connection string used to connect to the database and to disable caching of annotated schemas. By default, schemas are cached in memory the first time they’re used to access data. While you’re developing the schema, you might want to disable caching, which forces the schema to be reloaded with each request.