Environment Information :Experienced this with CP 6.4.x and also applicable to earlier versions.
Symptoms : We have noticed authentication failure against MSSQL database when the password field data type is integer. We found the below alert in the Access Tracker even after entering the correct password.
Error Code: 216
Error Category: Authentication failure
Error Message: User authentication failed
Alerts for this Request -
RADIUS: PAP: CLEAR TEXT password check failed
Cause : ClearPass is expecting the password field data type in string to pass the Clear Text Password Check when we use protocol PAP for authentication.
Or
ClearPass is taking the password from client with data type as string and trying to validate it against the MSSQL DB. And the authentication fails if the data type is not string in the DB.
Resolution : We can convert the data type from integer to string when we query the MSSQL DB. So that the Clear Text password check will be passed for successful user authentication.
Answer : The below Filter Query will help you to convert password field data type to string, when you query the MSSQL DB for authentication.
SELECT CAST(password as VARCHAR(50)) AS User_Password FROM user_auth (nolock) WHERE username ='%{Authentication:Username}'
Note: In the above query the "password" is the name of password column and "user_auth" is the table name and username is the user id column name. You could modify the column and table name as per your design.
The Filter query should be added in the MSSQL authentication source. Navigate to Configuration >> Authentication >> Sources >> Select the created MSSQL authentication source >> Attributes and replace/add the query as shown below.
