ClearPass and SQL Database Authentication Source

When writing queries for checking a mac address in a table, denoting the correct filter for a mac address can be confusing, depending on the format stored in the table. Here’s a few options

  • %{Authentication:Username}
  • %{Connection:Client-Mac-Address}
  • %{Connection:Client-Mac-Address-NoDelim}
  • %{Connection:Client-Mac-Address-Hyphen}
  • %{Connection:Client-Mac-Address-Dot}
  • %{Connection:Client-Mac-Address-Upper-Hyphen}

Example SQL Server Query

Select top 1 mac from clientmac where mac = ‘%{Authentication:Username}’ or mac = ‘%{Connection:Client-Mac-Address}’

In the enforcement policy, using an exists comparison should be sufficient, although most official examples add another rule to compare the returned value from the query.