SQL Server – Reporting Services – Invoking from .Net Application – Permission Errors

Background

We have provisioned Microsoft SQL Server Reporting Services and now testing it out by having a .Net Application connect to it, ask for a report to be ran and consume the report.

 

Errors

There are two sets of errors that we will look at.

We will like at both the errors that are exposed on the .Net Client code and the ones logged on the server.

 

.Net Client Error

  1. The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later

 

Reporting Services Back End Error

There are a couple of growing pains that we are experiencing and those are:

  1. Execute permission denied on the Object
  2. Permissions granted to user are insufficient for performing the operation

 

Indepth

Error – Execute Permission on Stored Procedure

Image

reportserverservice_log_20170124_0934_log

Tabulated

Error
webserver!ReportServer_0-5!20f0!01/24/2017-12:48:37:: i INFO: Processed folder ‘/ASSIST’
processing!ReportServer_0-5!20f0!01/24/2017-12:48:42:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘InstitutionList’. —> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘rptInstitutionList’, database ‘AssistDW’, schema ‘dbo’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
 processing!ReportServer_0-5!20f0!01/24/2017-12:48:42:: e ERROR: An exception has occurred in data set ‘InstitutionList’. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘InstitutionList’. —> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘rptInstitutionList’, database ‘AssistDW’, schema ‘dbo’.
 processing!ReportServer_0-5!20f0!01/24/2017-12:48:42:: i INFO: DataPrefetch abort handler called for Report with ID=. Aborting data sources …
processing!ReportServer_0-5!20f0!01/24/2017-12:48:42:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: , Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘InstitutionList’. —> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘rptInstitutionList’, database ‘AssistDW’, schema ‘dbo’.

 

 

Resolution

Grant select and execute permission on the specific Objects or Schema.

Sample

use [AssistDW]; GRANT EXECUTE on  SCHEMA :: [dbo] to [LAB\svcMarketing];
use [AssistDW]; GRANT SELECT  on  SCHEMA :: [dbo] to  [LAB\svcMarketing];

Error – Call to GetSystemPropertiesAction() :: Permissions granted to user ‘WebPoolUsr‘ are insufficient for performing this operation.

Tabulated

Tabulated

Error
library!ReportServer_0-11!263c!01/25/2017-12:24:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: , Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user ‘LAB\WebAppPool’ are insufficient for performing this operation.;

 

 

Image

reportserverservice_log_accessdeniedexception__20170124_0156pm_log

 

In our case, we granted permission at the Report Manager Home Level, but it was broken at the specific folder level

Broken Permission

Only the BUILTIN\Administrators have permission at the ASSIST Folder Level

roleassignment-folder-assist-20170125-1242pm

 

Revert to Parent Security

We chose to revert to the Parent Security.

Previously, we validated that things was good at the Parent Level.

roleassignment-folder-assist-reverttoparent-20170125-1243pm

After Reverting

roleassignment-folder-assist-afterrevert-20170125-1243m

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s