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.