SetDataSourceCredentials for ReportViewer works only for first page of report

Question

I just deployed my first SSRS Report on Report Server and called it from application using ReportViewer (had to use it). Problem is, it always asks for database username and password before the execution of report. No matter where I set database credentials (on report server, in report) it always asks me for them. So I tried to set data source credentials programmatically like this:

                    DataSourceCredentials cred = new DataSourceCredentials();
                    ReportDataSourceInfoCollection dataSource = ReportViewer1.ServerReport.GetDataSources();
                    cred.Name = dataSource.First().Name;
                    cred.UserId = "sa";
                    cred.Password = "admin_pass";
                    ReportViewer1.ServerReport.SetDataSourceCredentials(new DataSourceCredentials[] { cred });
                    ReportViewer1.ServerReport.Refresh();

and first page of report loads just fine. Unfortunatelly second page asks for credentials again, and if I don't type them again, report viewer shows an error like dataSource does not exist:

Cannot create a connection to data source 'DataSource2'. (rsErrorOpeningConnection)

Does anyone have this problem? How to solve it? Thanks!

Answer 1

When are you setting the credentials? Make sure you add it as a ReportLoadedEventHandler to ReportLoaded.

Answer 2

First, is there a data source called DataSource2 in your project? I've run into a situation before where an extra data source that I was not aware of was automatically created by the Visual Studio report designer for some reason (but was not visible under the Shared Data Sources folder).

So you'll want to right-click on your report in Visual Studio and click View Code to verify the data sources. In my case all I had to do was delete any references to the non-existent data source and everything worked as expected.

Otherwise, take a look at the SSRS logs to get a more detailed error message. The logs should be in a location similar to C:\Program Files\Microsoft SQL Server\MSRS11.SQLPROD1\Reporting Services\LogFiles

What's the detailed error message?

Answer 3

Your datasource is probably configured to prompt for credentials. I think you should just specify a specific user name and password in the report. See screenshot below: