Configure SQL Server Logging in NPS
Applies To: Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2
Use this procedure to log Remote Authentication Dial-In User Service (RADIUS) accounting data to a local or remote database running Microsoft® SQL Server™.
You can configure Network Policy Server (NPS) to perform RADIUS accounting for user authentication requests, Access-Accept messages, Access-Reject messages, accounting requests and responses, and periodic status updates. You can use this procedure to configure logging properties and the connection to the server running SQL Server that stores your accounting data.
NPS formats accounting data as an XML document that it sends to the report_event stored procedure in the SQL Server database that you designate in NPS. For SQL Server logging to function properly, you must have a stored procedure named report_event in the SQL Server database that can receive and parse the XML documents from NPS.
Membership in Domain Admins , or equivalent, is the minimum required to complete this procedure.
To configure SQL Server logging in NPS
Open the NPS console or the NPS Microsoft Management Console (MMC) snap-in.
In the console tree, click Accounting .
In the details pane, in SQL Server Logging Properties , click Change SQL Server Logging Properties . The SQL Server Logging Properties dialog box opens.
In Log the following information , select the information that you want to log:
To log all accounting requests, click Accounting requests .
To log authentication requests, click Authentication requests .
To log periodic accounting status, click Periodic accounting status .
To log periodic status, such as interim accounting requests, click Periodic status .
To configure the number of concurrent sessions allowed between the server running NPS and the SQL Server, type a number in Maximum number of concurrent sessions .
To configure the SQL Server data source, in SQL Server Logging , click Configure . The Data Link Properties dialog box opens. 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 NPS and SQL Server, click Test Connection . Click OK to close Data Link Properties .
In Logging failure action , select Enable text file logging for failover if you want NPS to continue with text file logging if SQL Server logging fails.
In Logging failure action , select If logging fails, discard connection requests if you want NPS to stop processing Access-Request messages when log files are full or unavailable for some reason. If you want NPS to continue processing connection requests if logging fails, do not select this check box.