Security

I hadn't seen anything on the forums, so I figured I would post my setup/use case

We are a higher education campus, with about ~1500 BYOD, as well as ~2500 Coporate owned devices. We used to use Bradford Networks as our NAC, and we would be forced to manually register all our devices, and it was tiring, espically when we recieved our shipments.

On Campus, we used JAMF for all our MACS, and SCCM for all our PCs. Clearpass has endpoint context server for JAMF, however, we wanted to integrate with SCCM, which was not as straight forward. Below are the general steps I followed to get CPPM to integrate successfully with SCCM, and pull devices in.

1: Have a populated SCCM Database

This isn't going to work well unless you have devices in SCCM. SCCM automatically gets the MAC address' of the devices that it manages. CPPM will be running a query against it's database to get that information.

 

2: Create a user account in the SCCM database server, with read rights.
We created a special account with only read rights to the database. This is the account that clearpass will use when it logs in.

 

3: Allow the IP address through the firewall

This might not be needed in your enviornment, however, in ours we needed to open the firewall on the database server, to let clearpass IP address through.

 

4: Configure an Authentication Source in Clearpass:

 Give it a name that you'll remember, and select "Generic SQL Database"

For the next page, fill in your appropiate server information. Make sure the server name you put in is DNS resolveable, (Or use the IP address), but it needs to be the SQL SERVER not the SCCM server. In our enviornment they were diffirent. Enter in the database name you used during setup, and the username and password you created in step 2. Select mssql as the driver.

 

The Hardest part for me was finding out the sql query to run. The Query is as follows:

 

select MAX(System_System_OU_Name_ARR.System_OU_Name0) as 'Organizational Unit',System_MAC_Addres_ARR.MAC_Addresses0, Resource_Names0 as 'Host Name' from System_System_OU_Name_ARR left join
System_MAC_Addres_ARR on System_System_OU_Name_ARR.Itemkey = System_MAC_Addres_ARR.ItemKey
left join System_Resource_N_ARR on System_System_OU_Name_ARR.ItemKey = System_Resource_N_ARR.ItemKey
where MAC_Addresses0 = '%{Connection:Client-Mac-Address-Colon}'
group by System_Resource_N_ARR.Resource_Names0,System_OU_Name0,System_MAC_Addres_ARR.MAC_Addresses0;

 

This Query looks up the MAC address of the device, the OU it belongs too, and the hostname configured for that device. We have it setup so that certain OUs get certain VLANS, and the radius:ietf username gets updated with the hostname.

 

 

Last part of this page is to fill in the attributes fetched. I have the following ones returned. 

MAC_Addresses0     SCCM_MAC string

Organizational Unit    SCCM_OU string       attribute

Host name                  SCCM_Hostname     attribute

 

 

Hit save a few times, add the authentication source to your service, and you should be able to start using SCCM_MAC, SCCM_OU, SCCM_Hostname in your enforcment policies.


The Biggest issues we ran into were with the firewall issues I mentioned above, and the way the username has to be entered when adding the authentication source. We used a domain account to login, and the username had to be entered in the domain\user format.

I am writting this from memory, so I apologize if I missed something while your following this guide. If you guys have any questions/comments please let me know. I'll be happy to lend any help I can.

 

 

 

This is great content! Thanks for sharing.

This is very interesting as I need to do something like this as well. Do you know where the device's state within SCCM is stored?

The idea being if a device need patching then I can move it into the appropriate isolation/patching role...

Regards Derin

Hey,

 

This is a great solution thanks for posting.   Quick question. We use CPPM for wireless and wired authentication and it appears SCCM only stores one of those MAC addresses at a time in the database.  So for devices that move back and fourth between wireless/wired authentication we are unable to query the correct mac-address.  Are you aware of a table in SQL that stores all mac-addresses on a devices so we can query the wired and wireless mac?

 

Thanks!

I'm sure there is, but we never had a need. We use dot1x on the wireless side so we never needed to worry about that table. 

What we did to get this setup, was setup a small SCCM server, add one computer to it, then just poked around the DB until we found what we wanted, then crafted the statement from there. 

Hi,

I had the same problem with wired and wireless addresses, and also the original query would not work for me if the device did not have a hardware inventory, since that's where the data comes from.

 

I changed the query, we are running with this query which works good for us:

 

SELECT [MAC_Addresses0] FROM [System_MAC_Addres_ARR] WHERE MAC_Addresses0 = '%{Connection:Client-Mac-Address-Colon}' UNION SELECT [MAC_Addresses0] FROM [v_RA_System_MACAddresses] WHERE MAC_Addresses0 = '%{Connection:Client-Mac-Address-Colon}'

