External SQL Authentication Source with ClearPass

Recently, I had a customer who had all of his user accounts in an external SQL database and we were looking for a way to authenticate against this external SQL authentication source with ClearPass. It is easy, to get this working and the following post demonstrates the configuration.

ClearPass supports MSSQL, Oracle and PostgreSQL out of the box. Support for MySQL can be added with the corresponding ODBC driver. This driver is not included due to licensing problems.

For this test, I use PostgreSQL, but the post is specific to the ClearPass part. The database part is not part of this post. If need specific instructions for a specific database type, leave me a comment and I can create one.

Add SQL Authentication Source to ClearPass

Before you can use a SQL authentication source in ClearPass, you need to add the database to ClearPass. To add the database to ClearPass log into the Policy Manager in ClearPass and go to “Configuration–>Authentication–>Sources” and click the “Add” link in the upper right corner:

External SQL Authentication Source - Add Authentication Source General
External SQL Authentication Source – Add Authentication Source General

On the first screen of the wizard, select the authentication source type. This is “Generic SQL DB”. To use the information from the database for the role selection process in an enforcement policy check “Use for Authorization” checkbox. Also, hand out a name to the SQL authentication source. You can also add a┬ábackup server to the configuration. In my case, I only have one database server. Click “Next” to configure the primary database server:

External SQL Authentication Source - Add Authentication Source Primary
External SQL Authentication Source – Add Authentication Source Primary

The “Server Name” is the IP or the hostname of the database. Make sure, that ClearPass is able to reach the database server and that ClearPass is an allowed source of requests on the database server.

Assign the “Database Name”, the “Login Username” and the “Login Password” for the database. Below those settings, select the “ODBC” driver. In my case, this is “PostgreSQL”. The last setting is the “Password Type”. And it is different than you would think in the first place. This password type defines the format or encryption, the password for all of your users is stored in the database. In my example, I use a simple MAC authentication and the password, which is the MAC address, is stored in “Cleartext” in the database. For other authentication methods, you can have the password also encrypted in the database. Just make sure, that ClearPass knows, which encryption you have selected.

Click the “Next” button, to get to the “Attributes” page:

External SQL Authentication Source - Add Authentication Source Attributes
External SQL Authentication Source – Add Authentication Source Attributes

On this page, you can create SQL queries to the database to get all the attributes you need. A very important one is the query for authentication. There is no need to name it like this, but it makes it easy to identify the query. The important thing is, that the password has to be in a column called “user_password”. In my case the column in the database is “mac” and in the select statement I change this to “user_password”. ClearPass use this column to check the user password from the radius request. For all other attributes, you are totally free, in regards to the name.

Afterward, click the “Next” button and the “Save” the SQL authentication source. You can now use the SQL authentication source in your services.

What database type would you prefer? MSSQL? Oracle? PostgreSQL? Or even MySQL? Leave me a comment and we can discuss all your questions around this post.

9 thoughts on “External SQL Authentication Source with ClearPass”

  1. Hi Florian,
    I am suing MSSQL server versoin 2017. And I can’t aable to connect to MSSQL server from ClearPass to run queries.

  2. Hi,
    many thanks for your time and I appreciate your support. My issue the ClearPass can’t run any SQL query on MSSQL server, so that I appreciate your support by some screenshots for required configuration on MSSQL server to be able to accept the SQL queries from ClearPass. “my issue with establishing session between ClearPass and MSSQL server to accept the queries from ClearPass”.

    Note: now I am using simple table username is “admin” and password is “password”. and many simply query “select from TestTable where username = “%{Radius:IETF:User-Name}” and i don’t have any problem with SQL query.

    • Hi Ahmed,

      Just to make sure, I understand you correctly. You are using MSSQL (the Microsoft one)? Which version are you using?
      And you are not able to connect to MSSQL from ClearPass to run your queries? Correct?

  3. Dear,
    many thanks for your time and tutorial to how to integrate with SQL server. i am using MSSQL server but while i am trying to integrate with it, i have face the following issue “MS-CHAP2-Response is incorrect” due to I can’t do any SQL query. please i need to fix this issue

    • Hi ahmed,

      Where do you get the error? While connecting to MYSQL or while authenticating users against MYSQL? Can you share the ClearPass configuration for the MySQL authentication source? You can send me the information via mail. This would make communication much easier. You can reach me via flo(at)flomain.de.

      BR
      florian

      • dear,
        many thanks for your response, kindly send me your E-mail to make communication much easier. And also i would like to inform you that I have created Database on MSSQL and then created 2 table, one for username and second one for passwords. And finally I enabled the Dot1x on Aruba controller to authenticate the users, the issue when I am trying to authenticate the users using the username and passwords which they are exiting in password column in table.

        Note: the authentication method is MS-CHAPv2 and the error in challenge to implement MS-CHAPv2 the error as the following “FAILED: MS-CHAP2-Response is incorrect”

        • Hi Ahmed,
          To my knowledge it is not possible to do mschapv2 against an MySQL database. But I will try to test this in my lab.

          • Hi Florian,
            I am trying to do the same above LAB but I created table with 2 column first one is username and second on is MAC address for it’s Laptop. and i want to use the MAC address as Password.
            I am using ClearPass and MSSQL server. the issue when ClearPass try to check password column it fail to get the value for that reason “FAILED: MS-CHAP2-Response is incorrect”.

            Note: I tested the query on MSSQL and it is working fine and get the password correct.

            BR
            Ahmed Magdy

          • Hi Ahmed,

            From my point of view, the password needs to come from the client. You can use the mac address, but then, the user needs to enter the mac address as a password or the access device needs to be configured to use the mac as the password.
            I have tested PEAP (outer method) with MSCHAPv2 (inner method) against MySQL and it is working. I think the problem in your scenario is not the MySQL database.
            Can you explain, how the mac address should be used as a password in your environment?
            If the user is not entering the mac address as a password, which password should the user enter, during username password request? Who is replacing the entered password with the mac address?

            BR
            Florian

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: