SSRS Data Driven Subscription Troubleshooting

Microsoft SQL Server Reporting Services (SSRS) 2008 R2 will only allow data driven subscriptions under certain conditions, including data source security and server configuration options.   SSRS Subscriptions always work great and easy in the demos and training classes.  The following is the list of requirements to actually get them to work with in a data driven environment.

1. You must have SSRS configured to run with an ‘Execution Account’
2. Data Sources must not have your Data Sources folder hidden with the option ‘Hide in tile view’.  If you check that box, you will be unable to select a data for your subscription.
3. Data Sources must have stored credentials.  Without stored credentials, you cannot even create subscriptions.
4. The stored credentials must have access to every remote data source including databases and OLAP cubes.
5. Reference:  http://www.bidn.com/blogs/MikeDavis/ssis/147/ssrs-subscriptions-cannot-be-created-because-the-credentials-are-not-stored
6. The report cannot contain user paramters like User!UserID.  See:  http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/94ad2583-4591-4d28-add3-629b9565c27d
7. Make sure “Use as Windows credentials when connecting to the data source” is ticked.
8. When you store the credentials, you cannot check the box beside:  “Impersonate the authenticated user after a connection has been made to the data source.”     If you do check that box after a subscription has been created, then you will get a yellow exclamation mark on your subscription page and the following error:
   “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.”
   Yes, that is the error message I got this morning which led me down all these paths and back to taking notes on my blog.
9. The credentials used must have access to the Report Server and has RSExecRole access.  It must also have permission to log in locally to the server.

References: http://msdn.microsoft.com/en-us/library/ms160330.aspx

With all of that in mind, I finally decided to create a set of data source connection files with stored credentials.   All of these conditions basically mean full access to every data source and the server, so I put these in a subfolder named ‘Subscription Data Sources’ which is restricted to Administrators in SSRS. Summary:

• Most data sources will have the ‘Impersonate’ box checked in order to pass user credentials.
• Subscription data sources will NOT have the ‘Impersonate’ box checked.

Since the data sources are different, that means I needed a different copy of the reports so I created a ‘Monthly Subscriptions’ folder for reports.

1. This folder is restricted only to SSRS Admins.
2. To repoint the data sources, click the down arrow by the report name, then click Manage.
3. Navigate to the Data sources tab.
4. Beside each Data Source path, click the ‘Browse’ button and navigate to the ‘Subscription Data Sources’ folder created above.  Be patient, the browse dialog is very slow.
5. Repeat for every data source.
6. Click Apply at the bottom of the page.

On the Subscriptions tab, you should no longer have exclamation marks and error messages.  You can now create a ‘New Data-driven Subscription’

Reference for creating a data driven subscription: http://msdn.microsoft.com/en-us/library/ms156012.aspx

EDIT:  Another point I just learned is that if you are using Shared DataSets, you need a copy of the datasets, each of which points to a subscription data source with stored credentials.