Partager via

Key concepts for 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 sections describe key logging concepts such as requests logged by IAS, IAS log file contents, and how IAS creates an XML document from accounting and authentication data.

Data logged by IAS

By default, IAS does not log any data until you configure it to do so. When you configure SQL Server logging, all required attributes, accounting, and authentication data that is normally logged in either IAS or database-compatible format is logged to the SQL Server 2000 or MSDE 2000 database.

Initially, it is recommended that you enable the logging of accounting and user authentication requests. You can refine your logging settings after you determine your required data.

Requests logged by IAS

You can log the following information in a SQL Server database:

  • Accounting-on requests, which are sent by the access server to indicate that it is online and ready to accept connections.

  • Accounting-off requests, which are sent by the access server to indicate that it is going offline.

  • Accounting-start requests, which are sent by the access server (after the user is authenticated and authorized by the IAS server) to indicate the start of a user session.

  • Accounting-stop requests, which are sent by the access server to indicate the end of a user session.

  • Accounting interim requests, which are sent periodically by some access servers during a user session, and which can be logged by IAS. This type of request can be used when the Acct-Interim-Interval RADIUS attribute is configured to support periodic requests in the remote access profile on the IAS server. The access server must support the use of accounting interim requests if you want the interim requests to be logged on the IAS server. If the access server does not send accounting interim requests, they are not logged.

  • Authentication requests, which are sent by the access server on behalf of the connecting user. These entries in the log contain only incoming attributes.

  • Authentication accepts and rejects, which are sent by IAS to the access server, indicating whether the user should be accepted or rejected. These entries contain only outgoing attributes.

IAS log file contents

Unlike database-import log files, which use a fixed sequence of attributes, the sequence of the attributes in a SQL Server 2000 or MSDE 2000 log file depends upon the sequence used by the access server. For more information about the sequence of these records, see the documentation for your access server.

For lists of attributes that are logged and are not logged in the MSDE 2000 and SQL Server databases, see “Interpreting IAS-formatted log files” in “Related Links” later in this white paper.

For information about vendor-specific attributes (VSAs), see “Interpreting IAS IDs for vendor-specific attributes” in “Related Links” later in this white paper.

Required database fields for session correlation

When you create the fields in the tables of your MSDE 2000 and SQL Server databases, you must provide all fields that allow applications to query the database, correlate related fields, and return a cohesive view of each session in the query results. This is called session correlation. At a minimum, to provide session correlation, you must log the following IAS accounting data in the MSDE 2000 and SQL Server 2000 databases:

  • NAS-IP-Address

  • NAS-Identifier (You need both NAS-IP-Address and NAS-Identifier because the network access server, or NAS, can send one or the other.)

  • Class

  • Acct-Session-Id

  • Acct-Multi-Session-Id

  • Packet-Type

  • Acct-Status-Type

  • Acct-Interim-Interval

  • NAS-Port

  • Event-Timestamp

Configuring accounting to provide the best session correlation

When you use an application to query your SQL Server 2000 database, it is important that sufficient data is logged to provide the application with the ability to correlate related fields and information into a cohesive view of any particular user session. To provide the best session correlation, take the following actions:

  • Use NASs that send the Class attribute in all accounting-requests. The Class attribute is sent to the RADIUS client in an Access-Accept message, and is useful for correlating Accounting-Request messages with authentication sessions.

  • If the Class attribute is sent by the NAS in the accounting request messages, it can be used to match the accounting and authentication records. The combination of the attributes Unique-Serial-Number, Service-Reboot-Time, and Server-Address must be a unique identification for each authentication that the server accepts.

  • Use NASs that support interim accounting.

  • Use NASs that send Accounting-on and Accounting-off messages.

  • Use NASs that support the storing and forwarding of accounting data. NASs that support this feature can store accounting data in circumstances when it cannot communicate with the IAS server. When the IAS server is available, the NAS forwards the stored records to the IAS server, providing increased reliability in accounting over NASs that do not provide this feature.

  • Always configure the Acct-Interim-Interval attribute in remote access policies. The Acct-Interim-Interval attribute sets the interval (in seconds) between each interim update that the NAS sends. You can add the Acct-Interim-Interval attribute on the Advanced tab on the profile settings of the remote access policy. According to RFC 2869, the value of the Acct-Interim-Interval attribute must not be smaller than 60 seconds, or one minute, and should not be smaller than 600 seconds, or 10 minutes. For more information, see RFC 2869, "RADIUS Extensions."

  • Ensure that logging of periodic status is enabled on your IAS servers.

