Cisco Prime Access Registrar 9.1 User Guide

Configuring an ODBC/OCI Service

You configure an ODBC or OCI service under /Radius/Services. When you define an ODBC or OCI service under /Radius/Services, you must set its type to ODBC or OCI and provide the following configuration options:

Note We will use ODBC or OCI as the ODBC or OCI service name in the following examples.

Example configuration for ODBC

Example configuration for OCI

Table 13-1 describes the ODBC or OCI service parameters.

Configuring an ODBC/OCI RemoteServer

Configuring an ODBC Remote Server

You must configure an ODBC RemoteServer object for each RemoteServer object you list under /Radius/Services/ODBC/RemoteServers. Use the aregcmd command add to add ODBC servers under /Radius/RemoteServers.

Configuring an OCI Remote Server

You must configure an OCI RemoteServer object for each RemoteServer object you list under /Radius/Services/OCI/RemoteServers. Use the aregcmd command add to add OCI servers under /Radius/RemoteServers.

Table 13-2 describes the ODBC or OCI service parameters. The fields that are displayed in the table changes based on the protocol type selected.

Table 13-2 ODBC/OCI Remote Server Parameters

Parameter	Description
Name	Required; inherited from the upper directory
Description	An optional description of the server
Protocol	Required and must be set to ODBC or OCI for ODBC or OCI service respectively; no default value
ReactivateTimerInterval	Required; default is 300000 (ms)
Timeout	Required; default is 15 (seconds)
OCITimeOutCount	Required; continuous timeout count to disconnect the selected connection. Default is 10.
OCIActiveConnectionThresholdCount	Required; threshold count of disconnections after which Prime Access Registrar will mark the remote server as down and try to reactivate it. Default value is 4.
OCIConnectionReactivationInterval	Required; time interval for attempting to reconnect the disconnected OCI remote server session. Default value is 3000 ms.
DataSourceConnections	Required; number of concurrent connections to data source (default is 8)
ODBCDataSource	Required; no default value
SQLDefinition	SQLDefinition/ (mandatory, no default); UserPasswordAttribute = (mandatory, no default; data store field for user password) SQLStatements/ SQLStatement1/ SQLStatement2/
ODBCToRadiusMappings (OCIToRadiusMappings)	Optional; a list of name/value pairs in which the name is the name of the odbc attribute to retrieve from the user record, and the value is the name of the RADIUS attribute to set to the value of the odbc attribute retrieved. For example, when the ODBCToRadiusMappings has the entry: FramedIPAddress = Framed-IP-Address, the RemoteServer retrieves the FramedIPAddress attribute from the odbc user entry for the specified user, uses the value returned, and sets the Response variable Framed-IP-Address to that value. Note When you select the protocol as OCI, the field name will be displayed as OCIToRadiusMappings.
ODBCToEnvironmentMappings (OCIToEnvironmentMappings)	Optional; a list of name/value pairs in which the name is the name of the odbc attribute to retrieve from the user record, and the value is the name of the Environment variable to set to the value of the odbc attribute retrieved. For example, when the ODBCToEnvironmentMappings has the entry: group = User-Group, the RemoteServer retrieves the group attribute from the odbc user entry for the specified user, uses the value returned, and sets the Environment variable User-Group to that value. Note When you select the protocol as OCI, the field name will be displayed as OCIToEnvironmentMappings.
ODBCToCheckItemMappings (OCIToCheckItemMappings)	Optional; a list of ODBC attribute/value pairs which must be present in the RADIUS access request and must match, both name and value, for the check to pass. For example, when the ODBCToCheckItemMappings has the entry: group = User-Group, the Access Request must contain the attribute group, and it must be set to User-Group. Note When you select the protocol as OCI, the field name will be displayed as OCIToCheckItemMappings.

OCI Connection Timeout and Disconnection

Any single connection from Prime Access Registrar to Oracle server will be disconnected when one of the following is observed:

• Occurrence of native Oracle errors that are configured under AdditionalNativeOracleErrors.
• Continuous query timeouts (configured). This is configured using the OCITimeOutCount parameter.

