SQL Server database logging

  • Article

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

SQL server database logging

You can use SQL server database logging to log user authentication and accounting requests, which are received from one or more network access servers, to a centralized data source. Log data is passed from IAS to a stored procedure in a database that supports both structured query language (SQL) and extensible markup language (XML), such as one created with Microsoft SQL Server 2000. A stored procedure is a custom program, created by a SQL server database programmer, that runs within the SQL server database environment.

Logging to a relational database instead of a standard text file (that is, either IAS format or database-compatible format) has the advantage that relationships between data tables enable the flexible creation of dynamic data views by using queries and reports.

Which requests are logged

By default, no data is logged. When you configure 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 database. You can log the following information in a SQL server database:

  • Accounting requests, including the following:

    • 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 accepted 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.

  • Authentication requests, including the following:

    • 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.

  • Periodic status, which is used to obtain interim accounting requests sent by some access servers during sessions.

Accounting-interim requests, which are sent periodically by the access server during a user session, can also be logged to a SQL server database. 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.

Attributes that are recorded by SQL server

Unlike database-import log files, which use a fixed sequence of attributes, the sequence of the attributes in a SQL server log file depends upon the sequence used by the access server. For more information about the sequence of these records, see the documentation for the access server. The table in Interpreting IAS-formatted log files lists, in numerical order, RADIUS attributes that can be found in a SQL server database and configured for IAS logging. This table does not cover vendor-specific attributes (VSAs). For information about VSAs, see Interpreting IAS IDs for vendor-specific attributes. For more information about VSAs that are supported by your access server, see your access server documentation.

Attributes that are not recorded by SQL server

Although most of the attributes that are sent by an access server and supported in Windows Server 2003, Standard Edition; Windows Server 2003, Enterprise Edition; and Windows Server 2003, Datacenter Edition are logged when you specify the use of SQL server logging, some attributes are not logged because they contain sensitive information (for example, passwords) that should not be included. The table in Interpreting IAS-formatted log files lists some of the attributes that are not logged.

You can use the IAS console to specify which requests are logged. For more information, see Select requests to be logged.

You can use the IAS console to specify additional SQL server logging properties. For more information, see Configure log file properties.

Notes

  • All types of request logging are disabled by default.

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

  • You can also log accounting, authentication, and periodic status in IAS or database-compatible format. For more information, see Logging user authentication and accounting requests.

How IAS and SQL server logging works

When IAS is configured to log to a SQL server database, the following occurs:

  1. The IAS server receives or produces the data that it is configured to log to a SQL server 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 the server running SQL server. If the IAS server is unable to establish a connection with SQL server, the IAS server stops processing authentication requests and users cannot log on to the network.

    IAS connects to SQL server using password-based credentials that you can configure on the Connection tab in the Data Link Properties dialog box.

    If you select Use Windows NT integrated security, the IAS server attempts to authenticate the connection with the local computer account. If you select Use a specific name and password, you must specify the user name and password for a valid user account that you have already configured on your SQL server.

    For more information about configuring SQL server data link properties, see Configure log file properties.

  4. If a connection with the computer running SQL server is successful, the IAS server signals SQL server to run a stored procedure named report_event. IAS then sends the accounting data (an XML document) to the computer running SQL server. The stored procedure that is called by IAS must be named report_event, or logging will fail.

    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.

  5. The report_event stored procedure in the SQL server database processes the XML document and stores the processed data in SQL server database tables.

  6. SQL server notifies the IAS server that the transaction is successful.

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

How IAS creates an XML document from accounting and authentication data

If you select the SQL server log format, 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 date types for attribute values: Non-negative integers, strings (text), hexadecimal numbers, IPv4 addresses, and date and time. The following example XML document, which is created by IAS, includes the attribute name (User-Name), the attribute value (DOMAIN\username), and the data type of (1), indicating a value data type of string:

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

The next example XML document 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 includes the attribute name (Provider-Type), the attribute value (1), and the data type of (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 typical 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>

Configuring SQL server database logging

To log information to a SQL server database, you must configure the SQL server data link in Remote Access Logging in the IAS console. For more information, see Configure log file properties.

To provide centralized logging, failover, and redundancy with SQL server logging, you can place two SQL server databases on different subnets. Use the SQL server Create Publication Wizard to set up database replication between the two servers.

For larger IAS deployments, you should use more than two computers running SQL server and you should not configure all IAS servers to log to the same one. When you have a primary and backup IAS server configuration, the primary IAS server should log to a computer running SQL server that is other than the one to which the backup IAS server logs. With this configuration, if the primary computer running SQL server fails, preventing the primary IAS server from servicing clients, the backup IAS server, which is configured to log to a different computer running SQL server, can service clients until the primary server is back on line.

If the computer running SQL server runs out of disk space and can no longer perform IAS logging, IAS discards all accounting and authentication requests, preventing client authentication through the IAS server. Make sure you maintain adequate hard disk space on your computer running SQL server.

The performance of the SQL server database (throughput and latency) affects the response time of the IAS server. During an authentication attempt, IAS does not respond to the RADIUS client (network access server) until after SQL server notifies the IAS server that the last data write operation is successful. Because of this, you should ensure that the computer running SQL server is designed to handle the IAS logging load and is able to respond with minimum delay.

In addition, some RADIUS clients might retry authentication attempts if a response is not received from the IAS server within a specific time period (for example, one second). This could add to the load on the computers running IAS and SQL server during periods of slow response due to network conditions. You should configure the network access servers with an appropriate retry interval to prevent overloading the computers running IAS and SQL server with retry attempts.

For more information about SQL server configuration and best practices, see the SQL server 2000 documentation.

Requirements

Logging to a SQL server database requires the use of one or more servers running Windows Server 2003 and IAS and an SQLXML-compliant database, such as Microsoft SQL server 2000. Because this feature requires SQLXML, Microsoft Data Engine (MSDE) cannot be used instead of SQL server.