This is just fantastic! 

We ended up in this query with some filtering from crap. The problem we had, previous PXE-installed computers that had been running operativesystem for a while using wifi, reported and overwrite the origin MAC-address on the LAN interface.

SELECT MACAddress0 AS MAC FROM v_GS_NETWORK_ADAPTER WHERE AdapterType0 = 'Ethernet 802.3' AND Name0 NOT LIKE '%WAN%' AND Name0 NOT LIKE '%Bluetooth%' AND Name0 NOT LIKE '%VPN%' AND Name0 NOT LIKE '%TAP%' AND Name0 NOT LIKE '%Virtual Ethernet%' AND MACAddress0 = '%{Connection:Client-Mac-Address-Colon}'

UNION

SELECT MAC_Addresses0 as MAC FROM [System_MAC_Addres_ARR] WHERE MAC_Addresses0 = '%{Connection:Client-Mac-Address-Colon}'

------------------------------
Emil Andersson
------------------------------

Original Message:
Sent: Jul 16, 2017 10:02 AM
From: Christopher Wickline
Subject: SCCM Integration with Clearpass

I hadn't seen anything on the forums, so I figured I would post my setup/use case

We are a higher education campus, with about ~1500 BYOD, as well as ~2500 Coporate owned devices. We used to use Bradford Networks as our NAC, and we would be forced to manually register all our devices, and it was tiring, espically when we recieved our shipments.

On Campus, we used JAMF for all our MACS, and SCCM for all our PCs. Clearpass has endpoint context server for JAMF, however, we wanted to integrate with SCCM, which was not as straight forward. Below are the general steps I followed to get CPPM to integrate successfully with SCCM, and pull devices in.

1: Have a populated SCCM Database

This isn't going to work well unless you have devices in SCCM. SCCM automatically gets the MAC address' of the devices that it manages. CPPM will be running a query against it's database to get that information.

 

2: Create a user account in the SCCM database server, with read rights.
We created a special account with only read rights to the database. This is the account that clearpass will use when it logs in.

 

3: Allow the IP address through the firewall

This might not be needed in your enviornment, however, in ours we needed to open the firewall on the database server, to let clearpass IP address through.

 

4: Configure an Authentication Source in Clearpass:

 Give it a name that you'll remember, and select "Generic SQL Database"

For the next page, fill in your appropiate server information. Make sure the server name you put in is DNS resolveable, (Or use the IP address), but it needs to be the SQL SERVER not the SCCM server. In our enviornment they were diffirent. Enter in the database name you used during setup, and the username and password you created in step 2. Select mssql as the driver.

 

The Hardest part for me was finding out the sql query to run. The Query is as follows:

 

select MAX(System_System_OU_Name_ARR.System_OU_Name0) as 'Organizational Unit',System_MAC_Addres_ARR.MAC_Addresses0, Resource_Names0 as 'Host Name' from System_System_OU_Name_ARR left join
System_MAC_Addres_ARR on System_System_OU_Name_ARR.Itemkey = System_MAC_Addres_ARR.ItemKey
left join System_Resource_N_ARR on System_System_OU_Name_ARR.ItemKey = System_Resource_N_ARR.ItemKey
where MAC_Addresses0 = '%{Connection:Client-Mac-Address-Colon}'
group by System_Resource_N_ARR.Resource_Names0,System_OU_Name0,System_MAC_Addres_ARR.MAC_Addresses0;

 

This Query looks up the MAC address of the device, the OU it belongs too, and the hostname configured for that device. We have it setup so that certain OUs get certain VLANS, and the radius:ietf username gets updated with the hostname.

 

 

Last part of this page is to fill in the attributes fetched. I have the following ones returned. 

MAC_Addresses0     SCCM_MAC string

Organizational Unit    SCCM_OU string       attribute

Host name                  SCCM_Hostname     attribute

 

 

Hit save a few times, add the authentication source to your service, and you should be able to start using SCCM_MAC, SCCM_OU, SCCM_Hostname in your enforcment policies.


The Biggest issues we ran into were with the firewall issues I mentioned above, and the way the username has to be entered when adding the authentication source. We used a domain account to login, and the username had to be entered in the domain\user format.

I am writting this from memory, so I apologize if I missed something while your following this guide. If you guys have any questions/comments please let me know. I'll be happy to lend any help I can.