How To Setup External SQL Filter Queries for Authentication in ClearPass

Aruba Employee
Aruba Employee

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;

Version history
Revision #:
2 of 2
Last update:
‎07-18-2014 06:30 AM
Updated by:
Labels (1)

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.

Hi pgemme. I´m wondering if you ever got solved the issue you were having with SHA256....I´m working on my lab and got into the same issue. Authentication with clear text works fine but when I change to SHA256 I get the same error:


ERROR RadiusServer.Radius - rlm_mschap: FAILED: No NT/LM-Password. Cannot perform authentication.

ERROR RadiusServer.Radius - rlm_mschap: FAILED: MS-CHAP2-Response is incorrect




I never did solve this issue. I'm not sure if it was something that could be solved; if I remember correctly. As if the process itself was out of the hands of Aruba and a limitation of a protocol handshake. I can't fully remember, went through my notes and they are unfinished... :-(


My setup is still cleartext but we found other ways of doing guest accounts for now.

Search Airheads
Showing results for 
Search instead for 
Did you mean: