XML and SQL 2000 (Part 3)

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.

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles