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:
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:
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:
Set a descriptive “Name” and use the “Generic SQL DB” for the “Type”.
Now the interesting part:
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:
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.
Share on:
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.
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
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.
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
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. 🙂
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
Hi, Thank you for posting this! The solution works. My question is around defining the new auth source. If i had 6 nodes in my cluster (all L3 separated), for example, would I then create 6 identical new auth sources, just updating the Server Name with the IP of each server? Then in the Web Auth \ MAC Auth Service where this logic is referenced, list all 6 auth sources as authorization sources there? I just want to ensure this works if some of the nodes are offline and the client is authenticating to a different node in the cluster. Thanks again.
Hi Scott,
I have used the VIP os the cluster, pointing to the publisher, or standby publisher. If you nodes are L3 seperated, I would point the query directly to the publisher.
I tried “localhost”, but this does not work.
BR
Florian
Hi, Thank you for posting this! The solution works. My question is around defining the new auth source. If i had 6 nodes in my cluster (all L3 separated), for example, would I then create 6 identical new auth sources, just updating the Server Name with the IP of each server? Then in the Web Auth \ MAC Auth Service where this logic is referenced, list all 6 auth sources as authorization sources there? I just want to ensure this works if some of the nodes are offline and the client is authenticating to a different node in the cluster. Thanks again.