Security

Reply

ClearPass MAC Database SQL Query Issue

Hi all,

 

Looking to configure a SQL database to verify MAC addresses for corporate owned vs. BYOD devices. I have the connection to the database configured, but I continue getting errors stating "Invalid Syntax...". Any idea how this needs to be configured to check the MAC address exists in the database?

 

[2015-04-24]-Image-11.png

[2015-04-24]-Image-12.png

 

In our setup: Device_MAC is the table, MACAddr is the column, CP_Test is the database.

 

Thanks.

 



Michael Haring
If my answer is helpful, a Kudos is always appreciated!
Guru Elite

Re: ClearPass MAC Database SQL Query Issue

You need to return some type of value based off the SQL query. This should get you started. In this case, I'm returning the serial number.

 

 

corp-asset.PNG

 

corp-asset-role-map.PNG


| Tim Cappalli | Aruba Security | @timcappalli | timcappalli.me |

NOTE: Answers and views expressed by me on this forum are my own and not necessarily the position of Aruba or Hewlett Packard Enterprise.
Aruba Employee

Re: ClearPass MAC Database SQL Query Issue

Tim,

 

You are missing something from your screenshot:

 

Name                             Alias Name                             Data Type

serialnumber                  CorpAsset-SerialNumber       String

 

Name is what is being returned from the SQL Query. Alias Name is how you reference it in Role Mapping or Enforcement Policy.

 

Hope this helps.

 

Thanks,

Zach Jennings

Re: ClearPass MAC Database SQL Query Issue

Thanks guys, Ok so I was able to get the query configured successfully, but now when a device connects I'm not seeing that SQL auth source under authoriziation in the request. I configured it as an authorization source, I have the "allow to fetch role mapping..." in the database config. I am checking "Authorization:CorpSQL -> CorpComputer -> Exists"  and it didn't work.

 

Any idea why it wouldn't show up as an authorization source?



Michael Haring
If my answer is helpful, a Kudos is always appreciated!
Aruba Employee

Re: ClearPass MAC Database SQL Query Issue

Hi Michael,

 

Can you post a screenshot of the updated SQL Query?

 

Thanks,

Zach Jennings

Re: ClearPass MAC Database SQL Query Issue

[2015-04-24]-Image-18.png

 

In the SQL database, if we put the devices MAC address in for the Query, it responds with the MAC address in the table that matches.  Not sure if I have the options correct.



Michael Haring
If my answer is helpful, a Kudos is always appreciated!
Aruba Employee

Re: ClearPass MAC Database SQL Query Issue

Michael,

 

You need your NAME to match whatever you are querying.

 

Try this:

 

Name                             Alias Name                             Data Type

MACAddr                       MNPSCompMAC                    String

 

 

Then in your enforcement or role mapping, you can use Authorization:CorpSQL   MNPSCompMAC   EXISTS.

 

Thanks,

Zach Jennings

Re: ClearPass MAC Database SQL Query Issue

That was it! Thanks for the help guys. Clearly SQL is not something I use often. 



Michael Haring
If my answer is helpful, a Kudos is always appreciated!
Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: