Microsoft KB Archive/323723

= How to Set Up IAS SQL Logging in Windows Server 2003 =

PSS ID Number: 323723

Article Last Modified on 10/9/2003

-

The information in this article applies to:


 * Microsoft Windows Server 2003, Standard Edition
 * Microsoft Windows Server 2003, Datacenter Edition
 * Microsoft Windows Server 2003, Enterprise Edition
 * Microsoft Windows Server 2003, Web Edition
 * Microsoft Windows Server 2003, 64-Bit Enterprise Edition
 * Microsoft Windows Server 2003, 64-Bit Datacenter Edition
 * Microsoft Windows Small Business Server 2003, Premium Edition
 * Microsoft Windows Small Business Server 2003, Standard Edition

-



This article was previously published under Q323723



SUMMARY
Internet Authentication Service (IAS) in Windows Server 2003 supports SQL logging. This article describes the general process IAS uses to record accounting records in the SQL database and the configuration requirements of the SQL server.



MORE INFORMATION
In earlier versions of Windows, IAS only supports accounting to text log files and named pipes. The data is recorded in either IAS format (an attribute/value format that is delimited by commas) or Open Database Connectivity (ODBC) logging format (a comma-delimited format with static columnar structure). Presently, IAS supports logging directly to a SQL database. The following steps describe how IAS logs data to a database:
 * 1) IAS opens an ODBC connection to the SQL server by using the supplied credentials.

NOTE: If you configure Windows Authentication for the ODBC credentials that are used by IAS SQL logging, the IAS server tries to authenticate the ODBC connection with the local computer account by default.
 * 1) IAS runs a stored procedure named report_event on the SQL server. At this time, IAS passes the accounting data as a parameter.
 * 2) SQL server handles the accounting record as appropriate, and then returns control back to the IAS server when the process is completed.

The stored procedure that is called by IAS on the SQL server must be named report_event. This name is not configurable. This stored procedure takes only one parameter. This parameter is an XML document that contains the accounting data to be logged.

The following text is an example of a basic stored procedure that uses OPENXML to store the data in two tables, record_main and record_data, with a unique identifier and a timestamp:

CREATE PROCEDURE dbo.report_event

  @doc ntext AS

SET NOCOUNT ON

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

DECLARE @event_id uniqueidentifier

SET @event_id = NEWID

DECLARE @record_timestamp datetime

SET @record_timestamp = GETUTCDATE

BEGIN TRANSACTION

INSERT dbo.event_main VALUES (   @event_id,   @record_timestamp   ) INSERT dbo.event_data

SELECT   @event_id, attribute_type, attribute_value FROM OPENXML(@idoc, '/Event/*')

WITH (   attribute_type varchar(64) '@mp:localname',   attribute_value nvarchar(1024) 'child::text'   ) COMMIT TRANSACTION

EXEC sp_xml_removedocument @idoc

GO

Note The following example is useful if you have an empty database to work with: USE RAS_REPORTING go if exists (select * from dbo.sysobjects where id = object_id(N'[event_data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [event_data] GO if exists (select * from dbo.sysobjects where id = object_id(N'[event_main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [event_main] GO CREATE TABLE dbo.event_main (   event_id uniqueidentifier, record_timestamp datetime ) go CREATE TABLE dbo.event_data( event_id uniqueidentifier,       attribute_type varchar(64),       attribute_value nvarchar(1024)

Keywords: kbenv kbinfo KB323723

Technology: kbSBServ2003Pre kbSBServ2003Search kbSBServ2003St kbSBServSearch kbWinServ2003Data kbWinServ2003Data64bit kbWinServ2003Data64bitSearch kbWinServ2003DataSearch kbWinServ2003Ent kbWinServ2003Ent64bit kbWinServ2003Ent64bitSearch kbWinServ2003EntSearch kbWinServ2003Search kbWinServ2003St kbWinServ2003Web

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© 2004 Microsoft Corporation. All rights reserved.