SQL Server Reporting Services 2005 attempts to store credentials fail

Question

I'm trying to figure out which credentials I should be storing to enable use of Reporting Services' Data-Driven Subscriptions. Nothing I type in seems to work. It's all happening on localhost under XP-Pro SP3.

A little background:

I'm able to connect to the localhost server as a Database Engine in SQL Server Management Studio, using either Windows Authentication or the username 'sa' and a given password. However, trying to connect to localhost as a Reporting Services server using those same credentials fails with this error:

Cannot connect to localhost.
Additional information:
Unable to connect to the remote server (Microsoft.SqlServer.Management.UI.RSClient)
No connection could be made because the target machine actively refused it [IP]:80

In Reporting Services Configuration Manager, Windows Service Identity is set up like this:

Service Name: ReportServer
Service Account: LocalSystem
Built-in Account: Local System

Web Service Identity is set up like this:

ASP .NET Service Account: [Machine]\ASPNET

Database Connection:

Server Name: [Machine]
Database Name: ReportServer
Database Version: C.0.8.54
Server Mode: Native
Credentials Type: Service Credentials

The Service Status page is green and running.

The Report Server webpage is available and accessible; I can view and generate reports manually. However, I want to create a Data-Driven Subscription so that I can create multiple reports in one go. This is where I'm running into problems. When I click "Subscriptions", it refuses to let me set one up because

Data-driven subscriptions cannot be created because the credentials used to run the report are not stored, the report is using user-defined parameters values, or if a linked report, the link is no longer valid.

Fair enough, I haven't stored credentials. So I went to the "Properties" section and clicked "Data Sources". By default, the report uses "A shared data source". Switching to "A custom data source" requires me to select credential types. Nothing I enter into this seems to produce a result that will allow me to get past the "Data-driven subscriptions cannot be created..." message and create the subscription.

Anyone know how I can fix this?

Answer 1

That error with subscriptions implies that at least one of the data sources you're using (either for your report or for the subscription data) doesn't have stored credentials.

For example, normally I might be using Windows Integrated Security to connect to a data source, but if I want to store the credentials, I will enter MyDomain\SomeAccount and its Windows password in the box on the Data Source screen, and tick the checkbox to indicate it's Windows security.

If this is done for all the data sources, then you should be able to create a subscription. You don't need to use a custom data source if you're happy to store credentials in the shared data source.

Answer 2

I think you need to store credentials for the data source as well as for the execution account in the SSRS Configuration tool, especially if you're using Local System. Rob is correct about how to set the credentials for the data source, just modify the shared data source directly.