ISE ODBC Integration with MySQL Database

[jaschnei]

Introduction

This document describes how to configure Cisco Identity Services Engine (ISE) 3.3 for integration with MySQL Server via Open Database Connectivity (ODBC). The configuration of the MySQL Database is done using MySQL Workbench which is a GUI alternative to MySQL Shell (CLI) and the ISE GUI.

• Introduction
• Prerequisites
• Requirements
• Components Used
• Background Information
• MySQL Database Configuration
• MySQL Database Creation
• Adding tables and data to the database
• users_table
• groups_table
• vlans_table
• sgts_table
• departments_table
• user_group and user_attribute tables
• user_group
• user_attribute
• Creating Stored Procedures
• ISEAuthUserPlain
• ISEFetchPassword
• ISEUserLookup
• ISEGroups
• ISEAttrsH2
• ISE Configuration
• ODBC External Identity Source Configuration
• ISE Authentication and Authorization Configuration
• Verification and Troubleshooting
• Conclusions

Prerequisites

Requirements

This document assumes the reader has a basic understanding of the following topics:

• Basic AAA functions
• Cisco Identity Services Engine (ISE), Policy-Sets, Authorization Policies and Rules
• MySQL and Stored Procedures
• Segmentation

 

Components Used

The information and the configuration presented in this document is based on the following setup:

• Cisco Identity Services Engine (ISE) Version 3.3 patch 2
• MySQL Server version 5.7
• MySQL Workbench 8.0
• Catalyst 9300
• Windows 10 Workstation

 

Background Information

Cisco Identity Services Engine (ISE) can be integrated with several external identity sources from which, ISE can authenticate a user and validate their credentials. The identity source can also hold group membership and other attributes that ISE can retrieve in order to grant the proper authorization level for the user. The most commonly used external identity source is an Active Directory (AD). ISE has the ability to handle different type of external identity sources such as AD, LDAP, ODBC, and others.

Enterprises that leverage relational databases using SQL, may be leveraging that datastore to manage users endpoints and their attributes as well as the business logic to grant network access. ISE can make use of these databases via ODBC by calling stored procedures to retrieve information about a user.

The use of relational database and SQL queries can greatly simplify the configuration in ISE when customers need to create very granular network access policies by delegating the business logic for network authorization to the external database. An example of this situation is covered in this article: Simplified Access Policy using ODBC & ISE DB (Custom Attribute) for Large Scale Campus Network.

MySQL Database Configuration

Before starting to configure the external database, please install a compatible MySQL Server and a compatible MySQL Workbench on the same server. Please refer to ISE Compatibility with MySQL server versions for your specific ISE version. Make sure there is network connectivity between ISE and the MySQL server, the default port used is TCP 3306.

MySQL Database Creation

Open MySQL Workbench and select the Local MySQL Instance:

In the Schemas tab, create a new schema, ise_odbc is the one used in this guide:

From the administration tab, select the User and Privileges Menu to create the user ISE will use to connect to the database (cisco is the one used in this guide).

Grant 'SELECT' access to the newly created user, and full access to the new schema created previously:

 

 

Adding tables and data to the database

Now that we have the basic setup, we can start creating and populating the tables in the database. For this guide we will use the following tables:

• users_table: Table containing usernames and passwords.
• groups_table: Table containing groups available for users.
• vlans_table: Table containing vlans available for users.
• sgts_table: Table containing Scalable Group Tags (SGTs) available for users.
• departments_table: Table containing the different departments available for users.
• user_group: Table containing the user-to-group mappings.
• user_attribute: Table containing user-to-attributes mappings.

Use the menus in MySQL workbench to create a table under the created schema (ise_odbc in the guide). When tables are created, use the icons that appear to the right of the table name to add the columns and to populate the table with data: 

The following section show how the different tables were created for the purpose of writing this guide. In production environments this must be adjusted to meet the corporate business and operational requirements.

users_table

The structure and content of user_table is the following:

user_id	username	pwd
1	employee1	C1sc012345
2	employee2	C1sc012345
3	employee3	C1sc012345
4	employee4	C1sc012345
5	employee5	C1sc012345
6	contractor1	C1sc012345
7	contractor2	C1sc012345
8	developer1	C1sc012345

groups_table

The content and structure is the following:

group_id	groupname
1	Employees
2	Contractors
3	Developers

vlans_table

The content and structure of this table is the following:

vlan_id	vlan_name
1	CorpVlan
2	VLAN0016

