Local IAS SQL Server logging

  • Article

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

The following scenario depicts local IAS SQL Server logging for medium-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. Queries against multiple databases are performed using SQL Query Analyzer or a third-party product with similar capabilities.

Components of local IAS SQL Server logging

Figure 2 shows the components of IAS SQL Server logging.

a628503c-b925-4166-95fa-e0ffb090be98

Figure 2: Components of local IAS SQL Server logging

This configuration consists of the following components:

IAS server running SQL Server

A computer running Windows Server 2003 and Internet Authentication Service (IAS) configured to use SQL Server logging. The IAS server is 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 the 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 that accepts and processes the XML documents sent by IAS, and then stores accounting data in SQL Server 2000 tables.

How local SQL Server logging works

Local IAS SQL Server logging, including data collection, storage, processing, and querying for data views, works in three 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. Applications query the IAS databases

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 are sent in XML format to a stored procedure in the SQL Server 2000 database on the local computer.

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

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

Figure 3 depicts a query against SQL Server databases on two IAS servers by using SQL Query Analyzer on a portable computer.

b5a8425b-f395-4881-9845-c7f9092df7df

Figure 3: SQL Query Analyzer runs a SQL query

For more information, see "SQL Server Connection Basics" in “Related Links” later in this white paper.

To deploy local IAS SQL Server logging

  1. Install SQL Server 2000 on each IAS server that you want to configure for SQL Server logging. For more information, see “Overview of Installing SQL Server 2000” in “Related Links” later in this white paper.

  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. For more information, see “Creating a database” in “Related Links” later in this white paper.

  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. For more information, see “Create Procedure” in “Related Links” later in this white paper.

  4. Configure log file properties and the data link for each IAS server. Configure the data link on each IAS server to log to the local SQL Server 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 the 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.