Security

Reply
MVP
Posts: 395
Registered: ‎05-09-2013

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.

 


Thank you.

Michael Haring | AIS Consultant
Architecture and Implementation Solutions
Optiv Security Inc. | www.optiv.com
Guru Elite
Posts: 8,793
Registered: ‎09-08-2010

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 TME
@timcappalli | timcappalli.me | ACMX #367 / ACCX #480
Aruba Employee
Posts: 571
Registered: ‎04-17-2009

Re: ClearPass MAC Database SQL Query Issue

[ Edited ]

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
MVP
Posts: 395
Registered: ‎05-09-2013

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?


Thank you.

Michael Haring | AIS Consultant
Architecture and Implementation Solutions
Optiv Security Inc. | www.optiv.com
Aruba Employee
Posts: 571
Registered: ‎04-17-2009

Re: ClearPass MAC Database SQL Query Issue

Hi Michael,

 

Can you post a screenshot of the updated SQL Query?

 

Thanks,

Zach Jennings
MVP
Posts: 395
Registered: ‎05-09-2013

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.


Thank you.

Michael Haring | AIS Consultant
Architecture and Implementation Solutions
Optiv Security Inc. | www.optiv.com
Aruba Employee
Posts: 571
Registered: ‎04-17-2009

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
MVP
Posts: 395
Registered: ‎05-09-2013

Re: ClearPass MAC Database SQL Query Issue

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


Thank you.

Michael Haring | AIS Consultant
Architecture and Implementation Solutions
Optiv Security Inc. | www.optiv.com
Search Airheads
Showing results for 
Search instead for 
Did you mean: