Using an SQL Auth Server

This topic describes integration with the SQL Auth server.

SQL Auth Server Overview

This section describes support for using the SQL (also known as Oracle Database server) as a IPS authentication server. It includes the following sections:

Understanding SQL Auth Server

The SQL Auth server is widely deployed in the enterprise. Some enterprises use the SQL Auth server to store user credentials (usernames and passwords), MAC addresses, and other organizational information, such as group affiliations that are often the basis for authorization decisions. To support authentication and authorization against SQL Auth server databases, IPS supports an authentication server configuration that configures an Oracle Instant Client connection as well as relevant queries to the backend SQL Auth server.

Feature Support

Policy Secure uses Oracle Instant Client 11.2.0.2.0 to communicate with the SQL Auth server. The SQL Auth server version must support this version of the client. The access management framework depends on the SQL Auth server features described in this section.

You can use the SQL queries for authentication, authorization and role mapping, or both.

SQL SELECT Statements

The authentication transaction is based on an SQL query that returns a password (and possibly other information) based on the name entered by the user attempting to log in.

While a sample SQL query is provided in the original configuration file, you must configure the SQL entry of the configuration file with a query appropriate to your database. The query you enter must be either an SQL SELECT or an SQL EXECUTE statement that contains additional syntax elements that are preprocessed by the SQL authentication module.

The SQL authentication module executes SQL statements in parameterized form. This means that the SQL statement is compiled once, with parameter markers (usually question marks) as placeholders for data items that vary from one execution to the next. Only upon execution of the statement are the actual data values supplied.

The SQL statement you compose must not include parameter markers directly. Instead, include the names of the parameters where parameter markers would appear, in an appropriate format.

This is an example of a parameter marker:

SELECT password, profile, fullname FROM usertable WHERE username = :username
The SQL authentication module translates the SQL statement provided, replacing parameter names with parameter markers prior to passing the SQL statement to the database engine.
The SQL statement can be very simple. Basically, all that is required is to look up a password and possibly some optional information based on a username. The SQL statement can also be quite complex; it can include inner joins, and it can contain expressions. The underlying database engine is responsible for handling the SQL statement; the SQL authentication module performs no interpretation of the SQL statement other than to translate parameter names to parameter markers.

SQL Stored Procedures

A stored procedure is a sequence of SQL statements that form a logical unit and perform a task. You can use stored procedures to encapsulate a set of queries or operations that can be executed repeatedly on a database server. For example, you can code operations on an employee database, such as password lookup, as stored procedures that can be executed by application code. Stored procedures can be compiled and executed with different parameters and results. Stored procedures can use any combination of input parameters (the values passed to the stored procedure at execution time) and output parameters (the values set or returned by the stored procedure to the calling application or environment).

Vendor	Example of a called procedure
Oracle	BEGIN; myCalledProcedure( :username, :password!os, ipAddr!ios, filterId!o); END;
MySQL	CALL myCalledProcedure( :username, :password!os, ipAddr!ios, filterId!o);
MSSQL	{CALL myCalledProcedure( :username, :password!os, ipAddr!ios, filterId!o)}

As shown in the example, the procedure is called myCalledProcedure with input variables as username and ipAddr, output parameters as password, ipAddr, and filterId. The names of the output parameters are the names of the attributes added to the server catalog used for role mapping and return attributes. The parameter consists of a colon (:), the name of the parameter, and a format specifier.

SQL Format Specifiers

Table describes the SQL statement format specifiers with parameters in called procedures.

Specifier	Definition
i	Input parameter (Default if none is specified)
io	Input/output parameter
o	Output parameter
s	String type (default if none is specified)
n	Int type

SQL Statement Parameters

Table describes the SQL statement parameter names and types.

Item	Type	Meaning for SQL Authentication
:username	String	Specifies the username as presented to the authentication server.
:password	String	Specifies the password as presented to the authentication server.
:realm	String	Specifies the realm as presented to the authentication server.
:ipAddr	String	Specifies the source IP address (L3 authentications only), which is sent as a string. For example, 10.17.1.155.
:userAgent	String	Specifies the user agent string.
:log inTime	Int	Specifies the log in time presented in the number of seconds.
:log inURL	String	Specifies the user URL of the sign-in policy of the user.
:callingStationId	String	Specifies the MAC address of the client presented as xx-xx-xx-xx-xx-xx for L3 authentications and in the format specified by the RADIUS client for L2 authentications.
:language	String	Specifies the language used by client as specified by IETF language tag. For example, en-US for English as used in the United States.

SQL Password Hash Format

