How to authenticate Guest users from one ClearPass server on another using appexternal account

By esupport posted Mar 07, 2016 03:33 PM


We might have a use case where an organization might have 2 ClearPass servers which are not in cluster but we want users with guest accounts on one ClearPass to be able to authenticate on the other. That way they need not re-create their accounts and continue using their existing guest account even when authenticating across a different ClearPass server. 


We need to make use of the "appexternal" database account on the external ClearPass server in order for us to connect to its database. Using that account we need to create an authentication source of type SQL on the host ClearPass server and map that authentication source also to our service that handles the authentication for those users.



To start with we need to set a password for the "appexternal" account on the external ClearPass server.

Appexternal is a read-only database account which allows us to read values from the DB. The password for that account can be specified on the server under "Cluster-Wide Parameters" on the Server Configuration page.

Once that is done we need to move to the host ClearPass server which is going to authenticate users. We need to create an authentication source of type SQL using the IP address of the external ClearPass server under server name. The port is going to be 5432 the postgresql port. The username is going to be appexternal. The password is the password as specified on the external server. Please make sure that you only allow traffic from the host ClearPass server to port 5432 on the external ClearPass server on your firewall. 



Once that is done we can add the filters similar to the [Guest User Repository]

SELECT user_credential(password) AS User_Password,
       CASE WHEN enabled = FALSE THEN 225
            WHEN ((start_time > now()) OR ((expire_time is not null) AND (expire_time <= now()))) THEN 226
            WHEN approval_status != 'Approved' THEN 227 
            ELSE 0
       END AS Account_Status, sponsor_name,
       CASE WHEN expire_time > now() THEN CAST(EXTRACT(epoch FROM (expire_time - NOW())) AS INTEGER) 
            ELSE 0
       END AS remaining_expiration, expire_time::timestamp
FROM tips_guest_users           
WHERE ((guest_type = 'USER') AND (user_id = '%{Authentication:Username}') AND (app_name != 'Onboard'))

and the attributes similar to ones in [Guest User Repository]  like the expire_time, sponsor_name, remaining_expiration.


We can also add the filter for authorization similar to [Guest User Repository]  


       CASE WHEN expire_time is null or expire_time > now() THEN 'false' 
       ELSE 'true'
       END AS is_expired,
       CASE WHEN enabled = true THEN 'true' ELSE 'false' END as is_enabled
FROM tips_guest_users           
WHERE ((guest_type = 'USER') AND (user_id = '%{Endpoint:Username}') AND (app_name != 'Onboard'))

and attributes is_expired, is_enabled as Boolean.


We need to add a custom SQL query for fetching Role attributes and Email address if needed, to authorize based on guest account role.

select t2.tag_value AS Guest_Role_ID, t21.tag_value As Email
FROM tips_guest_users g
LEFT JOIN tips_guest_user_tag_mappings t1 ON ( = t1.instance_id)
LEFT JOIN tips_guest_user_tag_mappings t12 ON (
LEFT JOIN tips_tag_values t2 ON (t1.tag_value_id =
LEFT JOIN tips_tag_values t21 ON (t12.tag_value_id =
WHERE (g.guest_type = 'USER')
  AND (g.user_id = '%{Authentication:Username}')
  AND (t2.tag_id = (SELECT id FROM tips_tag_definitions WHERE name = 'Role ID' AND entity_id = (SELECT id FROM tips_dic_internal WHERE dic_prefix = 'GuestUser')))
  AND (t21.tag_id = (SELECT id FROM tips_tag_definitions WHERE name = 'Email' AND entity_id = (SELECT id FROM tips_dic_internal WHERE dic_prefix = 'GuestUser')))


The Guest_Role_ID and Email are being fetched using the above SQL query which can be used as Authorization attributes in our enforcement. We need to create the attributes  Guest_Role_ID, Email as shown in the screenshot below


Once this is done map the created authentication source in the respective service.




We can also enable authorization on the service and add the external source created as an authorization source also. 

After all this is done you should be able to authenticate guest users from one ClearPass server on the other ClearPass server and assign them different roles based on their guest account's role id.



Create an account on the external ClearPass server and authenticate across the host ClearPass server


Guest Account on External server


Authentication successful on the host ClearPass server

Authorization Attributes Role ID, Email fetched from the external server which can be used in our enforcement





1 comment


Feb 04, 2019 01:04 PM


I used this SQL query long time back with ClearPass v6.5.7 to fetch authorization attributes within the same server, and it is working perfectly!


However, with ClearPass v6.6.10 and v6.7.8 I got the following error:

The filter has been saved but has the following error: Invalid SQL syntax - ERROR: relation "tips_guest_user_tag_mappings" does not exist Position: 136


What was changed?