How IAS creates an XML document from accounting and authentication data

If you select SQL Server logging in the IAS snap-in, attribute-value pairs are converted to XML format.

When IAS creates an XML document from attributes, accounting, and authentication data, the XML document includes the attribute ID or name, the attribute value, and the data type of the attribute value. With SQL Server logging, there are five data types for attribute values:

  • Non-negative integers (data_type=0)

  • Strings (data_type=1)

  • Hexadecimal numbers (data_type=2)

  • IPv4 addresses (data_type=3)

  • Date and time (data_type=4)

The following example XML document created by IAS includes the attribute name (User-Name), the attribute value (DOMAIN\username), and the data type (1), indicating a value data type of string:

<Event>
    <User-Name data_type="1">DOMAIN\username</User-Name>
</Event>

The next example XML document created by IAS includes the attribute name (NAS-IP-Address), the attribute value (192.168.0.1), and the data type (3), indicating a value data type of IP address:

<Event>
    <NAS-IP-Address data_type="3">192.168.0.1</NAS-IP-Address>
</Event>

The next example XML document created by IAS includes the attribute name (Provider-Type), the attribute value (1), and the data type (0), indicating a value data type of non-negative integers:

<Event>
    <Provider-Type data_type="0">1</Provider-Type>
</Event>

The last example XML document, which is created by IAS, includes the three previous examples combined into one XML document:

<Event>
    <User-Name data_type="1">DOMAIN\username</User-Name>
    <NAS-IP-Address data_type="3">192.168.0.1</NAS-IP-Address>
    <Provider-Type data_type="0">1</Provider-Type>
</Event>

Following is an example of a typical XML document sent by IAS configured for SQL Server logging:

    <Event>
<Computer-Name data_type="1">MYNAS</Computer-Name>
<Event-Source data_type="1">IAS</Event-Source>
<Acct-Session-Id data_type="1">10</Acct-Session-Id>
<NAS-IP-Address data_type="3">10.10.1.1</NAS-IP-Address>
<Service-Type data_type="0">2</Service-Type>
<Framed-Protocol data_type="0">1</Framed-Protocol>
<NAS-Port data_type="0">7</NAS-Port>
<NAS-Port-Type data_type="0">5</NAS-Port-Type>
<Tunnel-Type data_type="0">1</Tunnel-Type>
<Tunnel-Medium-Type data_type="0">1</Tunnel-Medium-Type>
<Calling-Station-Id data_type="1">10.10.1.2</Calling-Station-Id>
<Tunnel-Client-Endpt data_type="1">10.10.1.2</Tunnel-Client-Endpt>
<User-Name data_type="1">MYDOMAIN\Administrator</User-Name>
<Client-IP-Address data_type="3">10.10.1.1</Client-IP-Address>
<Client-Vendor data_type="0">0</Client-Vendor>
<Client-Friendly-Name data_type="1">MYNAS</Client-Friendly-Name>
<MS-RAS-Vendor data_type="0">311</MS-RAS-Vendor>
<MS-RAS-Version data_type="1">MSRASV5.20</MS-RAS-Version>
<MS-RAS-Client-Version data_type="1">MSRASV5.20</MS-RAS-Client-Version>
<MS-RAS-Client-Name data_type="1">MSRAS-0-MYCLIENT</MS-RAS-Client-Name>
<Provider-Type data_type="0">1</Provider-Type>
<Class data_type="1">311 1 192.168.0.123 02/20/2003 19:03:02 9</Class>
<SAM-Account-Name data_type="1">MYDOMAIN\Administrator</SAM-Account-Name>
<Fully-Qualified-User-Name data_type="1">MYDOMAIN\Administrator</Fully-Qualified-User-Name>
<Authentication-Type data_type="0">4</Authentication-Type>
<Packet-Type data_type="0">1</Packet-Type>
<Reason-Code data_type="0">0</Reason-Code>
    </Event>

