IAS SQL Server logging with a central database

  • Article

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 with a central database for large-sized organizations. In this scenario, SQL Server 2000 is installed on each IAS server. Each IAS server is then configured to log to the local SQL Server 2000 database. In addition, a computer or computer cluster running SQL Server 2000 is deployed as a central database. All records from IAS servers are periodically published to the central SQL Server database. Queries against the central SQL Server database are performed using SQL Query Analyzer or a third-party product with similar capabilities.

Components of IAS SQL Server logging with a central database

Figure 4 depicts the components of SQL Server logging with a central database.

e65efb8a-0705-48c4-88ee-6e48bb1451db

Figure 4: The components of IAS SQL Server logging with a central database

This configuration consists of the following components:

IAS servers running SQL Server

Two or more computers running Windows Server 2003 and Internet Authentication Service (IAS) configured to use SQL Server logging. The IAS servers are also running SQL Server 2000. In the IAS snap-in, SQL Server logging is enabled, with a data link configured between IAS and SQL Server 2000 on the local computer.

Within the SQL Server 2000 database environment on each IAS server, the following components are configured:

  • IAS database. A database configured to store IAS accounting data.

  • Stored procedure. A program that runs inside the IAS database. The stored procedure accepts and processes the XML documents sent by IAS and stores accounting data in SQL Server 2000 tables.

Central computer running SQL Server

Within the SQL Server 2000 database, the following components are configured:

  • Central IAS database. A database that stores IAS accounting data that is published by the IAS servers running SQL Server.

How IAS SQL Server logging with a central database works

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 SQL Server 2000 database

  3. IAS servers running SQL Server publish records to the central SQL Server database

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

  5. Applications query the IAS 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 then 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.

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

Data from the access server and data produced by IAS is sent in XML format to a stored procedure in the SQL Server 2000 database on the local computer. The access server is configured at the IAS server as a RADIUS client.

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

  1. The IAS server receives or produces the data to be logged to a SQL Server 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 SQL Server 2000. If the IAS server cannot establish a connection with SQL Server 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 a SQL Server 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 SQL Server 2000 is successful, the IAS server signals SQL Server 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 SQL Server 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 SQL Server 2000 database, 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 SQL Server 2000 database processes the XML document and stores the processed data in SQL Server 2000 database tables.

  7. SQL Server 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. IAS servers running SQL Server publish records to the central SQL Server database

After a time period that you specify, the IAS servers running SQL server act as publishers and replicate data to the central SQL Server database, which acts as the subscriber. SQL Server 2000 replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases for consistency. You can also configure the SQL Server database on IAS servers to delete previously published records. For more information, see “Replication Overview” in “Related Links” later in this white paper.

4. The central SQL Server database warehouses data from two 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. The central IAS database provides 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 central 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. For more information, see “Creating a Data Warehouse” in “Related Links” later in this white paper.

To deploy IAS SQL Server logging with a central database

  1. Install SQL Server 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 SQL Server 2000 database for storage of IAS accounting log data.

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

  4. Create the central IAS database. On the computer or computer cluster running SQL Server 2000 that will function as the central SQL Server, create an IAS database to hold records from all IAS servers configured to use SQL Server logging.

  5. Configure replication from the IAS servers running SQL Server to the central SQL Server database.

  6. Configure log file properties and the data link for each IAS server.

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 central computer running SQL Server, click Test Connection.

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