HTTP Access to SQL Server 2000
With SQL Server 2000, you can access your data from anywhere on the Internet by using the HTTP protocol. This is especially useful when you need to cross firewalls, because the HTTP protocol uses port 80, which does not present any problem when working through firewalls.
SQL Server can produce static HTML pages with the Web Assistant Wizard. You can use this wizard to refresh the HTML page whenever the underlying data changes. However, this way of publishing SQL Server data on the Internet is not very flexible, because it is limited to specific reports.
You can use Internet Information Server with the SQL Internet Server Application Programming Interface (ISAPI) filter to send queries to SQL Server. IIS sends the results back to the client over HTTP.
The process, simplified, works as follows:
The client connects to a special virtual directory in IIS defined for SQL Server access, specifying a query to execute in the HTTP request.
The SQLISAPI filter sends the request to SQL Server through OLE DB using the FOR XML clause
SQL Server returns the results to IIS in XML format.
IIS returns the results to the client application through HTTP.
You can send requests to SQL Server through HTTP in different ways:
URL queries, where the query is embedded in the URL call itself. This option should be used only in testing scenarios.
XML template files, where the query is defined in pre-built files, which provides a more secure access than with URL queries.
XPath queries against schemas, to validate queries containing XPath language statements.
POST method, to be used directly from any Web page.
Updategrams, to insert, delete, or modify SQL Server data by XML updategrams.
CAUTION
Posting queries or templates directly through HTTP represents security problems, because users can send any valid Transact-SQL query using this method, as long as they can connect to the virtual directory.
In a production Internet environment, it is recommended to disable POST queries, restricting the HTTP access to using template files only, because in this way you restrict the queries that users can send through HTTP.
Note
You must install XML for SQL Server Web Release to be able to use updategrams in SQL Server 2000.
You can download this upgrade from http://msdn.microsoft.com/downloads/default.asp?URL=/code/sample.asp?url=/msdn-files/027/001/554/msdncompositedoc.xml
This section teaches you how to create an IIS virtual directory for SQL Server and send queries through HTTP using URL queries and templates.
For more information about this interesting topic, you can read the section "Accessing SQL Server Using HTTP" in Books Online, and the "XML for SQL Documentation" installed with the XML for SQL Server 2000 Web Release.
Creating a SQL Server Virtual Directory for IIS
The first step to provide HTTP access to SQL Server is to configure a SQL Server virtual directory in IIS.
When you install SQL Server 2000, you can find a menu called Configure SQL XML Support in IIS. If you click on this menu, you start the Microsoft Management Console with the IIS Virtual Directory Management for SQL Server applet.
Select your server, expand its tree, and right-click on the Default Web Site node to display the context menu. On the context menu, choose New, Virtual Directory to start the IIS Virtual Directory Management Utility. Write the name of the virtual directory and the physical path, located in the General tab, and you will see something similar to Figure 16.9. To follow the examples of this section, the virtual directory is called SQLXML.
){kind=link}
Figure 16.9 The new virtual directory properties.
Click on the Security tab to specify how the users will be authenticated. You have the following options:
Always log on as, to specify a single account for all HTTP access to SQL server. Make sure you don't use an account with administrative privileges in SQL Server when using this option.
Use Windows Integrated Authentication, which uses the Microsoft Windows NT challenge/response (Windows NT 4.0) or Windows Integrated Authentication (Microsoft Windows 2000) IIS authentication method.
Use Basic Authentication (Clear Text) to SQL Server account, to prompt for a SQL Server username and password.
In this case, select the Use Windows Integrated Authentication option. You will see the situation exposed in Figure 16.10.
Figure 16.10 Specify the authentication mode to access SQL data through HTTP.
){kind=link}
Note
To provide different access security modes, you can create different virtual directories to the same server. From IIS you can manage access security to each one of these virtual directories.
Click on the Data Source tab to select the target server and the default database, as shown in Figure 16.11.
){kind=link}
Figure 16.11 Specify the target SQL Server instance and the default database.
Note
To access multiple servers, create one virtual directory for each one.
Click on the Settings tab to select the access method. In this case, select Allow URL queries, Allow XPath, and Allow template queries, as shown in Figure 16.12.
){kind=link}
Figure 16.12 Configure the access method to the SQL Server data.
To test the virtual directory you just configured, open Internet Explorer, and type the following URL in the address line:
http://localhost/sqlxml?sql=SELECT+CategoryName+
FROM+Categories+As+Category+
FOR+XML+AUTO&root=Categories
You will see what is shown in Figure 16.13.
){kind=link}
Note
In URL queries, type a + symbol instead of spaces.
Figure 16.13 Results of a URL Query through an Internet Information Server virtual directory for SQL Server.
Using SQL Server Data From HTTP
In the preceding section, you learned how to set up a virtual directory for SQL Server in Internet Information Server. You tested the virtual directory by sending a simple URL query.
You can use this virtual directory to send any kind of query to SQL Server. However, as you might imagine, this can be tedious, error-prone, and a possible security risk, because users can send any query through URL.
You can create XML template files with predefined queries. An XML template is an XML document that contains one or more SQL statements and XPath queries.
You can create your own first XML template now. Create a folder called Templates anywhere in the server hard disk. Open Notepad and create a new file called Categories.xml, stored in the physical path of the virtual directory you created in the preceding section. Write in this file the code included in Listing 16.23.
Listing 16.23—An XML Template to Show Categories
<?xml version="1.0"?> <Categories xmlns:sql='urn:schemas-microsoft-com:xml-sql'> <sql:query> SELECT CategoryName FROM Categories AS Category FOR XML AUTO </sql:query> </Categories>
Now open the IIS Virtual Directory Management for SQL Server and open the virtual directory you created in the preceding section. Go to the Virtual Names path, and create a new virtual name, called Templates, pointing to the Templates directory you just created, and specify a type template for this virtual directory. Figure 16.14 shows the Virtual Names tab after creating this virtual name.
){kind=link}
Figure 16.14 This SQL virtual directory contains a template virtual name.
Note
You don't need to create the template folder in the same directory as the virtual directory. However, if the template files are not stored in a virtual name folder of type template, IIS will not recognize them as valid XML templates.
Now you can open Internet Explorer and access the following URL:
http://localhost/sqlxml/templates/Categories.xml
You will see the familiar categories list in XML format.
In this way, you can have predefined queries saved as templates, and the users don't need to know how the queries are built.
To do something more useful, you can try to use parameters to select specific information about customers, their orders, and the products included in their orders. To do so, you can write the code included in Listing 16.24 and save it in the file CustomersOrders.XML in the Templates directory you created earlier.
Listing 16.24—An XML Template to Show Customers and Their Orders
<?xml version="1.0"?>
<Customers xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header>
<sql:param name="Customer">NULL</sql:param>
<sql:param name="Product">NULL</sql:param>
</sql:header>
<sql:query>
SELECT CompanyName,
OrderDate,
ProductName,
Quantity,
Item.
UnitPrice
FROM Customers AS Customer
JOIN Orders AS [Order]
ON [Order].CustomerID = Customer.CustomerID
JOIN [Order Details] AS Item
ON Item.OrderID = [Order].OrderID
JOIN Products AS Product
ON Product.ProductID = Item.ProductID
WHERE CompanyName LIKE
CASE @Customer
WHEN 'NULL' THEN '%'
ELSE @Customer + '%'
END
AND ProductName LIKE
CASE @Product
WHEN 'NULL' THEN '%'
ELSE @Product + '%'
END
ORDER BY CompanyName, OrderDate, ProductName
FOR XML AUTO
</sql:query>
</Customers>
Looking at the code in Listing 16.24, you can identify a new section at the beginning of the file:
<sql:header> <sql:param name="Customer">NULL</sql:param> <sql:param name="Product">NULL</sql:param> </sql:header>
That section defines the parameters to use. These parameters work in a similar way to the parameters in stored procedures or user-defined functions. In this case you declared two parameters: Customer and Product. For both parameters you declared NULL as their default value. Note that in this case it is not the SQL NULL value, but the string 'NULL', which is not considered in the same way by SQL Server.
This is the reason why the WHERE clause looks that uncommon:
WHERE CompanyName LIKE
CASE @Customer
WHEN 'NULL' THEN '%'
ELSE @Customer + '%'
END
AND ProductName LIKE
CASE @Product
WHEN 'NULL' THEN '%'
ELSE @Product + '%'
END
You can test this template by writing the following URL in Internet Explorer:
http://localhost/sqlxml/templates/CustomersOrders.xml
In this example, you didn't use the parameters, so IIS returns a big XML document, including every order made to every customer.
To reduce the scope, write the following URLs, and look at the results in Internet Explorer:
http://localhost/sqlxml/templates/CustomersOrders.xml?Customer=Alfreds http://localhost/sqlxml/templates/CustomersOrders.xml?Customer=Alf&Product=Ra http://localhost/sqlxml/templates/CustomersOrders.xml?Product=Ravioli
However, the way Listing 16.24 sends the query is not efficient. Listing 16.25 shows a longer but more efficient Transact-SQL code, because SQL Server can use indexes more efficiently, according to the parameters sent. The only difference between these two examples is that the query in Listing 16.25 splits the query into four individual queries:
Retrieve information about all orders when the user does not specify any of the parameters (Product and Customer are both NULL).
Retrieve only orders information related to the products whose name starts with the value specified in the parameter Product (Customer is NULL)
Retrieve only orders information related to the customers whose name starts with the value specified in the parameter Customer (Product is NULL)
Retrieve only orders information related to products with names that begin with the value specified in the parameter Product and customers with names that begin with the value specified in the parameter Customer.
Listing 16.25—A More Efficient XML Template to Show Customers and Their Orders
<?xml version="1.0"?>
<Customers xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header>
<sql:param name="Customer">NULL</sql:param>
<sql:param name="Product">NULL</sql:param>
</sql:header>
<sql:query>
IF @Customer = 'NULL'
IF @Product = 'NULL'
SELECT CompanyName,
OrderDate,
ProductName,
Quantity,
Item.
UnitPrice
FROM Customers AS Customer
JOIN Orders AS [Order]
ON [Order].CustomerID = Customer.CustomerID
JOIN [Order Details] AS Item
ON Item.OrderID = [Order].OrderID
JOIN Products AS Product
ON Product.ProductID = Item.ProductID
ORDER BY CompanyName, OrderDate, ProductName
FOR XML AUTO
ELSE
SELECT CompanyName,
OrderDate,
ProductName,
Quantity,
Item.
UnitPrice
FROM Customers AS Customer
JOIN Orders AS [Order]
ON [Order].CustomerID = Customer.CustomerID
JOIN [Order Details] AS Item
ON Item.OrderID = [Order].OrderID
JOIN Products AS Product
ON Product.ProductID = Item.ProductID
WHERE ProductName LIKE @Product + '%'
ORDER BY CompanyName, OrderDate, ProductName
FOR XML AUTO
ELSE
IF @Product = 'NULL'
SELECT CompanyName,
OrderDate,
ProductName,
Quantity,
Item.
UnitPrice
FROM Customers AS Customer
JOIN Orders AS [Order]
ON [Order].CustomerID = Customer.CustomerID
JOIN [Order Details] AS Item
ON Item.OrderID = [Order].OrderID
JOIN Products AS Product
ON Product.ProductID = Item.ProductID
WHERE CompanyName LIKE @Customer + '%'
ORDER BY CompanyName, OrderDate, ProductName
FOR XML AUTO
ELSE
SELECT CompanyName,
OrderDate,
ProductName,
Quantity,
Item.
UnitPrice
FROM Customers AS Customer
JOIN Orders AS [Order]
ON [Order].CustomerID = Customer.CustomerID
JOIN [Order Details] AS Item
ON Item.OrderID = [Order].OrderID
JOIN Products AS Product
ON Product.ProductID = Item.ProductID
WHERE ProductName LIKE @Product + '%'
AND CompanyName LIKE @Customer + '%'
ORDER BY CompanyName, OrderDate, ProductName
FOR XML AUTO
</sql:query>
</Customers>
As you have seen in Listing 16.25, you are not limited to a single query in a template, but a complete batch including several statements, as long as you use FOR XML in every SELECT statement.