Introduction : ClearPass supports querying external SQL databases for use in authentication and authorization. This can be a useful feature for integrating third party data into ClearPass policy management. In this article you will learn the basics of how to setup external SQL filter queries in ClearPass.
Environment : External SQL queries can be made to PostgreSQL, MSSQL, and Oracle 11g databases by default. Support for MySQL is also possible if the ODBC driver is installed. We do not bundle this with CPPM due to licensing and copyright considerations.
Configuration Steps : For this example I will be using a PostgreSQL database with a simple table:
basic_database=> select * from basic_table;
id | name | pass | role
-------+----------+------------+----------
1 | testuser | mypassword | employee
(1 row)
Answer :
First we will need to create a new authentication source for the external SQL server. This can be done under Authentication->Sources->Add New Authentication Source.
Next we will need to setup the authentication source. This requires the server name or IP address, port, database name, username, password, timeout, and driver.
Under the Attributes tab of our new authentication source you will be able to add a new filter query by clicking the "Add More Filters" button. Enter a new for your filter query and then enter the appropriate SQL query. In this case I'm using the following query:
SELECT pass AS User_Password FROM basic_table WHERE name = '%{Authentication:Username}'
There are two components to this that are important.
We are selecting the column "pass" as "User_Password" which is a specific name that CPPM understands indicates a user password.
Additionally we are placing a where condition on the query which requires that the "name" column equal "%{Authentication:Username}" which is a special format that indicates a computed attribute. As of version 6.2 there are 117 of these computed attributes which can be viewed in the attached attrs.txt file.
You may also add the attributes selected by your query.
Name: This is the name of the attribute
Alias Name: A friendly name for the attribute. By default, this is the same as the attribute name.
Data Type: Specify the data type for this attribute, such as String, Integer, Boolean, etc.
Enabled As: Specify whether this value is to be used directly as a role or attribute in an Enforcement Policy. This bypasses the step of having to assign a role in Policy Manager through a Role Mapping Policy.
In this example I added the attribute Name = pass, Alias Name = password, Data Type = string, Enabled As = attribute.
Save your new authentication source and add it to a service.
At this point you have completed setting up a basic external SQL authentication source and filter query. This query allows CPPM to get cleartext passwords from an external SQL server. You may now test your configuration.
Troubleshooting : Ensure that remote connections to your external SQL server are possible using the hostname/IP, port, username, password, and database.
Check access tracker logs for detailed errors.
Internal Note : Computed attributes list:
tipsdb=# select * from tips_dic_internal;