In this article, I would like to walk through the process of integrating the Microsoft SQL server with the Cisco Identity Services Engine (ISE). This is a recent piece of work that I have been exploring and not one that is very well documented at the time of writing this article.

The motivation for writing about this topic came about when a customer needed to integrate the two devices to help the business streamline its authorization policies within ISE. By integrating MS SQL with ISE and making use of the Open Database Connectivity (ODBC), ISE can be configured to leverage specified attributes in authorization policies and profiles to provide the endpoint/user with the correct level of access to a network. Specifically being able to specify attributes in the authorization profile is great as this allows the administrator the ability to streamline their policies. I won’t discuss too much more about this use case because it has already been articulated rather well here. However, what I intend to do is cover the step-by-step process required to get this solution up and running. Furthermore, I have included a video session for those who prefer watching as opposed to reading.

Assumptions

• This article assumes that the reader is familiar with MS SQL and identity-based networking using Cisco ISE
• This article assumes that the reader is familiar with the switch configuration required and therefore this will not be covered in this article
• This article assumes that MS SQL and ISE are configured with the initial configuration

Devices used in this Demonstration

• MS SQL Server 2019
• Cisco ISE 3.0
• Cisco Catalyst 3560-X Switch
• x2 Endpoints for Testing
• x1 Management Machine with MS SQL Server Management Studio 18

Topology used in this Demonstration

Configuration Steps Overview

1. Install MS SQL and ensure that TCP 1433 is open between ISE and the server
2. Start the MS SQL server service using the SQL Server Configuration Manager
3. Using SQL Server Management Studio (SSMS) or any other SQL editor tool, log in using Windows authentication and create a new SQL authentication account with the correct permissions. This will be used for the ISE integration
4. Change the SQL server authentication settings to ‘SQL Server and Windows Authentication Mode’
5. Create a new table and populate the fields or use an existing one for the following:
   1. Device MAC addresses
   2. MAB Computers Group
   3. Attributes
   4. User and Group Mappings
6. Create Stored Procedures for the following:
   1. Username retrieval
   2. Group retrieval
   3. Attribute retrieval
7. Add the MS SQL server as an ODBC External Identity Store and ensure that the relevant fields are populated
8. Test the connection to the server
9. Create an authorization profile to match the VLAN attribute
10. Create a new policy and specify the authentication and authorization policy. Assign the authorization profile created in step 3 to the authorization rule
11. View authentication results in the RADIUS live logs

1. Install MS SQL and ensure that TCP 1433 is open between ISE and the server

Install MS SQL in your environment and test reachability from ISE to MS SQL if access control is configured in your environment. Ensure that TCP 1433 is permitted from ISE to the MS SQL server.

If you are following along in a lab environment, you can download the MS SQL 2019 evaluation version here.

2. Start the MS SQL server service using the SQL Server Configuration Manager

Search for ‘SQL server configuration manager’ and ensure that ‘SQL Server’ is in a ‘Running’ state.

3. Using SQL Server Management Studio (SSMS) or any other SQL editor tool, login using Windows authentication and create a new database and SQL authentication account with the correct permissions. This will be used for the ISE integration

Expand the top-level SQL Server to expose ‘Databases’. Right-click databases and create a new database called ‘ISE-DB’.

Click on the database once logged in to expand the folders. Find and expand ‘Security’ and right-click on ‘Logins’. Click ‘New Login’ to create a new user.

General Settings

• Specify the login name (This will be the username that will be used in ISE)
• The login name a password (This will be required in ISE also)

Server Roles

• Select ISE-DB as the mapped database for this user and change the membership to match the roles listed in the screenshot below and press ‘OK’ once complete

4. Change the SQL server authentication settings to ‘SQL Server and Windows Authentication Mode’

In the object explorer, right-click on the SQL server and select ‘Properties’. Select ‘Security’ and under ‘Server Authentication’ select ‘SQL Server and Windows Authentication Mode’. Once done click ‘OK’ to exit.

5. Create new tables

In the Object Explorer, expand ISE-DB to expose ‘Tables’. Right-click on ‘Tables’ and select ‘New’ followed by ‘Table. Create the following four tables as shown in the screenshots below.

Groups Table

ISE_Users Table

Right-click on ‘user_id’ and set it to Primary Key. The password field will not be used in this demonstration but has been created should you decide to authenticate endpoints/users with their passwords. Therefore, you can either select ‘Allow Nulls’ against passwords if you don’t want to populate this field or you can leave it as per the screenshot and populate the field with dummy info.

User_Attributes Table

User_Groups_Mapping Table

Populate the Tables

Once the tables have been created, populate the tables with the following entries.

Note: To populate the tables, right-click on each one and select ‘Edit Top 200 Rows’.

In the ‘Usernames’ column enter the device mac addresses. The ‘user_id should auto-populate and if you have allowed nulls on the password field then you can leave ‘password’ blank if you aren’t performing password authentication.

To ensure that the attributes match the created users, match up the user_id where necessary. In the above example, user_id 5 has an attribute value pair of vlan, 30. Similarly, user_id 6 has an attribute value pair of vlan, 60.

