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 |
vlans_table
The content and structure of this table is the following:
| vlan_id | vlan_name |
| 1 | CorpVlan |
| 2 | VLAN0016 |
sgts_table
The content and structure of sgts_table table is the following:
| sgt_id | sgt |
| 1 | cts:security-gorup-tag=004-5 |
| 2 | cts:security-gorup-tag=005-12 |
| 3 | cts:security-gorup-tag=008-1 |
| 4 | cts:security-gorup-tag=010-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 SGT must be present in ISE (TrustSec Components) and the value in the MySQL database must match to an existing SGT in ISE.
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
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
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.