Note: These vlans names must be configured in the network access devices (WLC, switches, etc), so they can be interpreted correctly. Vlan names are case sensitive.

sgts_table

The content and structure of sgts_table table is the following:

sgt_id	sgt
1	cts:security-gorup-tag=0004-5
2	cts:security-gorup-tag=0005-12
3	cts:security-gorup-tag=0008-1
4	cts:security-gorup-tag=0010-3

Take a look at the value given for sgts in the table. This is actually the value that needs to be sent in the "cisco-av-pair" in the radius packet back to the network access device (NAD) when the user is authenticated and authorized by ISE. These SGTs must be present in ISE (TrustSec Components) and the value in the MySQL database must match to an existing SGT in ISE.

As can be seen in the previous table and pictures, SGT values have two components separated by a hyphen (-): the actual hexadecimal SGT value, and the Generation ID (or Revision ID). In this lab we chose to include the Generation ID for the values stored in the sgts_table however, lab testing shows that SGT values can be stored in the external database without the Generation IDs. In this case, ISE will take care of sending the appropriate Generation ID to the NADs.

departments_table

The content and structure is the following:

department_id	department_name
1	internal
2	external

Use the images provided for vlans_table as a reference to create and populate the departments_table in MySQL Workbench.

user_group and user_attribute tables

The user group_group and user_attribute table hold the user mapping to groups and attributes respectively. These tables use the "ID" of users, groups, and attributes to do the mappings. For the table where we are mapping IDs that are referenced in other tables, additionally to the creation of the columns inside the table, foreign keys need to be defined referencing the corresponding IDs in their respective tables. The structure and content of these table are the following, pay attention on the foreign key configuration to adapt them to your specific needs:

user_group

user_id	group_id
1	1
2	1
3	1
4	1
5	1
6	2
7	2
8	3

user_attribute

user_id	vlan_id	sgt_id	department_id
1	1	1	1
2	1	1	1
3	1	1	1
4	1	1	1
5	1	1	1
6	1	2	2
7	2	2	2
8	1	3	1

Use the image provided for the user_group table as a reference to create and populate the user_attribute table. Pay attention to the foreign key, since you need to create one foreign key per id referenced in a different table.

Creating Stored Procedures

Once tables are created in the MySQL database, the next step is to create the stored procedures ISE will use to gather information from the database using ODBC. ISE uses stored procedures for the following operations: plain password authentication, plain text password fetching, check username or machine exists in the database, fetch groups, and fetch attributes. For more information about how ISE uses the stored procedures, please refer to the ISE administration guide. In this article we will use the following stored procedures:

• ISEAuthUserPlain for plain password authentication
• ISEFetchPassword for plain password fetching
• ISEUserLookup for user lookup in the database
• ISEGroups for group fetching
• ISEAttrsH2 for attributes fetching

MySQL Workbench provides an SQL editor to configure stored procedure. To create a new stored procedure, expand the Stored Procedure option on the left panel of MySQL Workbench and select the option to create a new one, which will open the editor.

