Security

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?

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

Thanks.

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.

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 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?

Hi Michael,

Can you post a screenshot of the updated SQL Query?

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,

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.

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