Sql-server – Creating Report Subscriptions on report that are using a Shared data source that uses Windows Integrated Security

Securitysql serverssrsssrs-2012

We've recently deployed a new SSRS Server 2012, that can use Windows Integrated Security. We configured the servers to properly use Kerberos authentication and we solved the double hop issue.

Now, all our reports are using Shared data sources that are configured to use Windows Integrated Security instead of storing an execution user on the data source setup page.

It all works fine and I was very happy about it, as it increased our security (as the execute permission is derived from the AD user and not from the hard-coded execution user), but now we're running into a problem:

When we try to create a Report Subscriptions on report that are using a Shared data source that uses Windows Integrated Security, we're getting the error message:

Subscriptions cannot be created because the credentials used to run reports are not stored, or if a linked report, the link is no longer valid

I've googled around, and all the links I've found tell me to simply either configure the shared data source to have stored credentials, or configure the report to have custom data source with stored credentials.

This contradicts the whole point of us have Windows Integrated Security!

Is there any way around it?

The sole purpose of SSPI in our use case is to authenticate to the database. The RS server has reports that use data sources on different SQL Servers. We are not, for example, using Integrated Security for row-level security, or to show different data to different users.

Best Answer

Community wiki answer:

You have two choices:

  1. Change the data sources to use a SQL login and password, then you can use the built in subscription method.
  2. Use a scheduled app to generate the report, and have that do the e-mailing. The scheduled app would need to run under an account with permissions into SQL/SSRS.

I would only recommend #2 if you don't really need/want the users managing their own subscriptions. If the users are going to need the ability to generate their own subs then #1 is your only real option.