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:
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.
Launch Active Directory Users and Computers for the domain where users and server accounts are located.
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.
For the web server computer account, on the General tab, select "Trust this computer for delegation."
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.