This single connection disconnect will not impact the other active connections to that remote server. Hence, this will hold the state of the remote server in Prime Access Registrar as active.

Once a connection disconnects, it will attempt to reconnect after a reactivation time interval. You can configure this interval with the OCIConnectionReactivationInterval parameter.

Any Oracle server that Prime Access Registrar connects to will be marked as down during one of the following circumstances:

• Total number of disconnections reaches a threshold value. You can configure this threshold value using the OCIActiveConnectionThresholdCount parameter.
• Configured application times out—timeout in server/queue reaches the configured timeout (timeout count X number of connections).
• When the remote server starts or reactivates, no active connections are available even after waiting for the configured initial timeout.

In all the above cases, the Prime Access Registrar will attempt to re-establish the remote server connection after reactivation timer expires.

ODBC Data Source

ODBCDataSource is the name of the datasource to be used by the remote server. An ODBCDataSource name can be reused by multiple remote servers. You configure ODBCDataSources under /Radius/Advanced/ODBCDataSources. See Configuring an ODBC DataSource, for more information.

Tuning Parameters

1. SQLNET.ORA timeout configuration

Tuning $ORACLE_HOME/network/admin/sqlnet.ora file on the Oracle Client

For proper function of the reactivate timer interval, one or more of the following parameters in sqlnet.ora file needs to be tuned:

– SQLNET.INBOUND_CONNECT_TIMEOUT

– SQLNET.OUTBOUND_CONNECT_TIMEOUT

– SQLNET.SEND_TIMEOUT

– SQLNET.RECV_TIMEOUT

Ensure that the ReactivateTimerInterval of ODBC/ODBC-Accounting remoteservers is greater than the timeout values configured in sqlnet.ora.

2. AdditionalNativeOracleErrors connection lost error configuration

Whenever OCI remote server oracle connection encounters configured ORA error, Prime Access Registrar will disconnect the remote server and reactivate it after the ReactivateTimerInterval

Example

SQL Definitions

SQLDefinitions lists the UserPasswordAttribute and one or more SQL statements, listed numerically in the order to be run. The UserPasswordAttribute represents a column in the database that contains users’ password information. Individual SQLStatements are numbered SQL1 through SQL n under SQLStatements, as shown in the following example:

The following example is an SQL statement used for Authentication and Authorization:

For more information on stored procedures and stored functions, refer to Oracle Stored Procedures.

Table 13-3 describes the SQL Statement parameters.

SQL Syntax Restrictions

You must observe the following SQL syntax restrictions in SQL queries for Prime Access Registrar.

1. The SQL statement must be in the format of SELECT... FROM... WHERE..." (Statements might be in lowercase.)

Note 'WHERE' is compulsory in the SQL statement.

2. Stored procedures with return value must be in the " begin ? := <Stored_procedure_name> ( <IN/OUT Parameters>); end ;" format.

3. Stored procedures without return value can be in the " CALL <Stored_procedure_name> ( <IN/OUT Parameters>)" format.

4. Any arguments to Oracle functions like distinct, count must be given within braces, as shown in the following example:

select distinct(attribute),password from profiles where username=?

The resulted column from distinct(attribute) will be put into attribute which can be used for ODBC Mappings. The actual result set from Oracle for this column would be named distinct(attribute).

5. The column list in the SQL statement must be delimited with a comma (,) and any extra spaces between statements are ignored. Aliasing for column names in SQL is not allowed. SQLDefinition properties define the SQL you want to execute, as shown in the following example.

Specifying More Than One Search Key

You can specify more than one search key for a table in the SQL SELECT. To do so, add another search criteria to the SQL statement and add the environment variable name to the MarkerList. For example, the following query and MarkerList can be used to look up a username and CLID match.

In this case, the marker list would look like this:

To configure the multiple entries in the MarkerList list, surround the entire string in double quotes like the following:

To make this work, a variable called CLID must be in the environment dictionary. You can use a script to copy the appropriate value into the variable.