Lastly, map the ‘user_id’ to the ‘Group_ID’.

Before we finish with the tables, we need to create a Foreign Key Relationship with some of the tables so that the data can be mapped accordingly.

Starting with the ‘User_Groups_Mapping’ Table, we need to form a relationship with the Table ‘Groups’. To do this right-click on ‘dbo.User_Groups_Mapping’ and select ‘Design’. Once loaded, next to ‘Group_ID’ right-click and select ‘Relationships’. Under ‘Tables And Columns Specification’ add a relationship mapping between the ‘User_Groups_Mapping’ and ‘Groups’ tables as shown in the screenshot below.

So that the referenced rows in the child table are updated when the parent table is updated and similarly deleted, under ‘Table Designer’ expand ‘INSERT and UPDATE specification’ and set both the Delete Rules and Update Rule to Cascade and close once complete.

Create another FKR for mappings between ISE_Users and follow the same relationship process to add relationships for ISE_Users user_id and User_Attributes user_id. Ensure that the INSERT and UPDATE specification is also set to Cascade for both the Delete and Update rules.

We also need to create an FKR for the Attributes and ISE_Users as shown in the screenshot below.

6. Create Stored Procedures

Stored procedures are required so that the MAC address, groups and attributes relevant to the mac addresses in this can be retrieved by ISE.

In the Object Explorer expand ‘ISE-DB’ > ‘Programmability and right-click ‘Stored Procedures’ > ‘New’ > ‘Stored Procedure’. Name the first procedure ‘ISEAuthUserPlainReturnsRecordset’ and enter the following:

CREATE PROCEDURE [dbo].[ISEAuthUserPlainReturnsRecordset]
 @username varchar(255)
AS
BEGIN
 IF EXISTS( SELECT username
 FROM ISE_Users
 WHERE username = @username)
 SELECT 0,11,'Valid User'
 FROM ISE_Users
 WHERE username = @username
 ELSE
 SELECT 3,0,'odbc','ODBC Authen Error'
END

Once complete, ensure that you save and execute your script.

Create another procedure to fetch the groups and enter the following:

CREATE PROCEDURE [dbo].[ISEGroupsRetrieval]
 @username varchar(255), @result int output
AS
BEGIN
 if exists (select * from ISE_Users where username = @username)
 begin
 set @result = 0
 select Group_Name from Groups where group_id in (select group_ID from User_Groups_Mapping where User_Groups_Mapping.USER_ID IN (select USER_ID from ISE_Users where username=@username ) )
 end
 else
 set @result = 1
END

Create another procedure to fetch the attributes and enter the following:

CREATE PROCEDURE [dbo].[ISEAttrsRetrieval]
 @username varchar(255), @result int output
AS
BEGIN
 if exists (select * from ISE_Users where username = @username)
 begin
 set @result = 0
 select attribute_name , attribute_value from user_attributes where USER_ID in(SELECT USER_ID from ISE_Users where username = @username)
 end
 else
 set @result = 1
END

7. Add the MS SQL server as an ODBC External Identity Store and ensure that the relevant fields are populated

In Cisco ISE navigate to Administration > External Identity Sources > ODBC and click ‘Add’.

Populate the fields as shown below.

Note: Ensure that the fields are populated with entries matching your environment.

To fetch attributes and Groups, enter a username and press ‘Retrieve Attributes/Groups’ under each tab. When you have selected all relevant attributes, press ‘OK’.

Save the configuration when completed.

8. Test the connection to the server

Under the ‘Connection’ tab in the ODBC server that has just been added, click ‘Test Connection’ to verify connectivity to the MS SQL server. If successful, you should see a similar output to the screenshot below.

9. Create an authorization profile to match the VLAN attribute

In Cisco ISE, navigate to ‘Policy’ > ‘Policy Elements’ > ‘Results’ > ‘Authorization’ and click ‘Add’ to add a new Authorization Profile. This will be used to fetch the VLAN attribute from the MS SQL server.

Under the ‘Common Tasks’ configure the same settings as shown in the screenshot below.

When done, save the Authorization Profile.

10. Create a new policy and specify the authentication and authorization policy. Assign the authorization profile created in step 3 to the authorization rule

Navigate to ‘Policy’ > ‘Policy Sets’ and configure your policy set as shown in the screenshots below.

Note: The policy set created in this demonstration is basic for the purpose of testing. If you are configuring this in a production environment, it is recommended that you configure a more stringent policy set.

Ensure that the Authorization result matches the Authorization Profile created earlier. Once completed, save the policy.

11. View authentication results in the RADIUS live logs

With everything now in place, you should be ready for the newly configured policy against the devices specified in the MS SQL database. For demonstration purposes, I have included the output of my tests to show you what should be expected in ISE and on the switch.

Interface    MAC Address    Method  Domain  Status Fg Session ID
----------------------------------------------------------------------
Gi0/5        <omitted>     DATA    Auth      C0A86F0200000040067C2CF5
!
Server Policies:
          
           Vlan Group:  Vlan: 30

Method status list: 
       Method           State 

       mab              Authc Success

Video Demonstration