Accessing SQL Server over HTTP

for RuBoard

The first thing you should do to begin working with XML data in Transact-SQL is to set up an IIS virtual directory using the Configure SQL XML Support in IIS menu option in the SQL Server program folder. Note that you can use ADO to process XML data from SQL Server without setting up a virtual directory. You may take this approach, for example, when building components that provide business processing functionality on top of processing the documents themselves .

Configuring a virtual directory allows you to work with SQL Server's XML features via HTTP. This opens up all sorts of possibilities. To begin with, it allows you to create data-driven Web sites with far less work than technologies such as ASP and JSP would require. Furthermore, you can use XSL style sheets to translate XML data returned from SQL Server into other document formats such as HTML and WML (Wireless Markup Language). Additionally, internal and external clients can work directly with your SQL Server-produced XML data via simple HTTP requests .

You use a virtual directory to establish a link between a SQL Server database and a segment of a URL. It provides a navigation path from the root directory on your Web server to a database on your SQL Server.

SQL Server's ability to publish data over HTTP is made possible through SQLISAPI, an ISAPI application that ships with the product. SQLISAPI uses SQLOLEDB, SQL Server's native OLE-DB provider, to access the database associated with a virtual directory and return results to the client.

Client applications have four methods of requesting data from SQL Server over HTTP. These can be broken down into two general types: those more suitable for private intranet access because of security concerns, and those safe to use on the public Internet:

Private intranet

1. Post an XML query template to SQLISAPI.

2. Send a SELECTFOR XML query string in a URL.

Public Internet

3. Specify a server-side XML schema in a virtual root.

4. Specify a server-side XML query template in a virtual root.

Because of their open -ended nature, methods 1 and 2 could pose security risks over the public Internet, but are perfectly valid on corporate or private intranets . Normally, Web applications use server-side schemas and query templates to make XML data accessible to the outside world in a controlled fashion.

Configuring a Virtual Directory

As I mentioned earlier, you set up virtual directories using the Configure SQL XML Support in IIS utility. It's in the Microsoft SQL Server folder under StartPrograms. Load it and you should see the IIS servers configured on the current machine. Click the plus sign to the left of your server to expand it. (If your server isn't listed, for example if it's a remote server, right-click the IIS Virtual Directory Manager node and select Connect to connect to it.) To add a new virtual directory, right-click the Default Web Site node and select NewVirtual Directory. You should then see the New Virtual Directory Properties dialog.

Specifying a Virtual Directory Name and Path

The Virtual Directory Name entry box is where you specify the name of the new virtual directory. This is the name that users will include in a URL to access the data exposed by the virtual directory, so it's important to make it descriptive. A common convention is to name virtual directories after the databases they reference. To work through the rest of the examples in the chapter, specify Northwind as the name of the new virtual directory.

Although Local Path will sometimes not be used, it's required nonetheless. In a normal ASP or HTML application, this would be the path where the source files themselves reside. In SQLISAPI applications, this folder does not necessarily need to contain anything, but it must exist nevertheless. On NTFS partitions, you must also make sure that users have at least read access to this folder to use the virtual directory. You configure which user accounts will be used to access the application (and thus will need access to the folder) in the dialog's Security page.

Click the Security tab to select the authentication mode you'd like to use. You can use a specific user account, Windows Integrated Authentication, or Basic (clear text) Authentication. Select the option that matches your usage scenario most closely; Windows Integrated Authentication will likely be the best choice for working through the examples in this chapter.

Next, click the Data Source page tab. This is where you set the server and the database that the virtual directory references. Select your SQL Server from the list and specify Northwind as the database name.

Go to the Virtual Names table and set up two virtual names, templates and schemas. Create two folders under Northwind named Templates and Schemas so that each of these virtual names can have its own local folder. Set the Type of schemas to schema and the type of templates to template. Each of these provides a navigation path from a URL to the files in its local folder. We'll use them later.

The last dialog page with which we're concerned is the Settings page. Click it, then make sure every check box on it is checked. We want to enable all of these options so that we may test them later in the chapter. The following is a brief description of each of the options on the Settings page.

Allow URL Queries

URL queries allow users to specify a complete Transact-SQL query via a URL. Special characters are replaced with placeholders, but essentially the query is sent to the server as is and its results are returned over HTTP. Because allowing URL queries permits a user to run any query he wishes against a database, this option is usually disabled on production systems. For now, enable it so that we can try it out later.

Allow Template Queries

Template queries are by far the most pervasive method of retrieving XML data from SQL Server. XML documents that store query "templates"generic parameterized queries with placeholders for parametersreside on the server and provide a controlled access to the underlying data. The results from template queries are returned over HTTP to the user.

Allow XPath

When Allow XPath is enabled, users can use a subset of the XPath language to retrieve data from SQL Server based on an annotated schema. Annotated schemas are stored on a Web server as XML documents, and map XML elements and attributes to the data in the database referenced by a virtual directory. XPath queries allow the user to specify the data defined in an annotated schema to return.

Allow POST

HTTP supports the notion of sending data to the Web server via its POST command. When Allow POST is enabled, you can post a query template (usually implemented as a hidden form field on a Web page) to a Web server via HTTP. This causes the query to be executed and returns the results back to the client.

For this to work, not only must Allow POST be enabled, but Allow URL queries must also be turned on. As I mentioned earlier, the open-endedness of this usually limits its use to private intranets. A malicious user could form up her own template and post it over HTTP to retrieve data to which she isn't supposed to have access, or, worse yet, make changes to it.

Once you've enabled these options, click the OK button to create the virtual directory.

TIP

A handy option on the Advanced tab is Disable caching of mapping schemas. Normally, mapping schemas are cached in memory the first time they're used, and are accessed from the cache thereafter. While developing a mapping schema, you'll likely want to disable this so that the schema will be reloaded each time you test it.

for RuBoard