How To Save Credentials In SSRS Local Report

Question

I'm trying to run a report that can connect to the database and run. I've added multiple datasets to the data source that I created inside the report. I am trying to run this programmatically, inside WebApi and return a PDF. When I render the report I get an error that says:

ReportProcessingException: Plant

Plant is one of my DataSet names inside the report, So I went to the Query in Plant and ran it inside Sql Server Management Studio without any errors.

Next I decided to look at the XML on the report and it looks like this:

  <DataSources>
    <DataSource Name="DataSource1">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=.\SomeInstance;Initial Catalog=SomeDataBaseName</ConnectString>
        <Prompt>Specify a user name and password for data source DataSource1.</Prompt>
      </ConnectionProperties>
      <rd:DataSourceID>f4a33a86-c8f5-4fef-8e92-903b536c45dc</rd:DataSourceID>
      <rd:SecurityType>DataBase</rd:SecurityType>
    </DataSource>
  </DataSources>

And I noticed that there are not any credentials for the database.

I also noticed that every time I close, reopen, and run the report it asks for the database credentials.

So I decided to try to add the credentials in the connection string like:

 <ConnectString>Data Source=.\SomeInstance;Initial Catalog=SomeDataBaseName;User Id=SomeUser;Password=SomePassword</ConnectString>

ending up with the same result as before.

So then I decided I would just delete all but one of the data sets and see if that worked, I had the same error only with the remaining data set.

How can I get the report to connect to the database and run using LocalReport, and not ServerReport?

Marko Radivojević Marko Radivojević · Accepted Answer · 2020-04-16T22:15:30

You can create new report parameter called, for instance, ConnectionString.

When calling the report from your code, pass the connection string to the parameter, or instead of it, just set parameter's default value inside report.

In the report set this parameter as DataSource's Connection string.

How To Save Credentials In SSRS Local Report

1 Answers