Partager via

NPS SQL Server Logging

  • Article

Applies To: Windows Server 2008, Windows Server 2008 R2

You can use SQL Server logging to record user Remote Authentication Dial-In User Service (RADIUS) authentication and accounting requests received from one or more network access servers (NASs) to a data source on a computer running Microsoft SQL Server. Accounting data is passed from Network Policy Server (NPS) in eXtensible Markup Language (XML) format to a stored procedure in a SQL Server database. SQL Server databases support both SQL and XML (SQLXML).

Advantages of SQL Server logging

Logging to a SQL Server relational database instead of a standard text file — that is, either Internet Authentication Service (IAS) format or database-compatible format — has many advantages, including the following:

  • Relationships between data tables enable the flexible creation of dynamic data views by using queries and reports.

  • SQL Server has high-speed optimizations that can effectively support very large, multiple-terabyte-sized databases.

  • Multiple bulk copy operations can be performed concurrently against a single table in SQL Server to speed data entry.

  • The Transact-SQL BACKUP and RESTORE statements are optimized to read through a database serially and write in parallel to multiple backup devices. In addition, SQL Server supports differential backups, which replicate only the data changed after the last backup.

  • You can configure SQL Server logging to provide failover and redundancy, and multiple NPS servers can log to the same database, providing ease of administration.

  • Data from multiple NPS servers can be stored in the same database, providing a centralized data source for many NPS servers and the ability to achieve a more comprehensive data view for applications that query the database, such as Help desk applications.

Note

When you configure NPS to log accounting information to a database on a remote SQL server, you must also create a firewall exception on the SQL Server to allow the incoming connection from the NPS server. By default, SQL Server listens for incoming connections on TCP port 1433. For more information, see INF: TCP Ports Needed for Communication to SQL Server Through a Firewall.

In addition, you can improve logging performance and, in some circumstances, reduce the cost of deploying SQL Server logging by using SQL Server Express databases installed on each NPS server.

The following sections describe key logging concepts such as requests logged by NPS, NPS log file contents, and how NPS creates an XML document from accounting and authentication data when you deploy SQL Server logging.

Data logged by NPS

By default, NPS 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 format or database-compatible format is logged to the SQL Server 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 NPS

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

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

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

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

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

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

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

  • Authentication accepts and rejects, which are sent by NPS to the RADIUS client, indicating whether the user is accepted or rejected. These entries contain only outgoing attributes.

NPS log file contents

Unlike database-compatible format 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 RADIUS client. For more information about the sequence of these records, see the documentation for your RADIUS client.

Required database fields for session correlation

When you create the fields in the tables of your 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 values of the following NPS attributes as accounting data in the SQL Server databases:

  • NAS-IP-Address

  • NAS-Identifier (You need both NAS-IP-Address and NAS-Identifier because the RADIUS client 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 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 RADIUS clients that send the Class attribute in all Accounting-Request messages. 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 RADIUS client 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 RADIUS clients that support interim accounting.

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

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

  • Always configure the Acct-Interim-Interval attribute in network 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 Settings tab of the network policy. According to RFC 2869, the value of the Acct-Interim-Interval attribute must not be smaller than 60 seconds, or one minute, and cannot be larger than 600 seconds, or 10 minutes. For more information, see RFC 2869 "RADIUS Extensions."

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

How NPS creates an XML document from accounting and authentication data

If you select SQL Server logging in the NPS Microsoft Management Console (MMC) snap-in, attribute-value pairs are converted to XML format.

When NPS 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. In 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 NPS 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 NPS 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 NPS 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 created by NPS 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 NPS configured for SQL Server logging:

    <Event>
<Computer-Name data_type="1">MYNAS</Computer-Name>
<Event-Source data_type="1">NPS</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>