SSRS 2012 Subscriptions - Credentials used to run this report are not stored

Question

I'm trying to create a subscription on an SSRS 2012 report, working from a SQL Server 2008 database. From what I've read elsewhere, if I store the credentials in an individual report / stored datasource in the Report Manager, I should be able to create a subscription.

Unfortunately, for some reason this isn't happening for me. I have tried storing the credentials in a single reports, and also creating a datasource with stored credentials, with no luck (the 'Test Connection' button shows a successful connection). Please see screenshots.

Can anyone suggest what else I may still need to do to successfully create subscriptions?

Thanks, Gavin

Answer 1

The problem you are having is that the credentials are not stored in the report itself. You will need to go into the report and right click on the data source and oull up properties then you will need to have a SQL Account that has rights to the data and then open the credentials tab and enter the username and passowrd in the blanks provided then be sure to click remember credentials then you should be able to set-up a subscription with no problem.

Since youa are using subscriptions the report itself needs a user name to runit as. WHat you are trying to do here is create a data source for the folder to use. But the data source being used by the report is a seperate connection.

Answer 2

I both embedded the data in the report as suggested by Wes Palmer (thanks) and used a specific SQL server account rather than integrated security. I then needed two further steps:

1.) Add a 'Report Server' entry into the msdb 'syscategories table as follows:

sp_configure 'allow update', 1
go
reconfigure with override 
go 
SET IDENTITY_INSERT syscategories ON
go 
insert into syscategories(category_id, category_class, Category_type, Name)     values(100, 1, 1, 'Report Server') 
go 
SET IDENTITY_INSERT syscategories OFF
go 
exec sp_configure 'allow update', 0 
go 
reconfigure with override 
go 

2.) I set the SSRS Service account to a domain admin account.