This chapter shows you how to
Bringing your intranet or Internet site online after installing and setting up Internet Information Server (IIS) 2.0 is only the first step toward making your new system a success. You must consider many factors in order to achieve the objective of providing accurate and timely information to your site's users. As the site administrator or Webmaster, you need up-to-date details on your site's activity and its demands on your system. This chapter concentrates on two basic areas: Activity logging and distribution of activity reports, and generating Web pages with tools that are included with IIS 2.0 or available for no-charge downloading from Microsoft's Web site.
Chapter 19, "Setting Up the Internet Information Server," describes how to set up logging for your intranet or Internet server to a text file. As you amass information on the usage of your site, log files in text format become increasingly difficult to analyze. Although you can import text log files into an Excel worksheet or Access table, appending log records directly to a database table is a simpler and less error-prone process.
Following are the basic steps to create a logging database and query the IIS 2.0 log files to find the information you want:
The following sections describe these steps in detail, or provide references to information in previous chapters that relate to these steps. Microsoft SQL Server 6.5 and Access 95's Jet 3.0 databases are used as examples, but you can use any client/server or desktop database system for which you have a 32-bit ODBC 2.5 driver. The examples assume that you have at least some familiarity with SQL Server 6.x and/or Microsoft Access.
Following are brief recommendations for creating the logging database:
Internet Information Server 2.0 requires a logging table with a specific structure corresponding to the fields of the text version of the log described in Chapter 19, "Setting Up the Internet Information Server." A single logging file includes records for all IIS 2.0 services.
Table 20.1 lists the column information for the logging table for SQL Server and Jet data types. The Integer size in the table applies only to the Jet Number data type. Null values are allowed in each column. IIS 2.0 returns Null values in the LogDate field; the LogTime field includes date and time information in string format.
Table 20.1 Table Structure for Logging Table
Column | SQL Data Type | Jet Data Type | Size |
ClientHost | Char | Text | 255 |
UserName | Char | Text | 255 |
LogDate | Char | Text | 255 |
LogTime | Char | Text | 255 |
Service | Char | Text | 255 |
Machine | Char | Text | 255 |
ServerIP | Char | Text | 255 |
ProcessingTime | Int | Number | Integer |
BytesRecvd | Int | Number | Integer |
BytesSent | Int | Number | Integer |
ServiceStatus | Int | Number | Integer |
Win32Status | Int | Number | Integer |
Operation | Int | Number | Integer |
Target | Char | Text | 255 |
Parameters | Char | Text | 255 |
Don't create indexes on the table; indexes slow appending of new records. The improved query performance delivered by indexes doesn't warrant the impact of multiple indexes on your Web site's performance.
Listing 20.1 shows the SQL Server query to create the LogTable table used in the following sections. The four lines of listing 20.1 beginning with if exists delete an existing version of the table before creating the new table.
Listing 20.1 The SQL Server Query to Create the
Logging Database
/****** Object: Table dbo.LogTable ******/ if exists (select * from sysobjects where id = object_id('dbo.LogTable') and sysstat & 0xf = 3) drop table dbo.LogTable GO CREATE TABLE LogTable ( ClientHost char (50) NULL , UserName char (50) NULL , LogDate char (12) NULL , LogTime char (21) NULL , Service char (20) NULL , Machine char (20) NULL , ServerIP char (50) NULL , ProcessingTime int NULL , BytesRecvd int NULL , BytesSent int NULL , ServiceStatus int NULL , Win32Status int NULL , Operation char (200) NULL , Target char (200) NULL , Parameters char (200) NULL ) GO
The logtemp.sql query that IIS 2.0 setup installs in \WINNT\system32\inetsrv is a simplified version of the query of listing 20.1. The logtemp.sql query creates a table named inetlog with the varchar, rather than fixed-width char, data type for text columns. Fixed-width fields provide better performance than variable-width fields at the expense of table size.
To execute the query of table 20.1, follow these steps:
Typing the makelog.sql query into SQL Enterprise Manager's query text box.
Verifying the structure of the LogTable table created by executing makelog.sql.
Verifying the structure of the LogTable table created by executing makelog.sql.
After you verify that logging of user activity succeeds, you can use SQL Server's security system to assure that only authorized persons can access the Logging database.
See "Establishing Database Permissions," (Ch 22)
You can use Access 95 to create a Jet 3.0 LogTable in an .MDB file (as described in the following section), and then use Access's Save
As/Export command on theFile menu to export the table structure to the SQL Server database. Before exporting the table, you must create an ODBC data source for the SQL Server database, as described later in the section "Creating ODBC Data Sources for SQL Server Databases."
Using a Jet 3.0 table eliminates the need to license and administer SQL Server. To create a Jet 3.0 database with a LogTable table, follow these steps:
If you've assigned a password to the Access Admin account, the new database you create has security applied. The System.mdw file that contains account information must also be located on the server. If you use a new System.mdw file, the default Admin user has an empty password, which eliminates the need for the System.mdw file to open the database. You can create a new System.mdw file with Access 95's Workgroup Administrator application.
Specifying the field attributes of a Jet 3.0 logging table.
The ODBC data source is one of the most common source of problems with logging databases, as is the Internet Database Connector (IDC) described later in the "Using the Internet Database Connector" section. Problems with ODBC data sources are common to SQL Server and other client/server or desktop databases having 32-bit ODBC drivers. Thus, it's important to understand the details of setting up ODBC data sources for access by IIS 2.0 and server applications that run with IIS 2.0.
IIS runs as a Windows NT service, which starts automatically after Windows NT loads. In most cases, the server runs with no local user logged on. In Windows NT 4.0 and Windows 95, conventional ODBC data sources are associated with a specific user profile. Thus, a conventional ODBC data source isn't accessible to IIS 2.0 or IIS server applications until the user who created the data source logs on to Windows NT Server.
To solve this problem, Microsoft added ODBC system data sources for 32-bit ODBC 2.5. An ODBC system data source is accessible as soon as Windows NT Server (and SQL Server, if used) starts. ODBC system data sources don't depend on a user logging on to the server. You use ODBC system data sources for logging and for most of the dynamic Web pages you create from information stored in databases.
To create an ODBC system database on the server, follow these initial steps:
ODBC user data sources displayed by the opening dialog of the ODBC Administrator.
The System Data Sources dialog displaying the default SQL Server ODBC system data source.
At this point, the steps to create SQL Server and Jet data sources diverge, as described in the next two sections.
To continue creating an ODBC system database for SQL server, follow these steps:
Selecting the SQL Server driver in the Add Data Source dialog.
Completing the entries in the expanded version of the ODBC SQL Server Setup dialog.
The SQLLogging data source added to the System Data Sources list.
The process for creating an ODBC system data source for a Jet database is similar to that for SQL Server, but the Access 95 (7.0) ODBC driver presents a different dialog for specifying the database. To create an ODBC system database for Jet 3.0 tables, starting from the open Add Data Source dialog, follow these steps:
Entering the DSN and Description in the ODBC Microsoft Access 7.0 Setup dialog.
Specifying the logging .mdb file in the Select Database dialog.
Specifying the Access system (workgroup) file in the Select System Database dialog.
The security settings of the Set Advanced Options dialog.
The completed ODBC Microsoft Access 7.0 Setup dialog.
Now that you've created the table and the ODBC system data source for logging information, you change the logging instructions for each service. Follow these steps to implement the changes:
Changing logging to a SQL Server ODBC system data source on the Logging page of the property sheet for a service.
If a user accesses your server by using the file: protocol, the user's actions aren't logged. The client handles this type of URL by conventional network file sharing. Be sure to test your logging database with the appropriate URL prefix for each service.
The log data can quickly become overwhelming unless you write meaningful queries for analysis. Following are some of the questions that queries against the database can answer:
The sample SQL Server query of listing 20.2 returns summary information for hits against the server, and displays the relative popularity of Web pages. Figure 20.16 shows SQL Enterprise Manager displaying the result set returned by the last three queries of listing 20.2 against entries created by two brief testing sessions.
Listing 20.2 A Sample Transact-SQL Query That
Returns Information on Usage and Web Page Popularity
SELECT 'Total hits' = COUNT(*),'Last Access' = MAX(LogTime) FROM LogTable SELECT 'Hit summary' = Count(*), 'Date' = SUBSTRING(LogTime,1,8) FROM LogTable GROUP BY SUBSTRING(LogTime,1,8) SELECT 'Time of day' = (SUBSTRING(LogTime,13,2) + ' ' + SUBSTRING(LogTime,18,2)), 'Hits' = COUNT(SUBSTRING(LogTime,10,2)) FROM LogTable GROUP BY (SUBSTRING(LogTime,13,2) + ' ' + SUBSTRING(LogTime,18,2)) SELECT 'Page' = SUBSTRING(Target,1,40), 'Hits' = COUNT(Target) FROM LogTable WHERE (CHARINDEX('HTM',target) > 0) GROUP BY Target ORDER BY 'Hits' DESC
The result set of the last three queries of listing 20.2, displayed by SQL Enterprise Manager's Query tool.
You can create queries similar to those shown in listing 20.2 for FTP and Gopher services. If you log to a Jet database, you can use Access 95's graphical query-by-design tool to create analytical queries. Access 95's built-in graphing capability also is useful for analyzing usage of your site.
If you want to distribute usage information for your site, SQL Server 6.5's Web Assistant automates the process. The SQL Web Assistant automatically creates formatted Web pages that contain query result sets formatted as tables. A single page can display the result set from one or more queries. You also can specify the frequency at which the page is updated by a SQL Server 6.5 scheduled task. If you use a scheduled task, you can add a DELETE query after the SELECT queries to maintain records only for a specified period in the logging table. You must have system administrator privileges to use the Web Assistant.
The following steps use the SQL Server Web Assistant to create a Web page, WWWPages.htm, that displays the result of the last query in listing 20.2:
Entering required information in the Login dialog of the SQL Server Web Assistant.
Specifying the query to create the content for the Web page.
It's a good idea to copy and paste a previously tested query into the Type Your Query text box to avoid typographical or syntax errors.
Specifying the refresh interval for the Web page.
Specifying file options for the Web page.
The File Options dialog lets you add URLs and descriptions, either as a single line or as rows of a table. Additional URLs are useful for linking to other pages, if your report offers multiple pages.
Setting query text and page formatting options.
If you specified Now in step 5, you can check your Web page by opening the Pagename.htm file with the file://path/Pagename.htm URL in the browser on your server, or from a client with the http://servername/Pagename.htm URL if the file is in the \InetPub\wwwroot folder. Figure 20.22 shows the page created with the values specified in the preceding steps. If you use a multiple SELECT query, each query result set appears in separate tables, separated by a bar.
The single-query report page created by using the example values of this section.
If you specified a scheduled task in step 5, Web Assistant creates an encrypted stored procedure in the logging database named Web_yymmddhhmmsscc, based on your computer's system time when creating the stored procedure. (The last two digits, cc, are hundredths of seconds.) You must take the following steps to run SQL Enterprise Manager's Managed Scheduled Tasks tool to generate a test copy of your Web activity report:
Selecting the task to execute immediately in the Manage Scheduled Tasks window of SQL Enterprise Manager.
As the use of your intranet or Internet server grows, it's useful to determine the extent to which user activity taxes your server's resources. Installation of IIS 2.0 adds to Windows NT Server's Performance Monitor (PerfMon) the counters shown in table 20.2.
Table 20.2 Counters Added by IIS 2.0 to Performance Monitor
Counter | Purpose |
Aborted Connections | Total unintended disconnects |
Bytes Received/sec | The instantaneous rate of incoming bytes from all users |
Bytes Sent/sec Rate | The instantaneous rate of outgoing bytes sent to all users |
Bytes Total/sec Rate | The total of Bytes Received/sec and Bytes Sent/sec |
CGI Requests | The accumulated number of Common Gateway Interface (CGI) requests from the Web service |
Connection Attempts | The accumulated number of connections, successful or not |
Connections/sec | The instantaneous number of requests for Web pages |
Connections in Error | The accumulated number of failed connections |
Current Anonymous Users | The instantaneous number of users who are logged on via the Internet |
Current CGI Requests | The instantaneous number of CGI operations being processed |
Current Connections | The sum of Current Anonymous Users and Current NonAnonymous Users |
Current ISAPI Extension Requests | The instantaneous number of ISAPI operations being processed |
Current NonAnonymous Users | The instantaneous number of users who are logged on via an intranet |
Files Received Total | The accumulated number of files uploaded by users |
Files Sent Total | The accumulated number of files downloaded by users |
Files Total | The sum of Files Received Total and Files Sent Total |
Get Requests | The accumulated number of GET requests received by the Web service |
Gopher Plus Requests | The accumulated number of Gopher Plus requests |
Head Requests | The accumulated number of queries to determine whether a user needs to refresh a Web document |
ISAPI Extension Requests | The accumulated number of requests for services using ISAPI, such as the Internet Data Connector |
Logon Attempts | The accumulated number of logons, successful or unsuccessful |
Maximum Anonymous Users | The peak number of Internet users |
Maximum CGI Requests | The peak number of CGI operations |
Maximum Connections | The peak number of Internet plus intranet users (Maximum Anonymous Users plus Maximum NonAnonymous Users) |
Maximum ISAPI Extension Requests | The peak number of operations that use ISAPI |
Maximum NonAnonymous Users | The peak number of intranet users |
Not Found Errors | The accumulated number of requests that resulted in an HTTP 404 error code being returned to the requester |
Other Request Methods | The accumulated number of requests, other than GET, POST, or HEAD |
Post Requests | The accumulated number of POST operations |
Total Anonymous Users | The accumulated number of Internet users |
Total NonAnonymous Users | The accumulated number of intranet users |
In table 20.2, the accumulation of totals begins at system startup.
See "Using Performance Monitor," (Ch 14)
PerfMon's graph views let you display any of the counters in the table 20.2, but most of the counters deserve only occasional checking. Figure 20.24 shows a PerfMon configuration that displays the most important instantaneous and accumulated values for a Web and FTP site. Table 20.3 lists the counters and scaling factors used to create the graph of figure 20.24. Scaling factors are not the defaults; to maintain a readable scale, for example, you must scale the Bytes/sec values by 0.001 to display kBytes/sec. If your site traffic is heavy, change the scaling factors to suit expected full-scale values.
Monitoring server activity.
Table 20.3 Performance Monitor Key Indicators
Counter | Scaling Factor | Counter Object |
FTP Current Connections | 1.0 | FTP Service |
HTTP Current Connections | 1.0 | HTTP Service |
FTP Total Bytes/Sec | 0.001 | FTP Service |
HTTP Total Bytes/Sec | 0.001 | HTTP Service |
% Usage, Page File | 0.1 | Paging File |
Not Found Errors | 1.0 | HTTP Service |
Figure 20.24 shows, in relative terms, the activity on your server at a glance. You can see very quickly the number of connections and other important parameters. If you observe the % Usage of the page file growing steadily, check the types of access being completed. If you have an ISAPI application running, the application might have a memory-management problem.
An increasing % Usage of the paging file over time indicates that more virtual memory (the paging file) is being consumed. Typically, additional resources are required to handle the load as more users connect to the server. If the % Usage of your page file is above 80 percent, increase the size of the file. If % Usage continues to rise and you're certain you don't have a resource problem with a CGI executable or an ISAPI extension, consider adding more memory to the system.
One of the attractions of intranet and Internet servers for large organizations is that information stored in organization-wide databases can be made easily available on Web pages. A standard Web browser, such as Internet Explorer 3.0, can eliminate the need for most custom decision-support and even some transaction-processing front ends to client/server database back ends. Although sending a fully formatted Web page creates more network traffic than returning query result sets to a custom database front end, you gain the following benefits with Web database techniques:
You can provide two basic types of database services with the tools included with Windows NT Server 4.0 and SQL Server 6.5:
You can provide access to databases in several ways-from using the Internet Database Connector (included with Windows NT Server 4.0) to using ISAPI to directly manipulate the database with custom applications. In this chapter, the focus is on the IDC, how it works, and how you use it to create dynamic Web pages.
The IDC uses the Internet Services API (ISAPI) and an ISAPI DLL, Httpodbc.dll, to make the connection between Web pages and ODBC data sources in order to create Web pages based on parameterized (user-defined) queries. Query parameter(s), which determine the content of the resulting Web page, originate in a Web page that includes text boxes or lists for entering query constraints. A Filename.idc file contains the query's SQL statement and parameters to access the ODBC data source. A Filename.htx file provides the template into which the query result is inserted. Figure 20.25 shows the relationship between the components of the IDC.
The server-side components required to implement the Internet Database Connector.
ISAPI is an extension to IIS 2.0 that provides services similar to CGI executables. The advantage of ISAPI over CGI is that ISAPI DLLs run in IIS 2.0's process space, rather than in the separate process space required for executable applications. Using DLLs rather than EXEs provides a substantial improvement in performance and minimizes Windows NT Server resource requirements.
The following sections are based on the three IDC-based examples supplied with IIS 2.0, which query the pubs database supplied with SQL Server. You must have SQL Server 6.x installed to try these examples, which are located in the \InetPub\wwwroot\samples\dbsamp folder.
Before you can run the three IDC examples, you must create an ODBC system data source with the data source name Web SQL, and specify pubs as the default database. (Creating an ODBC data source is the subject of the section "Setting Up ODBC System Data Sources" near the beginning of this chapter.) Figure 20.26 shows the ODBC SQL Server Setup dialog for the Web SQL data source. You must be logged on to SQL server with the default sa (system administrator) account with no password for the example to work correctly.
Creating the Web SQL ODBC system data source.
To test your data source, type servername/samples/dbsamp/dbsamp2.htm in your browser's Address text box to display the example that uses a form to provide a user-entered parameter. The parameterized query returns the names of authors and year-to-date sales amounts that exceed the entered value. Figure 20.27 shows dbsamp2.htm, which includes the reference to the IDC file, sample.idc, open in Internet Explorer 3.0.
The IIS 2.0 database example page that uses a form to pass a parameter to the Filename.idc file.
Accepting the default value, 5,000, for year-to-date royalties results in the sample2.idc page shown in figure 20.28. What appears isn't the content of the sample.idc file, but a page named sample2.idc created by the sample.htx template file. The .IDC and opening .HTM files usually-but not necessarily-share the same file name. The .IDC and .HTX files for the IDC examples are located in the \InetPub\scripts\samples folder.
The sample2.idc Web page returned with the query result set constrained by the sales amount entered in dbsamp2.htm.
You use the HTML <FORM ...> tag with the <INPUT ...> tag to create a text box into which you enter the parameter value for the query parameter specified in the .idc file. Listing 20.3 shows the HTML code of the dbsamp2.htm page that lets you enter a minimum yearly sales amount to serve as the query parameter.
Listing 20.3 The HTML Code of dbsamp2.htm That
Implements a Parameterized Query
<HTML> <HEAD><TITLE>Simple Parameterized Query</TITLE></HEAD> <BODY BACKGROUND="/samples/images/backgrnd.gif"> <BODY BGCOLOR="FFFFFF"> <TABLE> <TR> <TD><IMG SRC="/samples/images/SPACE.gif" ALIGN="top" ALT=" "></TD> <TD><A HREF="/samples/IMAGES/db_mh.map"><IMG SRC="/SAMPLES/images/db_mh.gif" ismap BORDER=0 ALIGN="top" ALT=" "></A></TD> </TR> <tr> <TD></TD> <TD> <hr> <font size=2> <CENTER> <H2>Internet Database Connector Example 2: Query Driven by a Form</H2> </CENTER> <FORM METHOD="POST" ACTION="/scripts/samples/sample2.idc"> <P> Enter year-to-date (YTD) sales amount: <INPUT NAME="sales" VALUE="5000" > <P> <INPUT TYPE="SUBMIT" VALUE="Run Query"> </FORM> </font> </td> </tr> </table> </BODY> </HTML>
The <FORM METHOD="POST" ACTION="/scripts/samples/sample2.idc"> line specifies the name and location of the .IDC file, relative to the \InetPub\wwwroot folder. The "sales" element of the Enter year-to-date (YTD) sales amount: <INPUT NAME="sales" VALUE="5000" > line is the name of the parameter, the value of which is returned by %sales% in the .IDC file and by idc.sales in the .HTX template file.
Listing 20.4 shows the content of the sample2.idc file, which has the minimum entries necessary to specify the DSN, log on to SQL Server, specify the .HTX template file to use, and provide the query to return a result set. The parameter that sets the minimum year-to-date sales is %sales%; user-supplied parameters in .IDC files are enclosed by percent signs. You can add a Password: password line after Username:, but the password is clear text and is visible to anyone with access to the folder in which the .IDC file is stored. If you use integrated security to log on to SQL Server, the Username: and Password: entries are ignored. The user ID and password of the anonymous user applies for Internet connections; for an intranet connection, the user's credentials apply. If you must supply a Password: entry to log on to SQL Server, be sure to secure access to the folder in which you store the .IDC file. Security issues are the subject of the later section "Considering User Rights and Database Security."
Listing 20.4 A Simple IDC Source File
Datasource: Web SQL Username: sa Template: sample.htx SQLStatement: +SELECT au_lname, ytd_sales + from pubs.dbo.titleview + where ytd_sales > %sales%
The + symbol in the SQL Statement line is the line-continuation character for .IDC files. Elements of SQL statements not on a single line with SQLStatement: must be preceded with the + symbol. Httpodbc.dll parses .IDC files by looking for the ASCII/ANSI carriage return (CR) code, which has a decimal value of 13.
When IIS 2.0 loads an .IDC file, IIS examines the extension and determines the application used for the source file. One of the very powerful capabilities and features of IIS is its capability to use extension resolution to determine how to handle a given request. Files with a .GIF extension, for example, are graphic images, and files with an .IDC extension are IDC source files that associate with Httpodbc.dll. Associations for IIS 2.0's Web service are set up in the following Registry tree location:
HKEY_LOCAL_MACHINE SYSTEM CurrentControlSet Services W3SVC Parameters ScriptMap
If you add a new association entry with the Registry Editor, make it of the data type REG_SZ and type the associated extension. To correctly map the association, include the period before the extension, as in .idc. For the value, specify the path and file name executed when the extension loads. Provide the path from the root to ensure that IIS can locate the application regardless of the current working folder. Figure 20.29 shows the Registry entry for the .IDC extension in the Windows NT 4.0 Registry Editor.
Windows NT 4.0's Registry Editor displaying the associated DLL for .IDC files, Httpodbc.dll.
Listing 20.5 is the HTML code for the simple template file, sample.htx, that's used by the example dbsamp1.htm and dbsamp2.htm files.
Listing 20.5 HTML Source Code to Create the Query
Result Page Returned to the User
<HTML> <HEAD><TITLE>Authors and YTD Sales</TITLE></HEAD> <BODY BACKGROUND="/samples/images/backgrnd.gif"> <BODY BGCOLOR="FFFFFF"> <TABLE> <TR> <TD><IMG SRC="/samples/images/SPACE.gif" ALIGN="top" ALT=" "></TD> <TD><A HREF="/samples/IMAGES/db_mh.map"> <IMG SRC="/SAMPLES/images/db_mh.gif" ismap BORDER=0 ALIGN="top" ALT=" "></A></TD> </TR> <tr> <TD></TD> <TD> <hr> <font size=2> <CENTER> <%if idc.sales eq ""%> <H2>Authors with sales greater than <I>5000</I></H2> <%else%> <H2>Authors with sales greater than <I><%idc.sales%></I></H2> <%endif%> <P> <TABLE BORDER> <%begindetail%> <%if CurrentRecord EQ 0 %> <caption>Query results:</caption> <TR> <TH><B>Author</B></TH><TH><B>YTD Sales<BR>(in dollars)</B></TH> </TR> <%endif%> <TR><TD><%au_lname%></TD><TD align="right">$<%ytd_sales%></TD></TR> <%enddetail%> <P> </TABLE> </center> <P> <%if CurrentRecord EQ 0 %> <I><B>Sorry, no authors had YTD sales greater than </I><%idc.sales%>.</B> <P> <%else%> <HR> <I> The web page you see here was created by merging the results of the SQL query with the template file SAMPLE.HTX. <P> The merge was done by the Microsoft Internet Database Connector and the results were returned to this web browser by the Microsoft Internet Information Server. </I> <%endif%> </font> </td> </tr> </table> </BODY> </HTML>
Sample.htx is a standard HTML document. The formatting is Spartan, and most of the HTML tags should be familiar to those of you who have experience with HTML tables. The %au_lname% and %ytd_sales% parameters in the <TR><TD><%au_lname%></TD><TD align="right">$<%ytd_sales%></TD></TR> line specify the field names of the pubs.dbo.titleview view that populate the table columns.
The page is created based on the information that is, or isn't, returned from the query. For example, the following section of code performs conditional testing, examining for an empty set. The dbsamp1.htm page doesn't return a value for the IDC parameter (idc.sales eq ""), whereas the dbsamp2.htm page returns either the default value (5000) or a user-entered value.
<%if idc.sales eq ""%> <H2>Authors with sales greater than <I>5000</I></H2> <%else%> <H2>Authors with sales greater than <I><%idc.sales%></I></H2> <%endif%>
If you don't implement database security, access via the IDC or any other Web-based application that delivers information from databases can be a wide-open door to your entire database system. Obviously, you must avoid giving any Internet or intranet user system administrator access to your databases. Potential security breaches arise from exposing your databases to users, not through security loopholes in IDC or other Web-based Internet applications. You must protect your databases against unauthorized access by hackers, either on-site or connected via the Internet. Following are basic recommendations for securing your databases for Internet and intranet connections:
See "Establishing Database Permissions," (Ch 22)
Microsoft's dbWeb 1.1 combines many of the features of SQL Server's Web Assistant and the IDC, and works with any RDBMS or desktop database for which 32-bit ODBC 2.5 is available. dbWeb eliminates the need to write .IDC and .HTX files, and handles graphic images well. dbWeb also makes it easy to create Web pages that include drop-down lists and text boxes for parameterized queries.
The dbWeb application consists of a server-side ISAPI component and the dbWeb Administrator, a Visual Basic 3.0 application that you use to create Web pages containing formatted database reports. Like most other Microsoft Internet-related applications, dbWeb 1.1 is free and downloadable as a 7.7M self-extracting setup file, dbWeb11.exe, from http://www.microsoft.com/intdev/dbweb/dbwins-f.htm. You also can download a dbWeb tutorial in Word 6.0 .doc format from http://www.microsoft.com/intdev/dbweb/.
To install dbWeb 1.1 from dbWeb11.exe, follow these steps:
Specifying the directory for dbWeb 1.1's administrative files.
Selecting for installation all the components of dbWeb 1.1 except the ODBC 2.5 components.
Specifying the directory locations for dbWeb 1.1, with the version of Internet Information Server 2.0 included with Windows NT Server 4.0.
The final two message boxes for the installation of dbWeb 1.1.
You must change the Startup parameter for the dbWeb service with Control Panel's Services tool from Manual to Automatic, if you want dbWeb to run as a Windows NT service when you run dbWeb as a production service.
If you installed IIS 2.0 using the default Internet publishing directory, \InetPub, you must modify the DBX files that dbWeb uses to specify the location of its sample documents. Follow these steps to make the changes required to run the sample applications:
Changing the directory for dbWeb HTML pages in the DBX page of the data source editing dialog.
You access the dbWeb sample pages from a networked browser with the URL http://servername/dbweb/dbwtest.htm. Scroll down to the links to Pubs Examples and Northwind Examples to try the examples (see fig. 20.35). Figure 20.36 shows the page for the Northwind Category_Products data source displaying the first of four Categories table records and part of the Products table for the Beverages category. The Products list corresponds to an Access subform. The Category Listing page is static; no user-entered criteria is accepted.
Links to the sample dbWeb data sources for the dbpubs and dbnwind databases.
The dbWeb Categories page created by the Category_Products data source.
Figure 20.37 illustrates a more complex, dynamic query page for the pubs database. You enter query constraints in text boxes with modifiers chosen from standard drop-down lists. Figure 20.38 shows the result of the query of figure 20.37. (The pubs database hasn't been updated for the 510 area code of the San Francisco East Bay region.) If you click a City link, a list of all authors in the same city appear, illustrating the drill-down capabilities of dbWeb pages.
A dbWeb query page with a user-entered search parameter.
The result set of the dbWeb query of figure 20.37.
All the sample applications are relatively simple decision-support front ends, some of which offer drill-down capabilities. One of the benefits of using dbWeb is the capability to easily create Web pages for transaction processing, such as adding orders and line items to the Orders and Order_Details table. The dbtutor.doc file describes how to design a dbWeb order-entry form. It's not likely that you'd use dbWeb to establish an electronic commerce Web site, but dbWeb is a remarkably useful tool for demonstrating the database capabilities of IIS 2.0 to members of management who may not be up-to-date on the latest Web technology.
Windows NT Server 4.0 includes a copy of Microsoft FrontPage 1.1 for IIS 2.0 with a single client license. Like dbWeb, FrontPage 1.1 has two components: a server-side FrontPage Explorer for maintaining the organization of your Web pages, and a client-side WYSIWYG Web page editor for creating HTML files. FrontPage's ease of use makes it a logical tool to create the initial set of custom pages for your Web site.
The initial retail version of FrontPage 1.1 didn't include the ISAPI extension to connect to IIS 2.0. You needed to download a beta version of the IIS 2.0 extension from the Microsoft Web site. The version of FrontPage included with Windows NT Server includes the IIS 2.0 extension, but you must install the extension manually.
The Windows NT Server 4.0 setup program doesn't offer an option to install FrontPage 1.1. To install FrontPage from the distribution CD-ROM onto your Web server, follow these steps:
Specifying the location of the FrontPage 1.1 administrative files.
Choosing between Typical and Custom installation options for FrontPage 1.1.
Selecting only the Client Software for an IIS 2.0 installation.
Selecting the program folder for the FrontPage menu.
Confirming your installation choices in the Start Copying Files dialog.
Confirming the installation location for the IIS 2.0 extensions for FrontPage 1.1.
Determining the server name and TCP/IP address the first time you start FrontPage's Server Administrator.
Selecting IIS 2.0 as the FrontPage server type.
The Server Configuration dialogs for setting the location of the server configuration file (top) and the TCP/IP port number for IIS 2.0 (bottom).
Confirming the settings for Server Type, Server Port, and Document Root for the IIS 2.0 extension.
The Server Administrator dialog after setting up the IIS 2.0 extension.
Adding a password for your user ID to enable administration of the FrontPage Web site.
The message indicating that you haven't enabled the Basic (Clear Text) authentication required by FrontPage 1.1.
The message that explains that enabling the Basic (Clear Text) authentication option may compromise the security of password transmission.
The organization of Web sites and the generation of Web content is beyond the scope of this book, but you can gain an idea of the capabilities of the FrontPage Explorer by using it to map the .HTM files supplied with IIS 2.0 and those added by any other Web applications you've installed, such as dbWeb. FrontPage automatically organizes your Web pages into a hierarchy based on their contained hyperlinks to other pages at your site, as well links to other sites. The FrontPage Editor lets you edit existing Web pages and create new pages in a graphical context.
To create a view of the sample IIS 2.0 Web pages in FrontPage Explorer and run the FrontPage Editor, follow these steps:
Displaying existing FrontPage Webs in the Open Web dialog.
Logging on to FrontPage Explorer as an administrator.
FrontPage Explorer's view of the <Root Web> with IIS 2.0 and dbWeb sample pages.
FrontPage Editor displaying a Web page from the IIS 2.0 sample site.
Displaying HTML code in FrontPage Editor's Generated format.
FrontPage provides a set of templates that you can use to learn the
basics of Web content generation, as well as to provide a starting point
for the design of production pages. The list of templates shown in figure
20.58 appears when you choose New Web from FrontPage Explorer's
File menu. Select the Learning FrontPage template if you
want to try the tutorial included in the FrontPage help file.
A list of the templates and wizards included with FrontPage 1.1.
This chapter provided an introduction to the techniques used by professional Webmasters to establish and distribute activity logging data using SQL Server 6.5 and its Web Assistant add-in. The Internet Database Connector and Microsoft dbWeb examples demonstrated that you can automatically create Web pages from existing information stored in your organization's databases. The chapter closed with instructions on how to install Microsoft FrontPage 1.1 and the required IIS 2.0 extensions, plus a brief description for getting started with FrontPage.
For content related to the subjects discussed in this chapter, see the following chapters:
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.