IAS SQL Server logging using MSDE 2000

Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2

The following scenario depicts IAS SQL Server logging for the enterprise. This scenario uses both SQL Server 2000 and MSDE 2000, as well as a custom accounting-forwarder component that manages the MSDE 2000 databases installed on each IAS server.

Important

To deploy this scenario you must have software development experience. You must create the custom accounting-forwarder component described in this scenario. This component is installed and run on each IAS server configured for SQL Server logging.

Introduction to MSDE 2000

The Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is a data engine built and based on core SQL Server technology. With support for single- and dual-processor computers, MSDE 2000 is a reliable storage engine and query processor for desktop extensions of enterprise applications. The common technology base shared between SQL Server and MSDE 2000 enables developers to build applications that can scale from portable computers to multiprocessor clusters. This common technology base also allows IAS to use either a MSDE 2000 database or a database configured on SQL Server 2000 as storage for accounting data.

Designed to run in the background, supporting transactional desktop applications, MSDE 2000 does not have its own user interface (UI). Users typically interact with MSDE 2000 through the application in which it is embedded. MSDE 2000 is packaged in a self-extracting archive for ease of distribution and embedding. The only tool that is provided with MSDE 2000 is the osql utility. The executable file, Sql.exe located in the MSSQL\Binn folder, provides a default instance of MSDE 2000.

When you use MSDE 2000 in conjunction with SQL Server 2000, you can use the client tools included with SQL Server 2000, such as SQL Server Enterprise Manager and SQL  Query Analyzer, to configure and administer MSDE 2000 databases.

For more information about MSDE, see “Related Links” later in this white paper.

Components of IAS SQL Server logging using MSDE 2000

Figure 5 shows the components of IAS SQL Server logging using MSDE 2000.

e6962542-1ce6-4ff4-83b6-61a01958a344

Figure 5: Components of IAS SQL Server logging using MSDE 2000

This configuration consists of the following components:

IAS server

A computer running Windows Server 2003 and Internet Authentication Service (IAS) configured to use SQL Server logging.

To implement SQL Server logging, the following components are required on the IAS server:

  • MSDE 2000 database. MSDE 2000 installed on the computer running Windows Server 2003 and IAS. If there are multiple IAS servers configured for SQL Server logging, each server must have MSDE 2000 locally installed and configured for logging.

  • Stored procedure. A program that runs inside the MSDE 2000 database on the computer running Windows Server 2003 and IAS. The stored procedure must be named report_event. Report_event accepts and processes data from the IAS server and stores it in MSDE 2000 tables.

  • Custom accounting-forwarder component. A custom accounting-forwarder component, service, or application installed on the computer running Windows Server 2003 and IAS. The custom accounting-forwarder component periodically publishes records from the MSDE database on the IAS server to the IAS database on the computer running SQL Server. The custom component also flags local records as published, and deletes previously published records from the MSDE 2000 database during off-peak hours. If there are multiple IAS servers configured for SQL Server logging, each server must have the custom component installed to process records in the local MSDE 2000 database and forward records to the SQL Server database.

Computer running SQL Server

A computer or computer cluster running SQL Server 2000. If you have a small IAS deployment, you might need only one computer running SQL Server; however if you have a large IAS deployment in an enterprise network environment, you might need more than one computer or cluster running SQL Server.

To implement SQL Server logging, the following component is required on the computer running SQL Server:

  • IAS database. A database on the computer running SQL Server that is dedicated to storing the records from all IAS servers.

How IAS SQL Server logging works with MSDE 2000

IAS SQL Server logging, including data collection, storage, processing, and querying for data views, works in five basic stages:

  1. Accounting data is produced by a client connection attempt

  2. IAS logs accounting data to the stored procedure in the local MSDE 2000 database

  3. A custom accounting-forwarder component manages the local MSDE 2000 database

  4. SQL Server warehouses data from one or more IAS servers

  5. Applications query the IAS master database on the computer running SQL Server

1. Accounting data is produced by a client connection attempt

IAS performs centralized connection authentication, authorization, and accounting for many types of network access and router-to-router connections. When an access client attempts to connect to the network, it does so by using a network access server, such as a dial-up server, VPN server, wireless access point, or 802.1X authenticating switch. When these access servers are configured as RADIUS clients to an IAS-RADIUS server, IAS attempts to authenticate the user or computer with the Active Directory user accounts database for the domain, and IAS performs authorization (with user account properties and/or remote access policies configured on the IAS server) to discover whether the user or computer has the necessary permissions to access network resources.

If the RADIUS server is configured to provide accounting, or logging of session information, accounting data is produced that can be logged to an XML-compliant database on a computer running SQL Server 2000.

2. IAS logs accounting data to the stored procedure in the local MSDE 2000 database

Data from the access server and data produced by IAS are sent in XML format to a stored procedure called report_event in the MSDE 2000 database. The access server is configured at the IAS server as a RADIUS client.

Figure 6 depicts how RADIUS accounting data flows from an access server to the MSDE 2000 database on the IAS server.

