Many a times we come across the situation wherein we are trying to create a subscription for a SSRS report, but it fails with a message as below.
I came across such a scenario recently.
I created a new datasource on my report manager and was able to make a successful connection with the database. I created a SSRS report and pointed it to my new datasource. The report ran good as well. But when I came to create the subscription I got the error.
Here it was:
Now, this problem looks like the report expects the user to provide credentials when it is viewed. When you create a subscription, it does not have the credentials to execute the report. SSRS requires a Login to connect to the datasource to process the report when subscription will be occurring at its scheduled time. It was required to store the credential securely into report server.
There are two approaches basically for storing the credential securely into report server. Either you can change the datasource properties at the report level or at the datasource level itself.
Approach 1: Storing the credentials at the report level
If you are having this issue for any report follow the steps below:
Step 1: Go to Report à Properties à Click on the Data Sources tab
Step 2: Click on A custom data source and choose option Credentials stored securely in the report server and provide credential information and further you can use option Use as Windows credentials when connecting to the data source.
Step 3: Click on apply button.
Now you are good to create a subscription.
Approach 2: Storing the credentials at the datasource level
We can even change the same at the datasource level.
Step 1: Open the datasource from the Data Sources at the report manager.
Step 2: Fill in the connection string
Step 3: Choose to connect using: Credentials stored securely in the report server. Put the credentials and use option Use as Windows credentials when connecting to the data source.
Step 3: Click apply.
Below is the screenshot as to how it would appear.
Now can you can to the desired report and choose the option of ‘a shared data source. You are good to go with creating a new subscription now.
My Two Cents:
· If you are worried about security, I would suggest changing the datasource properites on the report level rather than the data source level itself. (i.e approach 1 as mentioned above)
· If your report uses sub-reports also ensure that the sub-reports are using the data source with saved credentials, otherwise it won’t let you create a subscription on the parent report.