Table describes the different SQL password types.

Hash/Name	Definition	Password Format	Supported RADIUS Protocols
Automatic	Automatically determines hash format based on Format.	All
Clear Text	No Encryption	PasswordText	PAP, CHAP, MSCHAP, MSCHAP-V2, EAP-JUAC, EAP-MSCHAP-V2, EAP-MD5-Challenge
SHA 1	SHA1+Base64 hash	{SHA}HashHashHash	PAP, EAP-JUAC
Salted SHA 1	salted SHA1+Base64 hash	{SSHA}HashHashHashSalt	PAP, EAP-JUAC
NT Hash**	MD4 hash of the unicode form of password	{md4}HashHash	PAP, MSCHAP, MSCHAP-V2, EAP-JUAC, EAP-MSCHAP-V2

Interoperability Requirements and Limitations

The following limitation applies when defining and monitoring an SQL Auth server instance:

The maximum number of connections to an Oracle database is limited to 50 connections for L2 and L3 log ins (concurrent and open RADIUS protocol), without any browser log ins.
You must enter the SQL keywords in uppercase letters.

Configuring Authentication with an Oracle SQL Auth Server

To configure authentication with an SQL Auth server:

Select Authentication > Auth.servers.
Select SQL Auth Server and click New Server to display the configuration page.
Select the SQL Vendor as Oracle. Complete the configuration as described in
Save the configuration.

Settings	Guidelines
Name	Specify a name to identify the server within the system.
SQL Vendor	Select Oracle. Read and accept the license agreement. You cannot save or test the configuration until you have accepted the license agreement.
SQL Auth Server	Specify the SQL Auth server host name or IP address. The default value is 1521.
SQL Port	Specify the SQL port number through which the SQL Auth server is accessed.
Backup SQL Auth Server	(Optional) Specify the backup SQL Auth server host name.
Backup SQL Port	(Optional) Specify the backup SQL port number.
SQL Service Name	(Optional) Specify the SQL service name if SQL service name has been defined in the SQL Auth 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 Auth server to return search results.
Finding user entries
SQL Statement	Specify the SQL statement to find the user entries. For example: SELECT password FROM usertable WHERE username = :username AND realm = :realm You must enter the SQL keywords in uppercase letters.
Password Attribute Name	Specify the attribute name specified in the SQL statement that the device uses for password authentication. If the username that is entered exists in the database, then the authentication succeeds. If you are using the SQL Auth server for authorization, no password is necessary here.
Full Username Attribute Name	(Optional) Specify the attribute name specified in the SQL statement for the system to use when displaying the user's full name.
SQL Password Type	Select one of the following SQL password types: Automatic Clear Text SHA 1 Salted SHA 1 NT Hash The SQL password type setting specifies the format of the hash used for the password. The values for the SQL password type include a prefix index that indicates how the password has been processed. The prefix is in clear-text between curly braces { } and is immediately followed by a hash value computed from the password. If no prefix is present in the value retrieved from the table Password column, the entire password is assumed to be in clear-text format.
Test User Lookup
Select Statement Values	Enter the attributes necessary to fill in the WHERE part of the SQL statement and click the Test Connection button to save the server configuration and attempt to connect to the database server with the information you have entered
Save SQL Column Names or Called Procedure variable names as Attribute names in the Server Catalog	Select this option to use the SQL query statement variables as server catalog attributes. You can use the server catalog in role mapping rules.
Server Catalog
Attributes	The Attributes button appears after you have saved the server information or performed a test connection operation. Click the Attributes button to display the server catalog.

Configuring Authentication with MySQL Auth Server

To configure authentication with an SQL Auth server:

Select Authentication > Auth.servers.
Select SQL Auth Server and click New Server to display the configuration page.
Select the SQL vendor as MYSQL.
Complete the configuration as described in table
Save the configuration.

