ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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 JS 7.8 are failing with 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, fill the required parameters and run the report
  4. The report runs successfully.
  5. Perform the same steps using a 7.8 JS

Expected result: The report runs successfully 

Actual result: 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)

Cause

This is caused by DE60486

 

Environment

Clarity 15.9.2 with Jaspersoft 7.8

 

Resolution

Rego SaaS & On Premise Customers: 

  • Stop the Jaspersoft Tomcat Services 
  • Navigate to \webapps\reportservice\WEB-INF\classes\esapi and backup the below mentioned files and keep it outside Jaspersoft installation folder. 
    • security.properties
    • validation.properties
  • Replace with the attached files to the path \webapps\reportservice\WEB-INF\classes\esapi
  • Delete the tomcat’s temp and work directory
  • 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: 

  • The issue has been resolved and communication shared in Status Page.

Additional Information

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

Errors on 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:

Attachments

1618578625290__validation.properties get_app
1618578616088__security.properties get_app