Clarity PPM Custom reports fail with error "6632" post Jaspersoft 7.8 upgrade
search cancel

Clarity PPM Custom reports fail with error "6632" post Jaspersoft 7.8 upgrade

book

Article ID: 212734

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Custom Studio reports in Jaspersoft (JS) 7.8 are failing with the error: "An error has occurred. Please contact your system administrator. (6332)" due to SQL validations when SQL starts using WITH & SELECT clause. This is happening since the Jasper Server has been upgraded to 7.8.

STEPS TO REPRODUCE 

  1. Create a custom report query that uses a WITH & SELECT clause prior to upgrade to Jaspersoft 7.8
  2. Home > Personal > Advanced Reporting.
  3. Open the report
  4. Fill the required parameters and run the report
  5. The report runs successfully.
  6. Perform the same steps using a 7.8 JS

Expected result: The report runs successfully 

Actual result: Receive error: 2021-04-16T12:18:42,509 ERROR Validator,pool-7-thread-1:540 [] - Validation unsuccessful. Failed evaluating default rule 'Validator.SQL_VALIDATION_PATTERN', no matches found
2021-04-16T12:18:42,510 ERROR AsyncJasperPrintAccessor,pool-7-thread-1:321 [] - Error during report execution
com.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632)

Environment

Clarity 15.9.2 with Jaspersoft 7.8

 

Cause

This is caused by DE60486

 

Resolution

Rego SaaS & On Premise Customers: 

  1. Stop the Jaspersoft Tomcat Services 
  2. Navigate to \webapps\reportservice\WEB-INF\classes\esapi. 
  3. Back up the below-mentioned files and keep them outside the Jaspersoft installation folder. 
    • security.properties
    • validation.properties
  4. Replace the files with the attached files in the path \webapps\reportservice\WEB-INF\classes\esapi
  5. Delete the tomcat’s temp and work directory
  6. Start the Jaspersoft Tomcat Services 

Note: Ensure to check the file permission before making the changes and ensure its same post file changes 

GCP SaaS Customers: 

Additional Information

There is a change in the SQL Validation setting which caused this issue, additional information on the SQL validator settings: Change SQL Security Validation Settings

Errors in the log files you will see:

com.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632) Arguments: at com.jaspersoft.jasperserver.api.security.validators.Validator.newSecurityException(Validator.java:552) at com.jaspersoft.jasperserver.api.security.validators.Validator.validateSQL(Validator.java:541) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.validateSQL(JRTimezoneJdbcQueryExecuter.java:219) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:165) at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:115) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:584) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:959) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:181) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:910) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1857) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1162) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1103) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:981) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834)


You can also confirm the query through your JDBC connection and you will see the same error: