MAB using MS SQL as a database

[CiscoNutt]

We are setting up a MS SQL server to store the MAC addresses for all the PCs in our network to be used for the purpose of remotely re-imaging the PCs when needed.

I have imported some test MACs into the SQL database in format username/password where both username and password are defined as the MAC address of the device.  Procedures are created and I have "imported" the groups (Group1 and Group2) that the MACs belong to into ISE.

The issue I am facing is that I am having trouble matching the MAC addresses in the database.  The authentication policy references the internal endpoints database, which is fine as this setup will be used primarily for PCs that have already been connected to the network earlier.  I have created a authorization policy under the existing MAB policy which matches on the SQL group that I imported earlier.  I see that ISE searches the policy I created but doesn't find a match and therefore continues and eventually hits the default authorization policy.

Has anyone setup a similar solution?  What match criteria should be used in the ISE? Or perhaps I should not have MAC address for both username and password in the SQL database, perhaps just the username?

Any help would be greatly appreciated.

[thomas]

Have you reviewed http://cs.co/ise-guides#ODBC > Configure ISE 2.1 with MS SQL using ODBC - Cisco to see how it compares to what you're doing?

[CiscoNutt]

Yes, that is acutally the guide I was following.  However, it specifies the SQL database use for login username and password. It does not really explain the use case for MAB, or what to match on for MAB.

[howon · Accepted solution]

If using SQL as identity source for authentication: Use XX:XX:XX:XX:XX:XX format (Uppercase with ':' as delimiter) for username and xxxxxxxxxxxx (All lowercase without any delimiter) for password.

If using SQL as lookup source in the authorization policy: Use whatever format the RADIUS:Calling-Station-ID field is in. It should be XX-XX-XX-XX-XX-XX format on Cisco devices, but you can confirm by looking at the detailed log in live log. For lookup, the password should not matter.

If need to create DB to match in both cases, I suggest customizing the stored procedure call to massage the username format before searching it in the DB.

[CiscoNutt]

So, what you are saying is configure the SQL DB in format XX-XX-XX-XX-XX-XX?

When I checked live logs I saw the RADIUS calling-station id as XX:XX:XX:XX:XX:XX.

I will do some testing next week.

[CiscoNutt]

It was the format of the MAC address entry in the SQL database.  Once I changed it to XX-XX-XX-XX-XX-XX we started to get hits.  Thank you very much for your help