AAA, NAC, Guest Access & BYOD

How To Setup External SQL Filter Queries for Authentication in ClearPass

by ‎07-17-2014 06:47 AM - edited ‎07-18-2014 06:30 AM

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.

rtaImage.png
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.

rtaImage.png
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.
rtaImage.png

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.

rtaImage.png

Save your new authentication source and add it to a service.

rtaImage.png
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.

rtaImage.png
rtaImage.png
rtaImage.png

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;

Comments
pgemme

Do I still use User_Password if I'm trying to use hashed passwords? I noticed some of the builtin repos have things like Password_Hash and Password_Ntlm_Hash.

 

My cleartext setup worked, but with SHA256 or MD5 I'm getting MSCHAPv2 errors (FAILED: No NT/LM-Password. Cannot perform authentication). Perhaps I need a certificate from the SQL server.

Search Airheads
Showing results for 
Search instead for 
Did you mean: 
Is this a frequent problem?

Request an official Aruba knowledge base article to be written by our experts.