Windows Authentication with SSRS Subscriptions

Question

We're starting to develop some SSRS reports using SSRS in SharePoint integrated mode. Environment consists of SharePoint 2013 with SQL Server 2012.

Things are working great, except for using subscriptions to deliver reports on a schedule. If our report data source is set to use Windows Authentication, we get the following error when trying to create a subscription:

Subscriptions cannot be created because credentials used to run this report are not stored. Provide credentials for the report datasource(s) or contact your administrator.

If we modify the data source to use a stored set of windows credentials, the subscriptions work fine. I should note that running the report manually when the data source is set to Windows Auth also runs fine.

Does anyone know if these is even supported? Doing some web research, I haven't been able to find any references to people doing it. If so, anyone know what logs I should be looking at to try and troubleshoot? Neither the ULS, nor the SSRS reports seem to be providing any info for me to base it on.

Answer 1

Unfortunately, you can't create subscriptions if your report has to use a Windows Identity (i.e., run as a logged-in user). This is because the subscription system cannot impersonate users when generating reports - like it says, it doesn't have any credentials to use.

I was recently faced with this situation - my solution was to create a secondary configuration of the report in Visual Studio Data Tools called 'Unattended', which deployed the report to an 'Unattended Reports' folder and connected to Data Sources in an 'Unattended Data Connections' folder. I then set up these new data connections to use stored credentials and then created the subscription on the unattended report.

I believe using the SSRS API you might be able to get around this by providing the stored credentials as part of the service call... that might just be for data-driven subscriptions though. We didn't go down that route as the 'doppleganger' solution described enough worked pretty well.