ODBCToRadiusMappings/OCIToRadiusMappings

You configure ODBCToRadiusMappings or OCIToRadiusMappings with a list of name/value pairs where name is the name of the data store attribute to retrieve from the user record and the value is the name of the RADIUS attribute to set to the value of the data store attribute retrieved.

For example, use the following aregcmd command to set a value for the variable Framed-IP-Address :

set FramedIPAddress Framed-IP-Address

When the ODBCToRadiusMappings or OCIToRadiusMappings has this entry, the RemoteServer retrieves the attribute from the data store user entry for the specified user, uses the value returned, and sets the response variable Framed-IP-Address to that value.

When an SQL select statement returns more than one row for a column mapped under ODBCToRadiusMappings or OCIToRadiusMappings, multiple Radius attributes are created.

For example, consider the following SQL select statement with ciscoavpair configured to Cisco-AVPair under ODBCToRadiusMappings. The table.column syntax requires an SQL alias for the mapping to work, as shown in the following example:

If two rows are returned for ciscoavpair column, two Cisco-AVPair attributes will be created.

ODBCToEnvironmentMappings/OCIToEnvironmentMappings

Under ODBCToEnvironmentMappings or OCIToEnvironmentMappings there is a list of name and value pairs in which the name is the name of the data store attribute to retrieve from the user record, and the value is the name of the Environment variable to set to the value of the ODBC or OCI attribute retrieved.

For example, when the ODBCToEnvironmentMappings has the entry: group =User-Group, the RemoteServer retrieves the attribute from the ODBC user entry for the specified user, uses the value returned, and sets the environment variable User-Group to that value. When an SQL select statement returns more than one row for a column mapped under ODBCToEnvironmentMappings, the value for all rows is concatenated and assigned to the environment variable.

ODBCToCheckItemMappings/OCIToCheckItemMappings

A list of ODBC or OCI attribute/value pairs which must be present in the RADIUS access request and must match, both name and value, for the check to pass.

For example, when the ODBCToCheckItemMappings or OCIToCheckItemMappings has the entry: group = User-Group, the Access Request must contain the attribute group, and it must be set to User-Group.

Configuring an ODBC DataSource

ODBCDataSource is the name of the datasource to be used by the remote server. You configure ODBCDataSources under /Radius/Advanced/ODBCDataSources. Multiple remote servers can use the same ODBCDataSource.

Under the ODBCDataSource object definition, for ODBC a list defines ODBC.ini filename/value pairs for a connection. The list includes a Type field and a Driver field, different for each Driver and Data Source, to indicate its Driver and Data Source. Prime Access Registrar currently supports only the Easysoft Open Source Oracle Driver.

For OCI services, ODBCDataSource type should be ‘oracle_oci’. The following is an example configuration of ODBCDataSource for OCI services.

Table 13-4 describes the OCILib Open Source Oracle Driver options for OCI.

Setting ODBC/OCI As Authentication and Authorization Service

Use aregcmd to configure the ODBC Service as the default authentication and authorization service under //localhost /Radius as in the following:

set DefaultAuthenticationService odbc-service

set DefaultAuthorizationService odbc-service

Use aregcmd to configure the OCI Service as the default authentication and authorization service under //localhost /Radius as in the following:

set DefaultAuthenticationService oci-service

set DefaultAuthorizationService oci-service

Note When you use an ODBC or OCI service, configure the BackingStoreDiscThreshold property under /Radius/Advanced to ensure that the data generated by log files do not exceed the size limit configured.

Setting ODBC/OCI As Accounting Service

Use aregcmd to configure the ODBC Service as the default accounting service under //localhost /Radius as in the following:

set DefaultAccountingService odbc-service

Use aregcmd to configure the OCI Service as the default authentication and authorization service under //localhost /Radius as in the following:

set set DefaultAccountingService oci-service

Saving Your Configuration