5b031ec7-cdc2-42dd-846a-930179fbfeae

Figure 6: RADIUS accounting data flow from access server to the MSDE 2000 database

When IAS SQL Server logging logs to a local MSDE 2000 database, the following interactions occur:

  1. The IAS server receives or produces the data to be logged to a MSDE 2000 database. For example, a network access server configured as a RADIUS client to the IAS server sends the IAS server incoming attributes for an authentication request by a user or computer.

  2. The IAS server packages the data as an XML document.

  3. The IAS server initiates a connection with MSDE 2000. If the IAS server cannot establish a connection with MSDE 2000, the IAS server stops processing authentication and accounting requests and users cannot log on to the network.

    • When you configure IAS to log to an MSDE 2000 database installed on the local computer, select Use Windows NT integrated security on the IAS server Connection tab in the Data Link Properties dialog box. The IAS server then attempts to authenticate the connection by using the Local System account. For more information, see “Configure log file properties” in Help and Support Center for Windows Server 2003.
  4. If a connection with MSDE 2000 is successful, the IAS server signals MSDE 2000 to run a stored procedure named report_event. IAS then sends the accounting data (an XML document) to the report_event stored procedure in the MSDE 2000 database. The stored procedure that is called by IAS must be named report_event, or IAS SQL Server logging will fail. If the IAS server is unable to write to the MSDE 2000 database (for example, if the database maximum size limit of 2 gigabytes (GBs) is reached), IAS SQL Server logging will fail and users cannot log on.

  5. The report_event stored procedure uses only one parameter. This parameter is a Unicode string of type ntext. The Unicode string contains an XML document which in turn contains the accounting data to be logged.

  6. The report_event stored procedure in the MSDE 2000 database processes the XML document and stores the processed data in MSDE 2000 database tables.

  7. MSDE 2000 notifies the IAS server that the transaction is successful.

  8. The IAS server is free to process additional data, repeating this process as required.

3. A custom accounting-forwarder component manages the local MSDE 2000 database

For the IAS SQL Server logging solution to work, you must write a custom component that manages the MSDE 2000 database on IAS servers. Due to the 2-GB limit of MSDE 2000, records must be periodically published by a custom accounting-forwarder component from MSDE 2000 to a designated database on the computer running SQL Server. The accounting-forwarder component can be installed as a service, a scheduled task in MSDE 2000, or as an application.

The custom accounting-forwarder component has two primary functions:

  • Publish records from the MSDE 2000 database to the SQL Server database

  • Delete previously published records from the MSDE 2000 database

The custom accounting-forwarder component performs these actions to protect against possible data loss and to save system resources during peak hours. Differential backups from the MSDE 2000 database to the computer running SQL Server provide the ability to publish small amounts of records at periodic intervals. Deletion of previously published records during off-peak hours saves resources during peak hours.

You can design your custom accounting-forwarder component to perform in the following manner:

  • Connect to a SQL Server 2000 database. The component creates a data link with the computer running SQL Server and publishes records to the designated SQL Server database. The connection is opened when the custom component starts. The connection between the custom component and the computer running SQL Server is left open unless the custom component is stopped.

  • Perform differential backups to a SQL Server database. At a specific interval, the component performs a differential backup, publishing new records from the MSDE 2000 database to the computer running SQL Server. (New records means IAS accounting records that are added to the MSDE 2000 database after the previous differential backup.) You can design the component to publish new records at an interval that best suits your IAS deployment and your degree of network access traffic, however the interval must be calculated to publish records before the MSDE database reaches its 2-GB storage limit. If you have a large number of network access requests processed and logged by the IAS server, the 2-GB limit of the MSDE 2000 database will be reached much more quickly than on a network with few network access requests to the IAS server.

  • Flag published records in the MSDE 2000 database. To save hardware resources during peak hours, records published to the computer running SQL Server are not immediately deleted in the MSDE 2000 database, they are flagged for deletion during off-peak hours.

  • Delete flagged records during off-peak hours. To free up space in the MSDE 2000 database, records that are flagged as previously published are deleted during off-peak hours. You can design the component to delete flagged records at an interval that best suits your IAS deployment, however the interval must be calculated to delete flagged records before the MSDE database reaches its 2-GB storage limit.

  • Close the connection to the computer running SQL Server. The component must be capable of closing the data link connection to the computer running SQL Server when the component is stopped.

Example of the performance of a custom accounting-forwarder service

Following is an example of the performance of the custom accounting-forwarder component within the data flow process, on a network with a large volume of network access traffic. In the example, IAS-01 is an IAS server configured to use SQL Server logging with a local MSDE 2000 database. The custom accounting-forwarder component, called the Accounting Forwarder service, is also installed on IAS-01. In addition, SQL-01 is a computer running SQL Server 2000 that is configured with a database dedicated to storing records from the IAS server. The database on the computer running SQL Server is named IAS_db.