The SQL code resides within the BEGIN and END statements. The next sections provide functional code the five stored procedures indicated previously in this section. Use the code in this guide as a reference to modify the code according to your requirements. (Don't forget to change the name of the stored procedure).

ISEAuthUserPlain

CREATE PROCEDURE `ISE AuthUserPlain`(username varchar(64), pwd varchar(255))
BEGIN
  IF EXISTS (select * from users_table where users_table.username = username and users_table.pwd = pwd) THEN
    select 0,11, 'This is a very good user, give him all access', 'no error';
  ELSE
    select 3,0, 'odbc', 'ODBC Authen Error';
  END IF;
END

ISEFetchPassword

CREATE PROCEDURE `ISEFetchPassword`(username varchar(64))
BEGIN
  IF EXISTS (select * from users_table where users_table.username = username) THEN
    select 0,11,'This is a very good user, give him all access','no error', pwd as password from users_table where users_table.username = username;
  ELSE
    select 3,0,'ODBC Authen Error';
  END IF;
END

ISEUserLookup

CREATE PROCEDURE `ISEUserLookup`(username varchar(64))
BEGIN
  IF EXISTS (select * from users_table where users_table.username = username) THEN
    select 0,11,'This is a very good user, give him all access','no error';
  ELSE
    select 3,0,'odbc','ODBC Authen Error';
  END IF;
END

ISEGroups

CREATE PROCEDURE `ISEGroups`(username varchar(64), OUT result INT)
BEGIN
  CASE username
  WHEN '*' THEN
    select distinct groupname from groups_table;
  ELSE
    select groupname from user_group
    inner join users_table on users_table.user_id = user_group.user_id
    inner join groups_table on groups_table.group_id = user_group.group_id
    where users_table.username = username;
  END CASE;
END

ISEAttrsH2

CREATE PROCEDURE `ISEAttrsH2`(username varchar(64), OUT result INT,  EAPAuth varchar(64))
BEGIN
  DECLARE isDeveloper BOOLEAN;
  SET isDeveloper = FALSE;
        
  IF EXISTS (select * from users_table where users_table.username = username) THEN
    set result=0;
       
    select exists (select 1 from user_group
    inner join users_table on users_table.user_id = user_group.user_id
    inner join groups_table on groups_table.group_id = user_group.group_id
    where users_table.username = username and groups_table.groupname = 'Developers') into isDeveloper;
        
    if ((EAPAuth = 'EAP-TLS') or (isDeveloper = FALSE)) THEN
      select vlan_name as vlan, sgt, department_name as department from user_attribute
      inner join users_table ON users_table.user_id = user_attribute.user_id
      inner join vlans_table ON vlans_table.vlan_id = user_attribute.vlan_id
      inner join sgts_table ON sgts_table.sgt_id = user_attribute.sgt_id
      inner join departments_table on departments_table.department_id = user_attribute.department_id
      where users_table.username = username;
    else
      select 'CorpVlan' as vlan, 'cts:security-group-tag=0010-3' as sgt, 'internal' as department;
    end if;
  ELSE
    set result=1;
  END IF;
END

The code for the first 4 stored procedures should be understandable without any deep knowledge of SQL. The last one (ISEAttrsH2), requires a brief explanation before we move on to the next section.

As we explained before, ISEAttrsH2 is used to fetch the attributes associated to a user. We could have used a simple logic such as the one we used to fetch groups if there wasn't any special requirement, and simply use the user-attribute table to retrieve the mapping. However, to exemplify the capabilities of the ODBC integration in ISE, we are going to validate inside the MySQL database that developers are being authenticated with a digital certificate, otherwise they will have restricted access to network resources.

In our code for ISEAttrsH2, we are validating first if the user belongs to the "Developers" group and we are setting a boolean variable "isDeveloper" accordingly. The next part of the code validates if the user is either using EAP-TLS or is not a Developer. If that the case, we fetch the attribute mapping from the user-attribute table. If both statements are false, that means that a Developer is trying to connect without EAP-TLS (i.e. without presenting a digital certificate from a trusted CA), and we set the vlan attribute as 'CorpVlan', the SGT to a tag of 16 (0010 in hexadecimal) which is a restricted SGT in ISE, and a department as 'internal'. The stored procedure gets the authentication method from ISE as a variable, which is declared in the third position in the CREATE PROCEDURE statement. The configuration in ISE will be presented later in this document.

ISE Configuration

ODBC External Identity Source Configuration

This section covers the configuration of the ODBC integration in ISE, assuming that ISE and the network are configured properly and basic authentication of users is operational. Basic ISE and network configuration for AAA is out of the scope of this article.

The configuration in ISE to integrate the MySQL database using ODBC is relatively simple. These are the required steps to configure ISE: 

In Administration -> Identity Management -> External Identity Sources, click on the ODBC menu on the left panel and select '+Add':

Give a name to the Identity Source in the General tab, and provide the hostname or ip address where the MySQL database is. Provide the credentials of the user that was created to access the schema containing the user, group, attribute tables, and stored procedures (we used the user cisco). Select the database type as MySQL.

Next, configure the stored procedures ISE will call to retrieve information from the MySQL Database. In our example we are going to configure the Advanced Settings in order to send the Authentication Method as an additional variable when ISE fetches the user attributes.

In the Advanced Settings we selected Network Access -> EAP Authentication as the attribute to pass to the MySQL stored procedure. The name in the SP (Stored Procedure) must be the name of the variable declared in the code of the stored procedure that retrieves the user attributes. At this point, if we test the connection to the database, ISE should be able to connect and execute the five stored procedures in the MySQL server.

The last step is to configure the groups and attributes that will be available in ISE to configure authentication rules and profiles. 

In the attributes tab, select '+Add', and then 'Select Attributes from ODBC'. In the dialog box use a user that must be present in the users_table. Finally click "Retrieve Attributes" and mark the attributes you need in ISE. In our example we are selecting all attributes from the database.

In the Groups Tab follow the same steps performed to add attributes into ISE from ODBC. In this case you can use '*' to retrieve all groups in the groups_table.

 

ISE Authentication and Authorization Configuration

Authentication and Authorization is configured in ISE using Policy Sets. Within a Policy Set, you can define the rules that will determine the network access level, which is defined in an Authorization profile. Since we are gathering information from the MySQL database, the Policy-Set configuration in ISE is greatly simplified. The following images show the Policy-Set configuration in ISE and the Authorization Profile which is configured with the values retrieved from the MySQL database via the ODBC integration.

Policy Set Configuration:

Authorization Profile:

The images shown above present the configuration in ISE to grant network access using the attributes in the ODBC database.

The Policy Set "Corporate Policy Set" check uses the predefined "All_User_ID_Stores" sequence to authenticate the user. For our exercise, it is important to have a sequence that includes Certificate Authentication Profile when doing EAP-TLS in order to validate the certificate. The identity obtained from the certificate must match the username in the users_table. The configuration of other external identity sources and certificate authorization profiles is outside of the scope of this document.

Once the user is authenticated, the authorization rules are evaluated. In our configuration there is only one authorization rule that always matches for authenticated users. The result of this rule is an Authorization Profile called "ODBC_Authorization_Profile"

In the ODBC_Authorization_Profile configuration the VLAN and the SGT is assigned using the attributes retrieved from the MySQL database using the stored procedures.

This is an example how the configuration in ISE is simplified by leveraging an ODBC external identity source. Certainly, the corporate policy set could have been configured to check the user group membership and the authentication method to achieve the same outcome, however that would have required at least four authorization rules, and four authorization profiles, whereas leveraging the ODBC integration we only needed one authorization rule and one profile. For more complex business logics, dozens of rules and profiles can be consolidated in a single rule that leverage the attributes received from the database.

Verification and Troubleshooting

This section will provide the steps to verify the proper operation of the integration of the configuration presented in the previous sections.

In our exercise, when a valid employee or contractor connects with valid credentials independently of using digital certificates, they will be granted network access with the corresponding VLAN and SGT. For developers, they will be granted with the developers SGT only if they present a digital certificate using EAP-TLS. The most valuable place to verify the proper operation is in ISE RADIUS Live Logs. The following images show the detailed reports from RADIUS Live Logs and the CLI output from the switch connecting the user for each scenario.

Employee Access (EAP-TLS)

Contractor Access (PEAP-MSCHAPv2)

 

Developer Access (EAP-TLS)

Developer Access (PEAP-MSCHAPv2)

Notice that for all the scenario the Authorization Rule and the Authorization Profile are always the same, howver the VLAN and SGT change for each scenario. Employees get the CorpVLAN (Vlan 15) and SGT 4 while contractors are placed in the VLAN 16 and get SGT 5. For employees or contractors it doesn't matter what authentication method is used. Developers are always placed in CorpVlan; they get SGT 8 if they present a digital certificate, if not they get SGT 16 which has restrictions. These attributes were fetched from the MySQL database using the logic from the stored procedures.

Conclusions

This article provides the steps to configure ISE and MySQL server for ODBC integration. The configuration of the MySQL database was done using MySQL Workbench where tables and stored procedures were configured. 

The configuration in ISE followed the steps to configure the MySQL database as an external identity source, policy sets and authorization profiles to properly enforce the required network access for each use case. The business logic resided in the MySQL stored procedures and not in the ISE Authorization policies, which simplifies ISE configuration.

The last section provides the different validation points to verify and troubleshoot the proper operation of the ODBC integration in ISE.

[powelca]

One note that I would add for anyone looking to utilize this solution with TrustSec, it does not appear that you're required to send the Generation ID as a part of the attribute-value-pair (based on my limited testing). This is the number that occurs after the dash. It would be nice to have some clarification on that though. What happens if your database has a Generation ID of 14 and a change in ISE causes the Generation ID to increment to 15? 

[jaschnei]

Hi @powelca, Thank you for the comment!!

You can actually save the SGT value in the external database without the Generation/Revision-ID. ISE will take care of sending the right value to the switch. I tested this configuration in my lab:

SGT Table without Generation ID:

ISE Response to the switch after user authenticates:

Authenticated session with SGT value coming from ISE on the Access Switch: