Security

Can anyone offer help or even an example of how to setup an Authentication source in ClearPass Policy Manager that points to an external Microsoft SQL database for a MAC Authentication service?

 

I have created a new Authentication source.

Set the Type as Generic SQL DB

I put the connection details of my MS SQL server into the Primary tab

The Attributes tab is the section I am very unclear about.

On the Attributes tab, I created one Filter called Authentication.

However, I have NO idea how to write the SQL query in the Filter query section. I know the name of my DB, table, and field that contains MAC addresses, but I'm looking for someone that might have a working example of an SQL Filter Query.

I confess, filter query is not my strong suit either.  However, here is an example of a similar configuration using the PostgreSQL DB of Amigopod as the external DB used in CPPM.  Also, check pages 122-125 of the CPPM 5.1 User Guide.

 

Example:

Configure SQL Authentication source on CPPM

• Navigate to Configuration » Authentication » Sources
• Click Add Authentication Source
• Select Generic SQL DB.
• On Primary tab Set Server Name to Amigopod IP address
• Set Port number to 5432
• Set Database Name to amigopod
• Set Login Username and Password
• Set ODBC Driver to PostgreSQL
• Configure appropriate SQL filter, for example, the following is derived from the local user repository:

 SELECT password AS User_Password, CASE WHEN enabled = 0 THEN 225 WHEN ((expire_time is not null AND expire_time <= (select extract (EPOCH FROM now())))) THEN 226 ELSE 0 END AS Account_Status FROM useraccount WHERE username = '%{Authentication:Username}'

You can try using something simple such as mentioned below for authentication filter,

 

I'm in a similar position here. I'm running CP 6.3.3 and my external DB is MySql. I've written a number of stored procedures that I call from my freeradius (2.2.5) service to obtain, for example the vlan to drop a device into after a successful mac-authentication. In FreeRadius I'd do :-

 

               update control {
#
# Tmp-String-0 = string representation of vlan id for given device type.
#
                  Tmp-String-0 := "%{sql_master:CALL get_vlan_id('%{NAS-IP-Address}','%{User-Name}')}"
               }

.... where control:Tmp-String-0 either contains 0 or a numeric VLAN string value. The SP

 

1) Determines the device type based upon the mac address stored in User-Name

2).Figures out the location based upon the NAS-IP Address of the switch

3).Figures out what vlan to return based upon the location/device type

4).Returns a string representation of the numeric vlan to be used.

 

 

Later on I'd do

 

update reply {
                     Tunnel-Private-Group-ID := "%{control:Tmp-String-0}"
                  }

......

Ideally I'd like to use my existing SPs in calls from clearpass. Can I do this? When I'm setting up the filter query, what would the format be?

 

Also, what clearpass attrributes do I use instead of the %{NAS-IP-Address} and '%{User-Name} arguments to pass to the procedure.

 

Rgds

A

 

 

 

 

You'd want to build that logic into your enforcement policy instead of SQL queries.

In your custom SQL auth source, create attributes that reference the data in the database. You then use those attributes along with location and other CP data in the enforcement policy.

o.k. Sounds reasonable, but that would mean reinventing something that I'm already using.  I'm currently running a production Freeradius  service and planning a gradual migration over to clearpass. The FR service uses stored procedures extensively and being able to use these in clearpass would simplify things greatly. Taking a step back, can I call stored procedures and process a returned value from it through the filter interface?. I'm happy to implement the same functionality in clearpass speak if I have to, but if i can use what's already there, I'd prefer to do that.

 

Rgds

Alex

 

Hi Toim,

Just as an update, I've implemented your recommendations .... seeing as I can't call stored procedures in clearpass :-((

 

Rgds

Alex