When you use aregcmd to save your configuration, Prime Access Registrar attempts to validate the configuration, checks for all required parameters, and ensures there is no logic error. If the validation is successful, the configuration is saved to the MCD database. When you reload, Prime Access Registrar shuts down any current ODBC/OCI connections and builds new connections for the configured ODBC Data Sources.

Oracle Stored Procedures

A stored procedure is a database procedure similar to other programming language procedures, which is contained within the database itself. A SQL Server stored procedure that contains one or more IN parameters are used to pass data into the stored procedure.Similarly, one or more OUT parameters in the stored procedure are used to return data back to the calling application. Prime Access Registrar supports Oracle stored procedures/functions with IN and OUT parameters only over the OCI interface.

For Authentication and Authorization, Prime Access Registrar supports both Stored Procedures and Stored Functions with the In/Out parameters and return value. In the configuration for the AA remote server, the UserPasswordAttribute value must be in the marker list for procedures.

For Accounting, Prime Access Registrar supports both Stored Procedures and Stored Functions with only the In parameters, and does not support return value and Out parameters.

The following are the examples for stored functions and procedures calling inside Prime Access Registrar:

Note Prime Access Registrar does not support, return value with the "call" format for the stored procedures.

The following shows an example configuration for OCI AA remote server:

The following shows an example configuration for OCI AAA remote server:

Note Prime Access Registrar supports Oracle stored procedures for OCI AA and OCI AAA remote servers.

MySQL Driver

You can download the MySQL driver from the MySQL website at http://mysql.com. You can go directly to the driver download page using the following URL:

http://dev.mysql.com/downloads/connector/odbc/3.51.html

Save the downloaded file to a temporary location such as /tmp. Use commands to unzip and install the driver.

For better performance with mysql, add the following code to the odbcinst.ini file under the /cisco-ar/odbc/etc directory:

Configuring a MySQL Datasource

You require the following to configure a MYSQL Datasource:

• ODBCDataSource object
• RemoteServer object
• ODBC service
• Default AA services

Configuring a MYSQL datasource

To configure the Prime Access Registrar server to query records form a MySQL database:

Step 1 Log into the Prime Access Registrar server and launch aregcmd.

Log in as a user with administrative rights such as user admin.

Step 2 Change directory to the /Radius/Advanced/ODBCDataSources and add a new ODBCDataSource.

cd /Radius/Advanced/ODBCDataSources

add mysql

Step 3 Set the new ODBCDatasource type to myodbc.

cd mysql

set type myodbc

The following is the default configuration for an ODBCDataSource object of type myodbc:

Step 4 Set the Driver property to the path of the MyODBC library. Use a command like the following:

set driver /scratch/myodbc/libmyodbc3_r.so

Step 5 Set the UserID property to a valid username for the MyODBC database and provide a valid password for this user.

set userid ar-mysql-user

set password biscuit

Step 6 Provide a DataBase name and the name of the Prime Access Registrar RemoteServer object to associate with the ODBCDataSource.

set database database_name

set server remote_server_name

Step 7 Change directory to /Radius/RemoteServers and add a RemoteServer object to associate with the new ODBCDatasource.

cd /Radius/RemoteServers

add mysql

Step 8 Change directory to the new RemoteServer and set its protocol to odbc.

cd mysql

set protocol odbc

Step 9 Set the ODBCDataSource property to the name of the ODBCDataSource to associate with this RemoteServer object.

set ODBCDataSource mysql

Step 10 Change directory to /Radius/Services and add an ODBC service as described in Configuring an ODBC/OCI Service.

Step 11 Change directory to /Radius and set the DefaultAuthenticationService and DefaultAuthorizationService properties to the ODBC service added in the previous step.

Example Configuration

The following shows an example configuration for a MySQL ODBC data source. See Configuring an ODBC DataSource for more information.

The following shows an example configuration for a RemoteServer. See Configuring an ODBC/OCI RemoteServer for more information.

The following shows an example configuration for an ODBC service. See Configuring an ODBC/OCI Service for more information.

The following shows an example configuration where the DefaultAuthenticationService and DefaultAuthorizationService properties have been set to the ODBC service.