XML and the SQL 2000 Server, Part 1 - Configuring the IIS Virtual Directory Management for SQL 2000 Server:
(Page 4 of 4 )
By installing SQL 2000 Server, XML support by IIS is not automatic. You need to use the IIS Virtual Directory Management for SQL Server utility to enable this support. This utility is available as a shortcut when SQL 2000 Server is installed as shown in this picture.The IIS virtual directory defines a connection between IIS and an instance of SQL Server 2000. The virtual directory is displayed as an MMC [Microsoft Management Console] snap-in. However, when SQLXML3.0 is added, an enhanced snap-in is made available. In this tutorial, only the out of the box SQL 2000 Server's version of the virtual directory configuraion is described.
Double clicking the Configure SQL XML Support in IIS shortcut brings up the IIS Virtual Directory Management for SQL server in the MMC console, as shown for this IIS Web server called Nechost. Nechost is the name of the IIS on this machine. By clicking the + symbol, you can expand this node to display the default website as shown in the picture that follows the next.
In the following steps the above default virtual site will be configured to provide XML support. Right click this default web site and choose to create a New -> virtual directory by choosing the drop-down option as shown in this picture.
There are several tabs on this New Virtual Directory Properties window. The General tab is the one that sets up the name of the virtual directory and also the full path to the actual directory that contains the files of the virtual directory. In the next picture both these folders have the same name XMLEnabled
The next tab is the one that configures the Security. This is the authentication needed for the SQL XML ISAPI DLL. The SQLVDIR object is configured by a wizard which steps thru this process. There are three choices for the authentication:
Anonymous Access
Enabling the radio button Always Log on as sets up the Anonymous user access. When this is enabled, the user gets access to the resource unless it is denied by the NTFS permissions. In this case, IIS provides stored credentials to the Windows OS using a special user account IUSR_ machine name and, by default, IIS controls the password.
In the present case the annonymous user is IUSR_Nechost. This should automatically come up when the radio button is enabled and the Windows credentials radio button is enabled. enable Windows account synchronization will also automatically get checked. This allows the IIS to synchronize the annonymous password (password field is grey).
The other option with Always log on as with the radio button SQL Server will look for the built-in internet guest account, IUSR_Machinename. In this case it is only necessary to provide the anonymous user name.This authentication (Annonymous log on by either mode) is optimum for Internet applications and, provides the best performance with no overheads.
Basic Authentication
When this mode is selected, the other two options will be greyed out. The authentication is now sought on the SQL Server account database. This has implications when you go to the next tab Data Source. The databases that can be accessed are limited to the permissions given to the user noted in basic authentication. Since the password is transmitted by HTTP as Base64 encoded data (basically clear text) this mode is insecure. It is mostly for intranet applications. This can be made more secure using SSL.
Integrated Authentication
Users in the domain, or trusted domains are allowed access. This is the best method for intranet users. The Windows users must have login accounts in the SQL Server.
The next tab is the Data Source. Here the browse button can be used to choose the appropriate server from the server group, or an appropriate server instance. You need to pick the default database to use from among the databases for which the credentials have permissions. At this point you may be asked to provide User Name and Password depending on the security mode (Basic) selected. In this example pubs database was chosen to continue with this tutorial.
The next tab specifies the SQL 2000 server access through the IIS. Allow template queries is enabled by default. URL queries may be allowed by checking the Allow URL queries option, but they are not safe and therefore are not recommended. They are useful in developing SQL requests before preparing a template file. As it passes anything that can go into an URL, they can also modify the database, drop tables, and so forth. Allow XPath allows users to execute XPath queries over SQL Views. Allow Post allows posting of data by the user (HTTP GET & POST), this is disabled by default. In this example, all options are chosen. In some versions of SQL Server another option, Allow posted updategrams may also be available, and also the default choices could be different as well.
The next tab, Virtual Names, that specifies a name as a part of the URL to execute, is configured as follows:
When this window comes up, there are no default choices. Click on New which pops up a window where the following choices can be made.
- Database object, dbObject. No path information is needed for this option. This allows specifying a database table or view at the URL.
- Xpath query against a mapping schema by choosing schema. These queries can return results in native SQL Server format.
- SQL queries in template files by choosing, template. Templates can be created to execute both SQLl queries as well as XPath queries. XSL can be used to transform the query results.
The virtual names hide the actual directories where information is stored, thus offering more security. The choices made appear as shown here. The path usually picks up the virtual directory chosen in the general tab.
The last one is the Advanced tab. The location of the sqlisapi.dll is automatially retrieved. In case it does not, or should you like to test a new version, you should browse to the location. The default directory in Windows 2000 Professional is C:\Program Files\Common Files\System\Oledb\sqlisapi.dll. This completes the configuration of the virtual directory. In order to force loading of modified documents, it is best to check the "cache" option.
The next picture shows three different virtual directories on the Nechhost server each with a different set of choices.
Summary In this first part, two main items were considered. Firstly, the language enhancements to the T-SQL available in and out of the box installation of SQL 2000 Server was discussed. The powerful for XML clause in its various modes to retrieve data in XML format from the SQL 2000 Server, and the open XML clause together with the two new stored procedures for the uploading of XML document to a table structure were discussed. Secondly, the enabling of a SQL 2000 server specific virtual directory was discussed in detail. These are two of the required items to converse with the SQL 2000 Server in XML over the Internet. The next part will focus on the various ways such a data based information transfer takes place across the Internet.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
