Add Custom SQL Queries to CPPM Auth Sources

Reading Time: 3 minutes

We all know the default authentication sources in ClearPass, like GuestDB or Time Source. Since some versions, it is impossible to edit or add custom SQL queries for those authentication sources. This is cumbersome and breaks some often-used scenarios.

If you see the following error, you are in the situation I’m talking about:

Custom Queries are not allowed
Custom Queries are not allowed

There is currently no option to add custom queries to the existing default SQL-based authentication sources.

Create a new Authentication Source with Custom Queries

The simple solution is to create a new authentication source and add your custom queries to that authentication source.

To access the internal DB in ClearPass you need to create/use the “appexternal” username with a password:

Create appexternal password
Create appexternal password

In my case, this password was the admin password, created during the installation of ClearPass. You can change the password in the “Cluster-Wide Parameters” as in the screenshot above.

The next step is to create a new authentication source. Look into the default sources and just copy most of them. Create a new time source to be able to have more custom time periods:

Add New Authentication Source - General
Add New Authentication Source – General

Set a descriptive “Name” and use the “Generic SQL DB” for the “Type”.

Now the interesting part:

Add New Authentication Source - Primary
Add New Authentication Source – Primary

The “Server Name” should be the IP of your ClearPass server. Do not use “localhost” here, as you will run into the same error messages as before. For multi node environments, I recommend using a VRRP IP to make sure, the auth source is always available.

Make sure to set the “Port” to the correct one. I copied the port from the default time source. Same with the “Database Name”.

The “Login Username” is “appexternal” and the “Login Password” is the one created above.

Select the “ODBC Driver” and “Password Type” according to the screenshot above.

On the last tab, you can create your own customer SQL queries:

Add New Authentication Source - Attributes
Add New Authentication Source – Attributes

I added one, for the one-year time span to update the “expire_time” attribute for endpoints and guest users for one year.

You can add different queries to your needs and use them during authentication in ClearPass.

If you find this post useful, leave me a comment and share your feedback with me. You can also buy me Pizza, using the “Buy me a Pizza” button on the right, to support this blog and keep the IT gremlin happy.
If you would like to do me a favor, share this post with your friends and social media contacts. This would really help to make this blog more popular and help others to find the information above more easily using search engines.

6 thoughts on “Add Custom SQL Queries to CPPM Auth Sources”

  1. Is this a bug or expected behavior? Pointing at a VIP instead of localhost doesn’t seem like a good idea in large clusters deployed across multiple data centers.

    Reply
    • Hi Dan,

      to some extend I fully agree with you, but if you use localhost, it will give you the same error. So using the VIP is the only option we have until this get changed.

      BR
      Florian

      Reply
  2. I came across this same issue attempting to add a custom filter to the [Endpoints Repository], but was able to get it working by eliminating the end semicolon from the query. I tested your query and had the same result; it was accepted without the semicolon. This was under version 6.11.7.

    Reply
    • Hi Andrew,

      thanks for the headsup. I’m sure this was not working, while I was testing it, as I tried the same 🙂 Thanks for reporting that it is now working, at least with 6.11.7.

      BR
      Florian

      Reply
  3. Hi!
    I had it for quite a while and it was working fine, but now it started to fail only when request goes to subscriber node:
    Error Code:
    106
    Error Category:
    Internal error
    Error Message:
    Internal error in RADIUS server
    Alerts for this Request
    Policy server Session failed for Host=Cluster-VIP Reason=[Failed to connect to datasource: timeout expired
    SQLState=08001 ErrorCode=101].
    [INTUNE ENDPOINT REPOSITORY] – authorization took 30 secs
    RADIUS Cannot send request to Policy server

    It works perfectly fine when request falls on Publisher.
    I tried reboot – no change.
    Any solution for that? I will open TAC anyway. 🙂

    Reply
    • Hi Ola,
      without knowing the details it is hard to guess. Maybe some firewall is blocking the connection from subscriber to publisher for this connection.
      Do you get a solution from TAC?

      BR
      Florian

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.