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:
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:
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:
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.
Good day, how do you specify a MSSQL instance in the server name?
Hi Rosa,
From my point of view, the instance is specified with the normal jdbc syntx like this:
db-domain-name\instance-name
have you tried this?
BR
Florian
This is not working: From Support:
I validated the Engineering notes (CP-21515) and found out symbol “\” was allowed in the older ClearPass versions but denied from later 6.7 ClearPass version.
hi Brian,
thanks for the information. Did you get the information from support, how to specify the instance if the “\” is not allowed?
BR
Florian