IIS Informant - 19 Mar 2001

I want to configure my IIS server to send log information to my Microsoft SQL Server 7.0 machine. I created a system Data Source Name (DSN) to the SQL Server machine and IIS so I could use ODBC logging, but how do I create the table structure in the database file?

ODBC logging lets you configure IIS to write logs to a database such as SQL Server. ODBC logging presents some interesting possibilities. The chief benefit is that it can act as a central store for multiple servers and sites. You can store all your Web sites' logs in one database, which makes running queries on the set of logs for the entire IIS server easy.

For example, if you've identified an intruder's IP address, you can quickly search for that address to see what mischief the intruder has been up to on any of your sites. In addition, you can run programs to monitor the log database for suspicious activity. Monitoring log files becomes more complicated when you store the logs for each Web site in various folders, each containing many files.

To centralize your log files, you can set up ODBC logging to record all IIS activity in one database. However, the setup isn't quite as simple as pointing to the SQL Server database in the logging properties. You must first properly configure the SQL Server machine by setting up authentication in SQL Server, establishing a DSN on the IIS server that connects to the SQL Server machine, and creating a SQL Server table with a specific format to receive the logging information.

To begin setting up ODBC logging, create a table in SQL Server to contain the logs. Unfortunately, IIS controls the fields and their lengths internally. You can't modify or add optional fields as you can in the World Wide Web Consortium (W3C) format, which is one of the main drawbacks of ODBC logging. Table 1 provides the SQL Server field names with their data sources and types. Fortunately, the winntsystem32inetsrv folder contains a file called logtemp.sql, which contains a query that will create the correct table.

