XML and SQL 2000 (Part 3)

Friday May 23rd 2003 by Marcin Policht

In the third article of his series, Marcin Policht examines executing T-SQL statements against SQL Server 2000 via HTTP.

In the third article of this series, we will look into executing T-SQL statements against SQL Server 2000 via HTTP. This functionality is provided by SQLXML component (implemented in the form of SQLISAPI.DLL, which is included as part of SQL Server 2000 installation). The updated versions of this component are available as a separate download from the Microsoft Web Site. By installing this component on the web server, you will be able to associate its virtual directories with individual databases on the SQL server. This allows users to access the databases by using the http://webserver/virtualdirectory path, where webserver is an IP address or name of the Web server and virtualdirectory is the name assigned to the virtual directory. The management of these virtual directories is handled with the IIS Virtual Directory Management for SQL Server tool (for SQLXML version included with SQL Server 2000) or with the Configure IIS Support tool (for downloadable SQLXML 2.0 and 3.0 versions).

Both tools have similar interfaces, (both are implemented as Microsoft Management Console snap-ins), although the Configure IIS Support tool includes an option to upgrade virtual directories to the newer version of SQLXML. Despite new and attractive features available with SQLXML 3.0 (such as client side XML processing and support for SOAP 2.0), you should keep in mind that there are some compatibility issues with older versions.

