Uploading Report Error | Please contact your system administrator. (6632)

[khurram.bilal]

Hi,

I have a working report on local, the report calls an SP. We uploaded to the server and the admin connected the report to a datasource which is same datasource i have created on local.

When we run we get the following error, any suggestions to fix this. The error didn't say much - where we can find the specific error details? The following is from the log from JBoss.

2018-11-21 12:35:14,289 ERROR Validator,pool-8-thread-4:496 - Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: EXEC [dbo].[sP_NAME]2018-11-21 12:35:14,290 ERROR AsyncJasperPrintAccessor,pool-8-thread-4:321 - Error during report executioncom.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632)Arguments: at com.jaspersoft.jasperserver.api.security.validators.Validator.validateSQL(Validator.java:497) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:165) at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:111) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:429) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:570) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:961) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:912) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1840) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1159) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1088) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:983) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)2018-11-21 12:35:14,294 ERROR ErrorPageHandlerAction,default task-40:115 - Error UID bdb87b42-d6d3-4fe2-be4b-3dc10a49fb82com.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632)Arguments: at com.jaspersoft.jasperserver.api.security.validators.Validator.validateSQL(Validator.java:497) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:165) at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:111) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:429) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:570) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:961) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:912) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1840) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1159) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1088) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:983) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)[/code]

[hozawa]

You need to set the language to plsql to call stored procedure on JasperReports Server. SQL works on Jaspersoft Studio to call stored procedure but not on JasperReports Server.

[prusyn]

As you might notice in logs, your issue is thrown by SQL Validator. By default it does not allow some SQL queries for security purpose. You can edit security configuration options to allow specific SQL or disable the validation completely:

1 - The following regex fix should work for most cases.

In this file: webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties/validation.properties  

Validator.ValidSQL=^s*(?i)(withs+.*)?(select|call|exec(ute)?)s+[^;]+$[/code]

2 - Alternatively turn it off here: webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties  

security.validation.sql.on=false[/code]