Intersoft Community - ClientUI - Problem with sqlreportviewer and multiple SSRS datasources

Problem with sqlreportviewer and multiple SSRS datasources

handy@intersoftpt.com — Fri, 10 Aug 2012 00:01:58 GMT

Hello Sumner,

What kind of security that you set? I think the problem also connected with the security that set in SSRS datasource. If our SQLReportViewer really still called other datasource report, we need to replicate this in our environment first. So, would you mind to brief or guide us in step by step in detail such as in screenshot or video? We just don't want to miss any scenario that make the issue comes again.

Regards,
Handy

Problem with sqlreportviewer and multiple SSRS datasources

sumner@ppg.com — Thu, 09 Aug 2012 07:25:16 GMT

After further investigation, what is happening is that the sqlreportviewer component is iterating through all available reports (issuing a FindObjectsRecursive to SSRS)

It then retrieves all the parameters for any other reports it finds and if any of those parameters are derived from a query, it tries to execute the query to get the parameters. If the user we are running at doesn't have security permissions to the underlying dataset then the entire process fails and we never see the original report.

We have tried setting EnableAutomaticListFetching="False" and CanUserShowReportList = "False" but this makes no difference at all.

The way I have found to get around the exception is to manually configure the report level security in SSRS to stop reports being visible to the sqlreportviewer - but, and this is a big but, this does not stop the underlying problem - we have many reports that use parameters driven by queries so even when I stop the actual exception message, sqlreportviewer is still running all my reports parameter queries even if I have no intention of running the report.

This is massively inneficient!

Is there a solution for this or does anyone know an alternative sql report viewer for silverlight that actually works?

Thanks.

Problem with sqlreportviewer and multiple SSRS datasources

sumner@ppg.com — Thu, 09 Aug 2012 04:36:26 GMT

Hi Handy,

I am trying to work around this issue today but this would appear to be something much more fundamental that a security issue. In the scenario I have described, there is no way that the sqlreportviewer component should even know about Database B let alone try to access it.

When I trace the actual calls made to SQL Server during the test run, I can see that there are calls made to try and find all reports and then it seems to try to access them in turn - why would it even do this? When I run any SSRS report from any other component, it checks if the report exists, opens it, get the parameters, get the data source, runs the report. The sqlreportviewer component first calls to get a list of ALL reports (regardless of which report I asked for) and then proceeds to try and open each report in turn.

Something is seriously wrong with the way this component is working.

Regards, Lee.

Problem with sqlreportviewer and multiple SSRS datasources

handy@intersoftpt.com — Wed, 08 Aug 2012 23:22:06 GMT

Hello Sumner,

Can you please guide me by showing a recording video how to set up your database in SSRS? I think there is a problem in security that he haven't handle. But we need your help to figure out the issue.

Regards,
Handy

Problem with sqlreportviewer and multiple SSRS datasources

sumner@ppg.com — Wed, 08 Aug 2012 17:53:14 GMT

I have a really strange problem with the sqlreportviewer control.

If I only have one datasource in reporting services, sqlreportviewer behaves exactly as expected.

For example;

I start with a completely new SSRS database

Add a single datasource called DataSourceA pointing to database A

Add a report called ReportA that uses DataSourceA

Run Report A from using sqlreportviewer and everything seems fine (report appears with data - no errors)

NOW THE WEIRD BIT

Add a new data source in SSRS called DataSource B pointing to Database B

Go back and run Report A and I get a security error telling me I cannot open Database B

Nothing else has changed except the addition ofi DataSource B

This is driving me mad and I have double/treble checked all the settings for the report renderer atc etc (and I don't believe it is this because the report initially works)

Why is sqlreportviewer trying to access DataSource B when absolutely nothing has told it to do so?

Any ideas?