In this example, authentication, authorization and accounting for network access is provided by IAS-01. When the Accounting Forwarder service on IAS-01 is started, it creates a connection to SQL-01 using a valid user name and password. The connection is open between the service and the computer running SQL Server for this example of the recommended data flow process:

  1. IAS-01 logs accounting data to the local MSDE 2000 database.

  2. The Accounting Forwarder service on IAS-01 reads the MSDE 2000 database, seeking only new records that are not flagged as previously published.

  3. The Accounting Forwarder service on IAS-01 publishes all new, unflagged records by executing a stored procedure on SQL-01 with the data read from the MSDE 2000 database on IAS-01. The Accounting Forwarder service publishes the IAS accounting data at a 15-second interval.

  4. The stored procedure on SQL-01 processes and stores the records as they are received from the Accounting Forwarder service on IAS-01. The records are stored in the IAS_db database by the stored procedure.

  5. The Accounting Forwarder service on IAS-01 flags the records in the MSDE 2000 database as published.

  6. At the off-peak time of 2 A.M., the Accounting Forwarder service on IAS-01 deletes all flagged records from the MSDE 2000 database.

4. SQL Server warehouses data from one or more IAS servers

The IAS database on the computer running SQL Server contains all records from all IAS servers configured with the SQL Server logging components and configured to log to the computer running SQL Server, providing a comprehensive view of accounting data for network access administrators and others who need to view the IAS accounting data.

5. Applications query the IAS database on the computer running SQL Server

You can create a custom application, such as a Help Desk application, to view data stored in the SQL Server database. You can write a stand-alone application with a software development program such as Visual Basic, or you can use Access 2002, SQL Server 2000 Enterprise Manager, or SQL Query Analyzer to view the data.

To deploy IAS SQL Server logging with MSDE 2000

When you deploy IAS SQL Server logging, you can configure IAS to log directly to a computer running SQL Server 2000 or you can log to an MSDE 2000 database on the local computer.

Logging directly to a computer running SQL Server presents the following issues:

  • Heavy network traffic can slow logging performance, possibly resulting in a timeout between the IAS server and the computer running SQL Server.

  • Network hardware failure between the IAS server and the computer running SQL Server can cause IAS logging to fail. For example, if a router or switch between the IAS server and the computer running SQL Server fails, IAS logging will fail and users cannot log on.

  • SQL Server hardware or software failure can cause IAS logging to fail and prevent users from logging on.

If IAS logging stops for any reason, users attempting to authenticate against the RADIUS server or RADIUS proxy server will fail, and users will be unable to log on to the network and access network resources. For this reason, install MSDE 2000 on each IAS server used for logging, and then configure a data link between the IAS server and the local MSDE 2000 database. Because MSDE 2000 has a 2-GB database size limit, local storage of IAS logging data is temporary, and you can instead periodically publish the data to a central computer running SQL Server using a custom component, service, or application.

Figure 7 shows the recommended deployment model for IAS SQL Server logging with MSDE 2000.

689dc5b8-5863-4505-bd7e-fe5caaa18c8e

Figure 7: Deployment model for IAS SQL Server logging with MSDE 2000

To deploy IAS SQL Server logging, the basic steps are as follows:

  1. Install MSDE 2000 on each IAS server that you want to configure for SQL Server logging.

  2. By using SQL Server 2000 tools, you can create the database structure in the MSDE 2000 database for storage of IAS accounting log data.

  3. Create a stored procedure in the MSDE 2000 database called report_event that will process the data passed to it by IAS in XML format.

  4. Create a custom accounting-forwarder component, application, or service that performs the functions described earlier in this paper.

  5. On the computer running SQL Server 2000, create an IAS server database to hold records from each IAS server configured to use SQL Server logging. Create a stored procedure in the database to process the data passed to it by the custom accounting-forwarder component on each IAS server that the database serves.

  6. Install the custom accounting-forwarder component, application, or service on each IAS server.

  7. Configure each IAS server for SQL Server 2000 logging, including configuring the data you want to log and the data link between IAS and the local MSDE 2000 database.

To configure log file properties and the data link, do the following on each IAS server:

  1. Open the IAS snap-in.

  2. In the details pane, right-click SQL Server, and then click Properties.

  3. In Log the following information, select the information that you want to log:

    1. To log all accounting requests, click Accounting requests.

    2. To log authentication requests, click Authentication requests.

    3. To log periodic status, such as interim accounting requests, click Periodic status.

    4. To configure the number of concurrent sessions allowed between the IAS server and the SQL Server database, type a number in Maximum number of concurrent sessions.

    5. To configure the SQL Server data source, click Configure, and then, on the Connection tab, specify the following:

      To specify the name of the server on which the database is stored, type or select a name in Select or enter a server name.

      To specify the authentication method with which to log on to the server, click Use Windows NT integrated security. Or, click Use a specific user name and password, and then type credentials in User name and Password.

      To allow a blank password, click Blank password.

      To store the password, click Allow saving password.

      To specify which database to connect to on the computer running SQL Server, click Select the database on the server, and then select a database name from the list.

To test the connection between each IAS server and the computer running SQL Server, click Test Connection.

For more information, see “Configure log file properties” in Help and Support Center for Windows Server 2003.