Configuring MSSQL Server Accounting

PPS supports storing the RADIUS accounting information to an external SQL database. PPS offers SQL Accounting feature under Auth Servers. MSSQL accounting supported only for 802.1x use cases and only one SQL server can be configured.

•The SQL statement is completely user-specified, allowing support of existing tables with existing field names and formats. It can include a variety of arithmetic and string expressions.

•Stored procedures invoked by SQL accounting can make use of input parameters, record results, and return output parameters.

Radius Accounting Request (Start, Stop, Interim) is received in PPS from switch. Radius Accounting attributes are extracted, and the attributes configured in the SQL queries will be sent to the SQL server based on the realm configuration. Apart from RADIUS Accounting Request attributes, the attributes in the below table can also be used in the insert query.

Attribute Name	Datatype	Description
TransactionTime	Time	The date/time that the event occurred that is the subject of the request.
Time	Time	The date/time when the request is being processed. (This is later than TransactionTime if the request is a retry.)
Type	String	The RADIUS accounting request type.
NASAddress	IP address	The IP address of the requesting RAS.
NASName	String	The name of the network access device that originated the request. This may be the name of the RADIUS client entry in the database or the value of the NAS-Identifier or NAS-IP-Address attribute.
NASModel	String	The RAS make/model.
FullName	String	The full name of the logged in user.
AuthType	String	The method by which the user was authenticated.
RADIUSClientName	String	The name of the network access device, as specified in a RADIUS client entry in the Steel-Belted Radius database.

Configuring SQL Accounting

You must configure both PPS and SQL database to support SQL accounting.

1.Select Authentication > Auth.Servers and select SQL Auth Server.

2.Select Accounting in configure SQL server.

3.Enter the SQL Accounting server settings as described in Table.

4.Under SQL Accounting Queries, enable Use this Acct-Start statement for Acct-Stop and Acct-Interim to use the same Start statement for Acct-Start and Acct-Interim.

5.Click Test Connection to test the connectivity to the server.

6.Click Save Changes.

Settings	Guidelines
Name	Specify a name to identify the server within the system.
SQL Accounting Server	Specify the SQL Accounting server host name or IP address.
SQL Port	Specify the SQL port number through which the SQL Accounting server is accessed.
SQL Service Name	(Optional) Specify the SQL service name if SQL service name has been defined in the SQL Accounting server configuration.
Admin	Specify the administrator username.
Password	Specify the password.
Connection Timeout	Specify the connection timeout value from 5 to 60 seconds. If this time is exceeded, and if there is a backup server defined, then the device attempts to reach the backup server.
Search Timeout	Specify the search timeout value from 5 to 60 seconds. It specifies the maximum amount of time the device will wait for the SQL Accounting server to return search results.
Use SSL	Select Use SSL to establish an encrypted connection between the client and server.
Server Certificate Validation	Select this option to validate the server certificate before using the public and private keys for encryption/decryption.

7.Under User Realms > Users select the SQL Accounting server.

The following is an example of a SQL INSERT statement in PPS-SQL Accounting query. The insert statement will directly provide the attributes and value placeholders to be saved on the SQL database.

For SBR-E customers, the "%" and "@" symbols in the insert query should be replaced with ":" symbol and the format specifiers should be removed.

Stored Procedure Example

The following is an example of stored procedure configuration to insert an entry to the SQL table.

Stored Procedure syntax on MSSQL Server

The following is an example of a stored procedure syntax on MSSQL server.