Example stored procedure

Following is an example stored procedure that creates an IAS database within the SQL Server 2000 database environment and processes XML documents sent to the computer running SQL Server by IAS servers configured to log to this specific computer running SQL Server. If you want to use this stored procedure in a test environment, you can copy the text below into Notepad, then save the document with the file name extension *.sql. Import the stored procedure into a database in SQL Server 2000, and run the stored procedure.

Note

This stored procedure is provided solely as an example, and is unsupported.

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'IASODBC')
    DROP DATABASE [IASODBC]
GO

CREATE DATABASE [IASODBC]  ON (NAME = N'IASODBC_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\IASODBC_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'IASODBC_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\IASODBC_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'IASODBC', N'autoclose', N'false'
GO

exec sp_dboption N'IASODBC', N'bulkcopy', N'false'
GO

exec sp_dboption N'IASODBC', N'trunc. log', N'false'
GO

exec sp_dboption N'IASODBC', N'torn page detection', N'true'
GO

exec sp_dboption N'IASODBC', N'read only', N'false'
GO

exec sp_dboption N'IASODBC', N'dbo use', N'false'
GO

exec sp_dboption N'IASODBC', N'single', N'false'
GO

exec sp_dboption N'IASODBC', N'autoshrink', N'false'
GO

exec sp_dboption N'IASODBC', N'ANSI null default', N'false'
GO

exec sp_dboption N'IASODBC', N'recursive triggers', N'false'
GO

exec sp_dboption N'IASODBC', N'ANSI nulls', N'false'
GO

exec sp_dboption N'IASODBC', N'concat null yields null', N'false'
GO

exec sp_dboption N'IASODBC', N'cursor close on commit', N'false'
GO

exec sp_dboption N'IASODBC', N'default to local cursor', N'false'
GO

exec sp_dboption N'IASODBC', N'quoted identifier', N'false'
GO

exec sp_dboption N'IASODBC', N'ANSI warnings', N'false'
GO

exec sp_dboption N'IASODBC', N'auto create statistics', N'true'
GO

exec sp_dboption N'IASODBC', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
    exec sp_dboption N'IASODBC', N'db chaining', N'false'
GO

use [IASODBC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[report_event]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[report_event]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[accounting_data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[accounting_data]
GO

if exists (select * from dbo.systypes where name = N'ipaddress')
exec sp_droptype N'ipaddress'
GO

setuser
GO

EXEC sp_addtype N'ipaddress', N'nvarchar (15)', N'not null'
GO

setuser
GO

CREATE TABLE [dbo].[accounting_data] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [timestamp] [datetime] NOT NULL ,
    [Computer_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Packet_Type] [int] NOT NULL ,
    [User_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [F_Q_User_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Called_Station_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Calling_Station_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Callback_Number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Framed_IP_Address] [ipaddress] NULL ,
    [NAS_Identifier] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NAS_IP_Address] [ipaddress] NULL ,
    [NAS_Port] [int] NULL ,
    [Client_Vendor] [int] NULL ,
    [Client_IP_Address] [ipaddress] NULL ,
    [Client_Friendly_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Event_Timestamp] [datetime] NULL ,
    [Port_Limit] [int] NULL ,
    [NAS_Port_Type] [int] NULL ,
    [Connect_Info] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Framed_Protocol] [int] NULL ,
    [Service_Type] [int] NULL ,
    [Authentication_Type] [int] NULL ,
    [NP_Policy_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reason_Code] [int] NULL ,
    [Class] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Session_Timeout] [int] NULL ,
    [Idle_Timeout] [int] NULL ,
    [Termination_Action] [int] NULL ,
    [EAP_Friendly_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Acct_Status_Type] [int] NULL ,
    [Acct_Delay_Time] [int] NULL ,
    [Acct_Input_Octets] [int] NULL ,
    [Acct_Output_Octets] [int] NULL ,
    [Acct_Session_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Acct_Authentic] [int] NULL ,
    [Acct_Session_Time] [int] NULL ,
    [Acct_Input_Packets] [int] NULL ,
    [Acct_Output_Packets] [int] NULL ,
    [Acct_Terminate_Cause] [int] NULL ,
    [Acct_Multi_Session_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Acct_Link_Count] [int] NULL ,
    [Acct_Interim_Interval] [int] NULL ,
    [Tunnel_Type] [int] NULL ,
    [Tunnel_Medium_Type] [int] NULL ,
    [Tunnel_Client_Endpoint] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Tunnel_Server_Endpoint] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Acct_Tunnel_Connection] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Tunnel_Pvt_Group_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Tunnel_Assignment_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Tunnel_Preference] [int] NULL ,
    [MS_Acct_Auth_Type] [int] NULL ,
    [MS_Acct_EAP_Type] [int] NULL ,
    [MS_RAS_Version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MS_RAS_Vendor] [int] NULL ,
    [MS_CHAP_Error] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MS_CHAP_Domain] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MS_MPPE_Encryption_Types] [int] NULL ,
    [MS_MPPE_Encryption_Policy] [int] NULL ,
    [Proxy_Policy_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Provider_Type] [int] NULL ,
    [Provider_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Remote_Server_Address] [ipaddress] NULL ,
    [MS_RAS_Client_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MS_RAS_Client_Version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.report_event
    @doc ntext
AS

SET NOCOUNT ON

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

/*
    All RADIUS attributes written to the ODBC format logfile are declared here.  One additional attribute is added: @record_timestamp.
    The value of @record_timestamp is the UTC time the record was inserted in the database.

    Refer to IAS ODBC Formatted Log Files in Online Help for information on interpreting these values.
*/

DECLARE @record_timestamp datetime

SET @record_timestamp = GETUTCDATE()

INSERT accounting_data
SELECT
    @record_timestamp,
    Computer_Name,
    Packet_Type,
    [User_Name],
    F_Q_User_Name,
    Called_Station_Id,
    Calling_Station_Id,
    Callback_Number,
    Framed_IP_Address,
    NAS_Identifier,
    NAS_IP_Address,
    NAS_Port,
    Client_Vendor,
    Client_IP_Address,
    Client_Friendly_Name,
    Event_Timestamp,
    Port_Limit,
    NAS_Port_Type,
    Connect_Info,
    Framed_Protocol,
    Service_Type,
    Authentication_Type,
    NP_Policy_Name,
    Reason_Code,
    Class,
    Session_Timeout,
    Idle_Timeout,
    Termination_Action,
    EAP_Friendly_Name,
    Acct_Status_Type,
    Acct_Delay_Time,
    Acct_Input_Octets,
    Acct_Output_Octets,
    Acct_Session_Id,
    Acct_Authentic,
    Acct_Session_Time,
    Acct_Input_Packets,
    Acct_Output_Packets,
    Acct_Terminate_Cause,
    Acct_Multi_Session_Id,
    Acct_Link_Count,
    Acct_Interim_Interval,
    Tunnel_Type,
    Tunnel_Medium_Type,
    Tunnel_Client_Endpoint,
    Tunnel_Server_Endpoint,
    Acct_Tunnel_Connection,
    Tunnel_Pvt_Group_Id,
    Tunnel_Assignment_Id,
    Tunnel_Preference,
    MS_Acct_Auth_Type,
    MS_Acct_EAP_Type,
    MS_RAS_Version,
    MS_RAS_Vendor,
    MS_CHAP_Error,
    MS_CHAP_Domain,
    MS_MPPE_Encryption_Types,
    MS_MPPE_Encryption_Policy,
    Proxy_Policy_Name,
    Provider_Type,
    Provider_Name,
    Remote_Server_Address,
    MS_RAS_Client_Name,
    MS_RAS_Client_Version
FROM OPENXML(@idoc, '/Event')
WITH (
    Computer_Name nvarchar(255) './Computer-Name',
    Packet_Type int './Packet-Type',
    [User_Name] nvarchar(255) './User-Name',
    F_Q_User_Name nvarchar(255) './Fully-Qualifed-User-Name',
    Called_Station_Id nvarchar(255) './Called-Station-Id',
    Calling_Station_Id nvarchar(255) './Calling-Station-Id',
    Callback_Number nvarchar(255) './Callback-Number',
    Framed_IP_Address nvarchar(15) './Framed-IP-Address',
    NAS_Identifier nvarchar(255) './NAS-Identifier',
    NAS_IP_Address nvarchar(15) './NAS-IP-Address',
    NAS_Port int './NAS-Port',
    Client_Vendor int './Client-Vendor',
    Client_IP_Address nvarchar(15) './Client-IP-Address',
    Client_Friendly_Name nvarchar(255) './Client-Friendly-Name',
    Event_Timestamp datetime './Event-Timestamp',
    Port_Limit int './Port-Limit',
    NAS_Port_Type int './NAS-Port-Type',
    Connect_Info nvarchar(255) './Connect-Info',
    Framed_Protocol int './Framed-Protocol',
    Service_Type int './Service-Type',
    Authentication_Type int './Authentication-Type',
    NP_Policy_Name nvarchar(255) './NP-Policy-Name',
    Reason_Code int './Reason-Code',
    Class nvarchar(255) './Class',
    Session_Timeout int './Session-Timeout',
    Idle_Timeout int './Idle-Timeout',
    Termination_Action int './Termination-Action',
    EAP_Friendly_Name nvarchar(255) './EAP-Friendly-Name',
    Acct_Status_Type int './Acct-Status-Type',
    Acct_Delay_Time int './Acct-Delay-Time',
    Acct_Input_Octets int './Acct-Input-Octets',
    Acct_Output_Octets int './Acct-Output-Octets',
    Acct_Session_Id nvarchar(255) './Acct-Session-Id',
    Acct_Authentic int './Acct-Authentic',
    Acct_Session_Time int './Acct-Session-Time',
    Acct_Input_Packets int './Acct-Input-Packets',
    Acct_Output_Packets int './Acct-Output-Packets',
    Acct_Terminate_Cause int './Acct-Terminate-Cause',
    Acct_Multi_Session_Id nvarchar(255) './Acct-Multi-Session-Id',
    Acct_Link_Count int './Acct-Link-Count',
    Acct_Interim_Interval int './Acct-Interim-Interval',
    Tunnel_Type int './Tunnel-Type',
    Tunnel_Medium_Type int './Tunnel-Medium-Type',
    Tunnel_Client_Endpoint nvarchar(255) './Tunnel-Client-Endpt',
    Tunnel_Server_Endpoint nvarchar(255) './Tunnel-Server-Endpt',
    Acct_Tunnel_Connection nvarchar(255) './Acct-Tunnel-Connection',
    Tunnel_Pvt_Group_Id nvarchar(255) './Tunnel-Pvt-Group-Id',
    Tunnel_Assignment_Id nvarchar(255) './Tunnel-Assignment-Id',
    Tunnel_Preference int './Tunnel-Preference',
    MS_Acct_Auth_Type int './MS-Acct-Auth-Type',
    MS_Acct_EAP_Type int './MS-Acct-EAP-Type',
    MS_RAS_Version nvarchar(255) './MS-RAS-Version',
    MS_RAS_Vendor int './MS-RAS-Vendor',
    MS_CHAP_Error nvarchar(255) './MS-CHAP-Error',
    MS_CHAP_Domain nvarchar(255) './MS-CHAP-Domain',
    MS_MPPE_Encryption_Types int './MS-MPPE-Encryption-Types',
    MS_MPPE_Encryption_Policy int './MS-MPPE-Encryption-Policy',
    Proxy_Policy_Name nvarchar(255) './Proxy-Policy-Name',
    Provider_Type int './Provider-Type',
    Provider_Name nvarchar(255) './Provider-Name',
    Remote_Server_Address nvarchar(15) './Remote-Server-Address',
    MS_RAS_Client_Name nvarchar(255) './MS-RAS-Client-Name',
    MS_RAS_Client_Version nvarchar(255) './MS-RAS-Client-Version'
    )

EXEC sp_xml_removedocument @idoc

SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

IAS SQL Server logging scenarios

The following three deployment scenarios provide SQL Server logging solutions tuned to organizations with specific logging requirements:

  • Local IAS SQL Server logging. This scenario depicts IAS servers that log to a local SQL Server 2000 database. Distributed queries can be run against databases on multiple IAS servers by using SQL Query Analyzer or a third-party product. This scenario is optimal for medium-sized organizations that need local collection of accounting data for reporting and billing.

  • IAS SQL Server logging with a central database. This scenario depicts IAS servers that log to a local SQL Server 2000 database. SQL Server 2000 replication is deployed to publish records from the SQL Server database on each IAS server to a central SQL Server 2000 database. This scenario is optimal for large-sized organizations that need centralized collection of accounting data for reporting and billing.

  • IAS SQL Server logging using MSDE 2000. This scenario depicts IAS servers that log to a local MSDE 2000 database. A custom accounting-forwarder component installed on each IAS server manages the local databases and then publishes records to a central SQL Server 2000 database. This scenario is optimal for enterprises.

In the sets of instructions that follow, these assumptions are made:

  1. Your organization has already deployed Windows Server 2003 Internet Authentication Service on your network, and your network access solution is functioning properly with another form of logging (either IAS format or database-compatible format). If you have not already deployed IAS on your network, see “Deploying IAS as a RADIUS server” and “Deploying IAS as a RADIUS proxy” in Help and Support Center for Windows Server 2003.

  2. You have SQL Server 2000 relational database development experience and you understand how to use SQL Server 2000 to create, modify, administer, and manage SQL Server 2000 databases. For more information, see “Related Links” later in this white paper.

  3. For the SQL Server logging using MSDE 2000 scenario, you have software development experience that allows you to create the custom accounting-forwarder component that is installed and run on each IAS server configured for SQL Server logging.

Deployment in stages

Before you deploy any of the three scenarios depicted in this white paper, it is recommended that you first deploy the scenario in a test lab environment.

After your test lab solution is functioning properly and you are prepared to deploy SQL Server logging in a production environment, you can deploy SQL Server logging in stages, as follows:

Stage 1: Use a text file format for logging. Configure IAS accounting to use either IAS format or database-compatible format and then verify that it is functioning properly.

Stage 2: Use both a text file format and SQL Server logging. With all instances of SQL Server 2000 or MSDE 2000 configured and in place in the production environment, enable SQL Server logging by using the IAS snap-in of each IAS server without disabling logging in IAS format or database-compatible format. Log in both formats for a duration that allows you to verify that your SQL Server logging solution is functioning properly. In this circumstance, if either SQL Server logging or text logging fails, IAS will halt processing of network connection attempts, and service to users will be interrupted.

Stage 3: Use SQL Server logging format. Disable text file logging after you have verified that your SQL Server logging solution is functioning properly.