Settings	Guidelines
Name	Specify a name to identify the server within the system.
SQL Vendor	Select MYSQL as the vendor type.
SQL Auth Server	Specify the SQL Auth server host name or IP address. The default value is 1521.
SQL Port	Specify the SQL port number through which the MYSQL Auth server is accessed. Default port is 3306.
Backup SQL Auth Server	(Optional) Specify the backup SQL Auth server host name.
Backup SQL Port	(Optional) Specify the backup SQL port number.
SQL Service Name	(Optional) Specify the SQL service name if SQL service name has been defined in the SQL Auth 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 Auth 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.
Finding user entries
SQL Statement	Specify the SQL statement to find the user entries.
Password Attribute Name	Specify the attribute name specified in the SQL statement that the device uses for password authentication. If the username that is entered exists in the database, then the authentication succeeds. If you are using the SQL Auth server for authorization, no password is necessary here.
Full Username Attribute Name	(Optional) Specify the attribute name specified in the SQL statement for the system to use when displaying the user's full name.
SQL Password Type	Select one of the following SQL password types: Automatic Clear Text SHA 1 Salted SHA 1 NT Hash The SQL password type setting specifies the format of the hash used for the password. The values for the SQL password type include a prefix index that indicates how the password has been processed. The prefix is in clear-text between curly braces { } and is immediately followed by a hash value computed from the password. If no prefix is present in the value retrieved from the table Password column, the entire password is assumed to be in clear-text format.
Test User Lookup
Select Statement Values	Enter the attributes necessary to fill in the WHERE part of the SQL statement and click the Test Connection button to save the server configuration and attempt to connect to the database server with the information you have entered. Upon a successful connection and retrieval of the user record, the server displays the results. It displays the entire returned user record (hiding the password) from the SELECT portion of the SQL statement. An error line is displayed if the connection to the SQL Auth server fails or if the user record could not be retrieved. The user record is displayed in the following format: attribute Name1 = value, attribute name2 = value, and so on. When trying to populate the server catalog attributes for the SQL Auth server, you must enter data into all columns of interest for a record. Columns that are not assigned data are ignored during the lookup and are therefore not added appropriately to the server catalog.
Save SQL Column Names or Called Procedure variable names as Attribute names in the Server Catalog	Select this option to use the SQL query statement variables as server catalog attributes. You can use the server catalog in role mapping rules.
Server Catalog
Attributes	The Attributes button appears after you have saved the server information or performed a test connection operation. Click the Attributes button to display the server catalog.

Configuring Authentication with MSSQL Auth Server

To configure authentication with an SQL Auth server:

Select Authentication > Auth.servers.
Select SQL Auth Server and click New Server to display the configuration page.
Select the SQL vendor as MSSQL.
Complete the configuration as described in table
Save the configuration.

Settings	Guidelines
Name	Specify a name to identify the server within the system.
SQL Vendor	Select MSSQL as the vendor type.
SQL Auth Server	Specify the MSSQL Auth server host name or IP address.
SQL Port	Specify the MSSQL port number through which the MYSQL Auth server is accessed. Default port is 1433.
Backup SQL Auth Server	(Optional) Specify the backup MSSQL Auth server host name.
Backup SQL Port	(Optional) Specify the backup MSSQL port number.
SQL Service Name	(Optional) Specify the SQL service name if SQL service name has been defined in the SQL Auth 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 Auth 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. The server certificate validation for MSSQL is qualified using self signed certificate.
Finding user entries
SQL Statement	Specify the SQL statement to find the user entries.
Password Attribute Name	Specify the attribute name specified in the SQL statement that the device uses for password authentication. If the username that is entered exists in the database, then the authentication succeeds. If you are using the SQL Auth server for authorization, no password is necessary here.
Full Username Attribute Name	(Optional) Specify the attribute name specified in the SQL statement for the system to use when displaying the user's full name.
SQL Password Type	Select one of the following SQL password types: Automatic Clear Text SHA 1 Salted SHA 1 NT Hash The SQL password type setting specifies the format of the hash used for the password. The values for the SQL password type include a prefix index that indicates how the password has been processed. The prefix is in clear-text between curly braces { } and is immediately followed by a hash value computed from the password. If no prefix is present in the value retrieved from the table Password column, the entire password is assumed to be in clear-text format.
Test User Lookup
Select Statement Values	Enter the attributes necessary to fill in the WHERE part of the SQL statement and click the Test Connection button to save the server configuration and attempt to connect to the database server with the information you have entered. Upon a successful connection and retrieval of the user record, the server displays the results. It displays the entire returned user record (hiding the password) from the SELECT portion of the SQL statement. An error line is displayed if the connection to the SQL Auth server fails or if the user record could not be retrieved. The user record is displayed in the following format: attribute Name1 = value, attribute name2 = value, and so on. When trying to populate the server catalog attributes for the SQL Auth server, you must enter data into all columns of interest for a record. Columns that are not assigned data are ignored during the lookup and are therefore not added appropriately to the server catalog.
Save SQL Column Names or Called Procedure variable names as Attribute names in the Server Catalog	Select this option to use the SQL query statement variables as server catalog attributes. You can use the server catalog in role mapping rules.
Server Catalog
Attributes	The Attributes button appears after you have saved the server information or performed a test connection operation. Click the Attributes button to display the server catalog.