Let's review the process of creating virtual directories and closely inspect each of available options. Using either tool, you can connect to the local or remote IIS installation. Once you connect to the target server, you can choose from the list of web sites installed on it. To create a new virtual directory, right click its icon in the contents pane and select New -> Virtual Directory. This will bring up the New Virtual Directory Properties dialog box, divided into six tabs:

  • General - allows you to set the virtual directory name (the one that will be used as part of the URL path when connecting via HTTP), and specify a path to the corresponding file system directory (where supporting files, such as XML templates and style sheets are stored), residing on one of the local drives. I will describe XML templates and stylesheets in more detail the next article of this series, for now, just keep in mind that an XML template stores SQL queries and XML stylesheet is used to modify the presentation of the XML document in the browser window.

  • Security - contains settings that determine the authentication mechanism used for connecting to the Web and SQL server. You have the following three options:

    • "Always log on as" uses the account name and password that you specify. This means that everyone who is allowed to connect to the virtual directory on the Web server will be accessing the Web server virtual directory and the corresponding SQL server database using the same credentials, regardless of their actual user name and password. Even though this option is convenient from a maintenance perspective, since only a single Web Server and SQL server login, as well as single database user are required, it might be not acceptable from a security and auditing point of view. Another serious limitation is the lack of granularity, since you cannot grant different levels of permissions to different users. Note, however, that this is the only option that allows you to use both SQL Server accounts (rather than relying on Windows accounts for connecting to the SQL server).

    • "Use Windows Integrated Authentication" relies on the credentials submitted by your users when logging on to Windows sessions on their local computers when connecting to the Web Site and the SQL Server. Obviously, in this case, you need to ensure that each user granted access to the target database will have permissions to access the virtual directory. Even though this option seems to be the most flexible and secure, there are some caveats of which you need to be aware.

      In the Windows environment, you can launch processes on remote computers through the mechanism called impersonation. In essence, this works by forwarding your credentials to the target computer, which are then used by the remote system to launch the process locally. In other words, the remote system actually does all the work on your behalf, after assuming your identity. This works fine, as long as the scope of the process is limited to that computer. If the process requires access to another remote system then it fails, since impersonation cannot be "reused." As you can imagine, this has important implications. Windows Integrated Authentication will work fine as long as the SQL server resides on the same system as the Web Server - which rarely is the case. If you want to take advantage of this option, then you have to resort to delegation, which permits "reusing" user credentials. However, delegation is possible only in Windows 2000 Active Directory domain operating in native mode, since it relies on Kerberos authentication. In order to take advantage of delegation, you will need to use the following steps:

      1. Note the name of the Web server on which you installed SQLXML support and determine the names of all users that will be accessing it.

      2. Launch Active Directory Users and Computers for the domain where users and server accounts are located.

      3. For each of the users account, bring up the account Properties dialog box, click on Account tab and select the "Account is trusted for delegation" checkbox.

      4. For the web server computer account, on the General tab, select "Trust this computer for delegation."

      5. Ensure that you restart the computer and have the user re-logon to the domain.

      Keep in mind that running a Web server on a member server trusted for delegation has some security implications; therefore, you might want to consider carefully whether the benefits of this configuration justify the risks. Note also that integrated authentication requires that the Web and SQL server belong to the same or trusted Windows domains, as well as that your clients log on to their computers using Windows domain accounts and use specific browser types (since support for Windows Integrated authentication is limited to Internet Explorer). This makes integrated authentication unsuitable for Internet scenarios.

    • "Use Basic Authentication (Clear Text) to SQL server account" - gives you granularity of the Windows integrated authentication, but has a couple of disadvantages. First, it requires users to type in the user name and password when accessing the Web site (which is not the case with the other two methods). In addition, the user credentials are sent in plain text, which means that they can be easily intercepted by running network capture utilities. However, by providing some type of encryption, typically in the form of a Secure Sockets Layer component installed on the Web server, you can provide secure and flexible connectivity. This is a very common solution when offering Web server access from the Internet.

  • Data Source - determines the name of the SQL server and the target database, to which virtual directory corresponds.

  • Settings - options specified on this page control the level of access to SQL server (in addition to the ones specified on the Security tab) and include the following operations:

    • "Allow sql=... or template=... URL queries" - this option refers to including T-SQL statements as part of a URL string that is used to access the virtual directory (via HTTP GET method). Even though this is the simplest and most convenient option from the implementation perspective, it is rarely used in production systems due to its security implications. Once this option is enabled, you have no control over the type of statements that users will attempt to run on the target database (of course, these statements are still subject to restrictions resulting from access rights granted to users accounts, and these, in turn, are determined by the settings on the Security tab).

    • "Allow posted updategrams" - limits the type of URL queries to data modification statements in the form of XML updategrams. I will describe this option in more detail in the next article of this series.

    • "Allow template queries" - provides a very effective way of controlling T-SQL statements, (the only option enabled by default), that can be executed by users accessing the Web server via the virtual directory. This is accomplished by including appropriately formatted SQL statements that users will be allowed to run inside an XML template file stored in this virtual directory.

    • "Allow XPath" - uses XPath-based XML mapping schema, stored as a file in the virtual directory, to determine which SQL server data will be accessible to users. XPath determines the elements and attributes in the XML document, which correspond to (or, in other words are mapped to) tables, columns, and rows in the target database. I will elaborate on this topic in my next article, but for introduction to XPath, you can refer to the previous article of this series.

    • "Allow POST" - provides more flexibility in accessing the Web server by allowing HTTP POST method for sending updategrams and templates. POST method does not have the size limitations imposed on GET and HEAD HTTP methods (which can be enabled with the first option). Note, however, that allowing POST makes the server vulnerable to some common Denial of Service exploits (this can be mitigated by using another configuration option on this page, which limits size of the POST queries). Allowing POST has also the same security implications as allowing URL queries (the first option on the Security tab, described above).

  • Virtual Names - in addition to assigning an "alias" (virtual name) to the folder representing the SQL server database (using the previously mentioned General tab of the New Virtual Directory properties), you can also assign aliases to folders and files stored in the virtual directory on the web site, as well as to binary objects stored in the target database. This will affect the URL path that will be used to access these folders, files or database objects via a browser.

  • Advanced - consists of three sections. The top one, labeled "ISAPI Location," specifies the name of the folder where the SQLISAPI DLL file is stored. If you updated the SQLXML version, you can determine the version number by checking the file name (this would be SQLIS2.DLL and SQLIS3.DLL for SQLXML version 2.0 and 3.0 respectively). The middle section "Additional user settings" allows you to append additional options to the URL string, that will be translated into a T-SQL statement submitted to the SQL server. Finally, the bottom section contains caching options. Typically you would use the default values (which keeps caching of XML mapping schemas and templates enabled), which increases the speed of query processing. Caching is typically disabled in development environments, to force reloading modified schemas and templates.

Note that the options described above are based on the IIS Virtual Directory Management for SQL Server included in the Windows 2000 server. If you installed the newer, downloadable versions of the SQLXML component, you will have additional choices (which I will be also discussing).

In this article, we covered the initial steps necessary for configuring IIS Virtual Directory for access to SQL server. Some of the options described above might be a bit unclear, but examples I present next should clarify their meaning and purpose. In particular, we will look at rules governing forming URL queries, template queries and XPath mapping schemas.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved