Friday, January 6, 2017

SSRS - Subscription can't be created or edited

A Reporting Services subscription is a configuration that delivers a report at a specific time or in response to an event, and in a file format that we specify. Subscriptions can be used to schedule and automate the delivery of a report and with a specific set of report parameter values. 

"Subscriptions cannot be created because the credentials used to run the report are not stored or if a linked report, the link is no longer valid" is the very generic error in SQL Server Reporting Services. This error occurs because SSRS requires a Login to connect to the data source to process the report whenever subscription will be occurring at its scheduled time.
If you are creating your report with the shared datasource and your datasource is built up on the Use Windows Authentication (Integrated security) as given below image then you will not able to create subscription on that report and will face the above error.
Shared Data Source is not allowed in Report Subscription and before creating subscriptions, scheduled report needs to know which credentials we want to run with, when producing the reports.
If you want to create report subscription on the report then I would recommend you to store the credential security into report level and use embedded connection instead of shared data source reference. To sort out this issue, we are doing the following settings step by steps.
1.In development studio, right click on the Data Sources and choose Data Source Properties from report data as given below:

2.In Data Source Properties, checked Embedded connections, Type Microsoft SQL Server and click on Edit button as given below:

3.After click on Edit button, it will open Connection Properties window where you need to fill Server name, checked Use SQL Server Authentication and fill user credentials, select or enter a database name and click on test connection. If Test result alert shows succeeded then click on OK button as given below:
4.Click on OK button on Connection Properties window. In Data Source Properties window’s Credentials tab, you can see that your report connection is set on Use this user name and password as given below:

5.Click on OK button and deploy your report on the report server again and open the data source of the report and set custom data source as given below:

6. After testing the connection string, click on Save button. Choose the Subscription, where you can see the enabled New Subscription.

After following the all the above steps, you are able to create "New subscription" or "New Data-driven Subscription" with the help of report manager.

No comments:

Post a Comment

Popular Posts