for authentication you cannot use the Intune HTTP auth source, you have to leave that blank. Herman's instructions have contained this criteria in a tutorial.
Original Message:
Sent: Apr 16, 2025 09:29 PM
From: BrendanMYS
Subject: Clearpass + Intune
Yes Gorazd I did try direct import as well but had the same issue.
However I eventually managed to solve the problem at least for the default Endpoint DB lookup, and for HTTP lookup for AuthZ - the issue was that my roles had to be mapped using the Auth source i.e.:
And for the HTTP method likewise:
Unfortunately trying the new SQL query like you and others have used still won't work for me - it returns Syntax error still. The Intune ID from the CN was substituted properly (in this test I used a SCEP template with Intune ID in the CN and the SAN)
My last issue now is that I'm not sure how to use HTTP method for Authentication - the documentation says it can be done (without needing the local Endpoint DB) but when I try and configure it I get this error:
Original Message:
Sent: Apr 03, 2025 02:19 AM
From: GorazdKikelj
Subject: Clearpass + Intune
Hi Brendan.
Did you import xml or just cut&paste query? I did have similar problem but with AD query.
Maybe you try to install the new appliance and test it on it? Similar as you already suggested?
Best, Gorazd
------------------------------
Gorazd Kikelj
MVP Guru 2025
Original Message:
Sent: Apr 02, 2025 07:30 PM
From: BrendanMYS
Subject: Clearpass + Intune
Thanks again Gorazd. Unfortunately, I got the same result, as your query is almost identical to some I've tried already - with the exception of using the full FQDN where I was told earlier that only 127.0.0.1 would work - it seems it doesn't seem to matter. (By the way I used the attached file and imported it. Interesting to notice that the <TipsHeader > is mandatory!)
Result was again:
rlm_sql_unixodbc: sql state - 42601, sql error - ERROR: syntax error at end of input; Error while executing the query
Original Message:
Sent: Apr 02, 2025 07:37 AM
From: GorazdKikelj
Subject: Clearpass + Intune
Hi Brendan.
Here is xml export from my test auth source. I also uploaded xml file as cut&paste can make funny things.
Best, Gorazd
<TipsContents xmlns="http://www.avendasys.com/tipsapiDefs/1.0">
<TipsHeader exportTime="Wed Apr 02 13:31:27 CEST 2025" version="6.11"/>
<AuthSources>
<AuthSource description="Test intune Auth Source" name="Intune Auth" isAuthorizationSource="true" type="Sql">
<NVPair value="36000" name="cache_timeout"/>
<NVPair value="PostgreSQL" name="sql_driver"/>
<NVPair value="your clearpass server here" name="server"/>
<NVPair value="5432" name="port"/>
<NVPair value="tipsdb" name="db_name"/>
<NVPair value="appexternal" name="login"/>
<NVPair value="" name="password"/>
<NVPair value="10" name="timeout"/>
<NVPair value="cleartext" name="password_type"/>
<Filters>
<Filter paramValues="" filterQuery="select attributes->>'Intune User Principal Name' as "Intune User Principal Name",attributes->>'Intune Model' as "Intune Model",attributes->>'Intune Jail Broken' as "Intune Jail Broken",attributes->>'Intune Operating System' as "Intune Operating System",attributes->>'Intune Managed Device Owner Type' as "Intune Managed Device Owner Type",attributes->>'Intune Management Agent' as "Intune Management Agent",attributes->>'Intune Azure AD Registered' as "Intune Azure AD Registered",attributes->>'Intune Compliance State' as "Intune Compliance State",attributes->>'Intune Device Name' as "Intune Device Name",attributes->>'Intune Azure AD Device Id' as "Intune Azure AD Device Id" FROM tips_endpoints WHERE attributes->>'Intune ID' = LOWER('%{Certificate:Subject-CN}')" filterName="Intune V6">
<Attributes>
<Attribute isUserAttr="true" isRole="false" attrDataType="String" aliasName="Intune Azure AD Device Id" attrName="Intune Azure AD Device Id"/>
</Attributes>
</Filter>
</Filters>
</AuthSource>
</AuthSources>
</TipsContents>
------------------------------
Gorazd Kikelj
MVP Guru 2025
Original Message:
Sent: Apr 02, 2025 02:54 AM
From: BrendanMYS
Subject: Clearpass + Intune
Thanks for the info Gorazd. I got psql to connect and tried the query. As you found I also got no data returned, but crucially also no syntax error.
I also tried a simple query:
SELECT * FROM tips_endpointsWHERE attributes::json->>'Intune ID' = '<sample machine Intune ID GUID';
and this returned 2 entries successfully. I don't think there's anything wrong with the DB or the data in it. There is certainly something wrong with either my configuration or SQL connection. I can see the username is populated correctly in the Request Details as 'host/<guid>' so I don't understand why I can't get the same query that everyone else is using to work. There must be something missing.
Unfortunately, I still have not had any success with TAC either.
Original Message:
Sent: Mar 31, 2025 03:12 AM
From: GorazdKikelj
Subject: Clearpass + Intune
You can test your query in psql client directly. Verbatim it won't return anything as argument will be empty, but it will check the syntax.
I tested your query on 6.11.10 and it's working without any problems. Postgresql version on 6.11.10 is 12.20.
Best, Gorazd
------------------------------
Gorazd Kikelj
MVP Guru 2025
Original Message:
Sent: Mar 27, 2025 06:47 AM
From: Istvan Hegedus
Subject: Clearpass + Intune
Hi,
Yes I understand that you said you typed it in by hand so there should not be secret character at the end. There must be something different then which causes this issue. My ClearPass is version 6.11.10 and Intune extension is 6.3.5 but the extension has nothing to do with it as you are just querying the endpoints DB.
Maybe the browser causes it that you use? I use Firefox to manage it.
Original Message:
Sent: Mar 26, 2025 06:30 PM
From: BrendanMYS
Subject: Clearpass + Intune
I appreciate the effort but I've tested this as well - and we need to remember that this is not an exact copy of what is in my Clearpass appliance - it's been copied and pasted into this website and then the website has applied whatever formatting it does. I can use a raw text pastebin to be 100% sure but this doesn't seem to be the issue. Even then I still tested it anyway to no avail.
By the way if you mean the query i pasted on Comment 25 - yes I see the same character, it shows up as a space - I assume this is just from this website because I formatted it in italics. Again to be clear - this is not in the original plain text query on Clearpass, it's just on the website.
Original Message:
Sent: Mar 26, 2025 09:25 AM
From: Istvan Hegedus
Subject: Clearpass + Intune
Hi,
I have created a test authentication source with your filter and I believe Herman is right, there is some strange invisible character at the end of your filter text (although I have copied it too). I got the same error but when I go to the end of your filter query (cursor blinks at the end of the last character), press the left arrow key once and press the delete key once then I can save the query without error.
Please try it!
Istvan
Original Message:
Sent: Mar 25, 2025 07:17 AM
From: BrendanMYS
Subject: Clearpass + Intune
Thanks again Istvan but it gets the same error.
Can you please confirm what version you are running of both ClearPass and the extension?
Original Message:
Sent: Mar 25, 2025 06:35 AM
From: Istvan Hegedus
Subject: Clearpass + Intune
Hi BrendanMYS,
I have checked it again and when I create the authentication source I also just have Generic SQL DB. Choose that one.
The key is the rest of the config.
My query is this one:
select attributes->>'Intune Device Name' as "Intune Device Name", attributes->>'Intune Device Registration State' as "Intune Device Registration State", attributes->>'Intune Managed Device Owner Type' as "Intune Managed Device Owner Type", attributes->>'Intune Compliance State' as "Intune Compliance State" FROM tips_endpoints WHERE attributes->>'Intune ID' = LOWER('%{Certificate:Subject-AltName-URI}')
Original Message:
Sent: Mar 24, 2025 07:31 AM
From: BrendanMYS
Subject: Clearpass + Intune
Thanks for the reply Istvan however I only have the option of "Generic SQL" and no just "SQL". I did wonder about this after seeing Hermans screenshots also. I hope it's not a deal-breaker to be using 6.12!
Also just to check I tried setting the port manually to 5432 but no change. I am fairly sure I had tried manually setting to 5433 earlier but that failed to connect at all, so default of blank seems to be no problem, or at least no increase in problem!
Original Message:
Sent: Mar 24, 2025 04:45 AM
From: Istvan Hegedus
Subject: Clearpass + Intune
I remember struggling with similar issues but I have solved it at the end. I have checked my config and the difference is in the authentication sources config. As you can see I use sql as type instead of Generic SQL DB. If I remember well the port 5432 was critical to get it work. I have CPPM 6.11
Original Message:
Sent: Mar 20, 2025 01:45 AM
From: BrendanMYS
Subject: Clearpass + Intune
It's strange - I get the same error: "The filter has been saved but has the following error: Invalid SQL syntax - ERROR: query is syntactically wrong" but only with the new Authentication/Source. If I added it as the 6th line for instance to the existing built-in Endpoint DB it does not generate the same error.
I have tried multiple variations on the query:
1. Herman's original query
select attributes->>'Intune User Principal Name' as "Intune User Principal Name",attributes->>'Intune Model' as "Intune Model",attributes->>'Intune Jail Broken' as "Intune Jail Broken",attributes->>'Intune Operating System' as "Intune Operating System",attributes->>'Intune Managed Device Owner Type' as "Intune Managed Device Owner Type",attributes->>'Intune Management Agent' as "Intune Management Agent",attributes->>'Intune Azure AD Registered' as "Intune Azure AD Registered",attributes->>'Intune Compliance State' as "Intune Compliance State",attributes->>'Intune Device Name' as "Intune Device Name",attributes->>'Intune Azure AD Device Id' as "Intune Azure AD Device Id" FROM tips_endpoints WHERE attributes->>'Intune ID' = LOWER('%{Certificate:Subject-CN}')
Attempting to save generates this error (ONLY SOMETIMES!)
If I reload the page and try again it works and instead I get this:
If I bounce the wired port and try a new access attempt the logs show this error
2. If I try with a query ssuggested by Perplexity.ai to clean up the query it is accepted by the editor Save, but then when I try and save the Auth source instead I get the error that "Primary: Custom SQL must not contain data-modifying SQL statements"
3. I tried with what was apparently the simplest query and still get the same result:
= Primary: Custom SQL must not contain data-modifying SQL statements
4. Removing semi-colon allows the query to be saved but still generates the same errors in the access logs
Original Message:
Sent: May 09, 2024 09:41 AM
From: michael.conlogue@greenmountainpower.com
Subject: Clearpass + Intune
I tried adding the custom filter as listed by Herman in Clearpass version 6.11 and get an error "The filter has been saved but has the following error: Invalid SQL Syntax - ERROR: query is syntactically wrong"... It looks okay to me, but I am obviously missing something...
select attributes->>'Intune User Principal Name' as "Intune User Principal Name",attributes->>'Intune Model' as "Intune Model",attributes->>'Intune Jail Broken' as "Intune Jail Broken",attributes->>'Intune Operating System' as "Intune Operating System",attributes->>'Intune Managed Device Owner Type' as "Intune Managed Device Owner Type",attributes->>'Intune Management Agent' as "Intune Management Agent",attributes->>'Intune Azure AD Registered' as "Intune Azure AD Registered",attributes->>'Intune Compliance State' as "Intune Compliance State",attributes->>'Intune Device Name' as "Intune Device Name",attributes->>'Intune Azure AD Device Id' as "Intune Azure AD Device Id" FROM tips_endpoints WHERE attributes->>'Intune ID' = LOWER('%{Certificate:Subject-CN}')
Original Message:
Sent: Dec 06, 2022 11:29 AM
From: Herman Robers
Subject: Clearpass + Intune
You may add different attributes in the Intune deployed certificates for student vs staff and filter on that; or use a different CA to issue your certificates (may be useful for other applications as well).
If staff devices are Corporate managed, and student devices 'personal', you could use the Intune Managed Device Owner Type to make your policy decision.
You probably should not rely on the client MAC address, rather on the Intune DeviceID that is in the client certificate. A query like the following would do such a thing if the Intune DeviceID is set as Common Name in the certificate:
select attributes->>'Intune User Principal Name' as "Intune User Principal Name",attributes->>'Intune Model' as "Intune Model",attributes->>'Intune Jail Broken' as "Intune Jail Broken",attributes->>'Intune Operating System' as "Intune Operating System",attributes->>'Intune Managed Device Owner Type' as "Intune Managed Device Owner Type",attributes->>'Intune Management Agent' as "Intune Management Agent",attributes->>'Intune Azure AD Registered' as "Intune Azure AD Registered",attributes->>'Intune Compliance State' as "Intune Compliance State",attributes->>'Intune Device Name' as "Intune Device Name",attributes->>'Intune Azure AD Device Id' as "Intune Azure AD Device Id" FROM tips_endpoints WHERE attributes->>'Intune ID' = LOWER('%{Certificate:Subject-CN}')
This approach avoids MAC spoofing attacks, as well it allows wired clients (as long as clients have at least one WiFi interface) when the lookup is done based on the DeviceID rather than on the MAC address.
And there is a v6 version of the Intune extension; I would not deploy new systems with v5.
With ClearPass 6.11 there now also is an Azure AD authorization source that can directly lookup Azure AD groups based on the Azure AD Username.
------------------------------
Herman Robers
------------------------
If you have urgent issues, always contact your Aruba partner, distributor, or Aruba TAC Support. Check https://www.arubanetworks.com/support-services/contact-support/ for how to contact Aruba TAC. Any opinions expressed here are solely my own and not necessarily that of Hewlett Packard Enterprise or Aruba Networks.
In case your problem is solved, please invest the time to post a follow-up with the information on how you solved it. Others can benefit from that.
Original Message:
Sent: Nov 28, 2022 07:41 PM
From: Binod Ranabhat
Subject: Clearpass + Intune
Hi Community members,
I am on the same situation, we are using Clearpass Intune ext v 5. Using the query filter
%{Connection:Client-Mac-Address-Hyphen}
At this point, all the attributes Clearpass is getting is related to Device, not the user. If we pick one attribute, for example 'Intune Azure AD Registered' eq true--assign for example staff role, that means students getting staff vlan.
Is there any query filter , that gets user information, so that we can use that in policy ? It's quite hard to distinguish staff and student at this point.
Please share your thought/view.
-BINOD
Original Message:
Sent: Feb 04, 2021 01:23 PM
From: Danny Jump
Subject: Clearpass + Intune
Zack,
I can't think why the mac-randomizatin would have any bearing on a device working or not, as you describe. It not like your checking for a known mac-address, or doing mac-auth. However, if you specifically making an authZ decision on In_Tune registered, when you make this check you'll have to be looking up the endpoint with its mac-address, if randomization is enabled then you're potentially in a pickle when comparing to the physical address reported by InTune that is the Endpoint mac-address......said another way I'd have expected mac-randomization to always drop the endpoint into the student vlan/role.
------------------------------
Danny Jump
"Passionate about CPPM"
Original Message:
Sent: Feb 04, 2021 01:58 AM
From: Zack Shore
Subject: Clearpass + Intune
And yes, we enroll all our devices into InTune before distribution. So they are synced to the Endpoint DB and ready by the time the user is ready to join wireless.
------------------------------
Zack Shore
Original Message:
Sent: Feb 04, 2021 01:43 AM
From: Danny Jump
Subject: Clearpass + Intune
I assume your using V5 of the InTune Extension??
Why do you have it running on two nodes, offset.... sync on one node and sync more regularly, when you sync on a SUB it will have to write the data to the PUB first.
Question1: The ATP and Palo stuff can be configured later, but I am just trying to get some questions answered on if this InTune extension and Clearpass can do what I envision in the chart? Basically, I want InTune devices only on our Staff VLAN. All other devices can go and get the student clearpass role and be put on that VLAN.
So far I have the InTune extension installed on both nodes with sync schedules that offset each other. I do not plan on using HTTP auth mode to pull in data "live"...unless needed. I am pulling data into my Endpoint DB and can view it. Now it really comes down to making sure a cert is pushed to the devices. I will be using PKCS with an internal CA.
{djj} - Yes, this workflow is achievable, in terms of using InTune data and D-ATP data as authZ content.
Question 2: Do I need to push a user auth certificate, or machine auth? or does it not matter? What are the pros and cons?
{djj} - This really depends on how you want to authN the user/device, if you have WIN10 and run TEAP you can do both.
Question 3: Even though I am not using HTTP Auth mode, do I still need to set up an Authentication Source that points to InTune?
{djj} - Not if you ingesting the endpoint into the CPPM EndpointDb and using that data as an authZ souce to make you first check, is this endpoint enrolled/known to InTune.
Question 4: Will I be able to achieve the same setup I have today, where when a user logs into their device, they are automatically joined to the wireless (i think this is where the certs come into play)
{djj} - Sure, that's the autHn portion, the Intune/D-ATP is more the authZ part. One of the huge benefits of CPPM is that authN & authZ can be separated to different identity stores/repositories.
------------------------------
Danny Jump
"Passionate about CPPM"