The Microsoft article "Configuring ODBC Logging in IIS 4.0" (http://support.microsoft.com/support/kb/articles/q245/2/43.asp) provides step-by-step instructions for creating the DSN and setting up logging properties in IIS. Although this article states that the length of the IP address field is 255 characters, the script sets the field to a length of 50 characters. The script works well, and you don't need to have a field length of 255 characters for an IP address. Be advised that you need to know how to set up SQL Server before proceeding. Also, be careful regarding usernames, passwords, and field names that you create, because they're case sensitive.

Microsoft literature is replete with references to the performance penalty required for a server hosting IIS with ODBC logging enabled. In addition, logging sometimes stops if the SQL Server machine becomes temporarily unavailable. My advice is to try to achieve the same result with a different method.

SQL Server 7.0 offers several useful importing and exporting capabilities, including Data Transformation Services (DTS). With DTS, you can easily and quickly import your logs into a SQL Server database. After you define the transfer in the DTS UI, you can save the transfer in a package. You can then schedule the package to run at any time. (You can find a good article titled "Data Import/Export" about this topic at http://www.microsoft.com/technet/sql/dataimp.asp.) Each time you want to transfer your logs to SQL Server, you need to

You could easily write a script that automates this process.

This process works well for sites that have fewer than 100 Web sites and can afford to be down for a few minutes at night. However, this process doesn't work well for sites with hundreds of Web sites because startup delays will occur when you restart IIS.

I have IIS 3.0 on a domain controller (DC) hosting a Web site on the Internet. I want to migrate the Web site to IIS 5.0 in a different domain and shut down the IIS 3.0 server. What's the best approach to upgrade from IIS 3.0 and avoid upgrade and host-name resolution problems?

Several approaches exist, each with advantages and disadvantages. The exact technique you use depends on your circumstances. Keep in mind that upgrading from IIS 3.0 to IIS 5.0 is a big jump. Although such an upgrade has been tested, it hasn't received the same attention as upgrading from IIS 4.0. Upgrading directly from IIS 3.0 to IIS 5.0 is riskier than upgrading from IIS 4.0.

Before you upgrade, I recommend that you conduct experiments. First, image or mirror your IIS 3.0 system, and test a direct upgrade to Windows 2000. Pay particular attention to any Web applications you've used Active Server Pages (ASP), COM objects, or Internet Server API (ISAPI) executables to develop. Then, test the upgrade thoroughly, and be sure to use load testing to see whether new bottlenecks appear as a result of the new environment.

If the upgrade from IIS 3.0 works, you can perform the upgrade on your production IIS 3.0 server. If the test upgrade is unsuccessful, consider upgrading your system to IIS 4.0 and using the IIS Import Wizard in the Microsoft Windows 2000 Resource Kit to import your Web site structure, files, and ACLs to IIS 5.0. The IIS Import Wizard isn't a complete migration solution, so you'll have to tune the results. However, the wizard will get you to a good starting place on a clean installation of Win2K and IIS 5.0. Also note that the wizard can't migrate sites greater in size than 4GB. I recommend that you start with a clean Win2K and IIS 5.0 installation when possible to avoid migrating years of accumulated and often obsolete files, registry entries, user profiles, and the like.

Depending on the complexity of the installation, upgrades can be complex. With good planning, however, you can mitigate many problems. As far as managing the name resolution, it takes a while for the DNS network of servers to update to the new IP address.

When a searching DNS server resolves a URL to an IP address, that server typically receives the entry from the authoritative DNS server for the domain being searched and caches the entry. In this way, the searching DNS server avoids having to ask around to discover the requested domain's IP address.

The amount of time the entry lives in the DNS cache is called the Time to Live (TTL). Novice DNS administrators often make the mistake of setting a long TTL, say a week or more, thinking it best to have other DNS servers cache entries for their Web site for an extended period. A long TTL is fine until you want to change your IP address. By making the TTL a week, fully distributing your new IP address will take a week.

Consequently, in this gray zone between IP address changes, you can employ several strategies. If possible, simply have the new server respond to both IP addresses, although this solution might be challenging if the IP addresses are on different networks. As an alternative, keep both servers online and redirect the old server's Web site to the new server's IP address. If this solution doesn't work, you're more or less stuck with having both servers online until traffic ceases to the old server.

I'm hosting an intranet on IIS 5.0, and I want to implement Kerberos authentication. Users who authenticate to my Web sites aren't using Kerberos because the users can't access other network resources. What's required to use Kerberos on IIS 5.0 with multiple Web sites?

Integrated Windows authentication is confusing to many administrators. Basically, it's two authentication methods rolled under one title—Windows NT Challenge/Response authentication as implemented in NT 4.0 and IIS 4.0, and Kerberos as implemented in Win2K and IIS 5.0. Unfortunately, you can't use the UI to select the method you want to use.

Let's start with the conditions that must exist for IIS to use Kerberos. Here are the server requirements:

In addition, there are two client requirements:

If any one of these requirements isn't met, IIS uses NT Challenge/Response authentication. This list might seem formidable, but these requirements are common in an intranet scenario.

If you've met these requirements but Kerberos still isn't being used, you might have a naming problem. When you install Win2K, you must provide a name for the server. This name is NetBIOS and appears in My Network Places (Win2K) or Network Neighborhood (NT 4.0 and Windows 9x). Users use a Uniform Naming Convention (UNC) pathname such as IIS_Serverpublic_files, where IIS_Server is the NetBIOS computer name, to access the server.

The NetBIOS name isn't like a DNS name such as www.IIS_server.com. On a Win2K system, if the Web site DNS name doesn't match the computer's NetBIOS name, users can't use the DNS name and thus can't use Kerberos to access the site. This problem occurs because by default, AD registers only the NetBIOS names of computers; the DNS name, which IIS doesn't register with AD, is required for Kerberos authentication to succeed.

As you can see, the problem is that it appears to be almost impossible to implement DNS names with Kerberos. Fortunately, a Win2K resource kit tool called setspn.exe is available. Setspn stands for Set Service Principal Name (SPN). An SPN is the name of a service registered with AD. Using setspn.exe, you can enable your Web site for Kerberos by registering the DNS name as an SPN. If you have more than one Web site on your server, you need to register each DNS name to enable Kerberos.

So, why go through the trouble? Kerberos is more secure than NT Challenge/Response authentication and supports delegation, which means that when users log on to a DC, they don't have to authenticate again to any server on the network (providing they have NTFS permissions to access the requested resource).