SSRS 2008 User Generated Report Subscriptions with Windows Integrated security - possible?

Question

I'm having a problem with my SSRS 2008 configuration at the moment. This is how we are currently set up.

1 server hosting SQL Server and SSRS, no integration with sharepoint etc. Users access the SSRS web application, and then from there they access the "Report Builder 1.0" too, which they can use to create and run reports based off a reporting model.

This is all done using Windows Authentication - so they login with their domain account to the web application, and then again when loading the Report Builder. Each domain user is configured to be able to login to SQL Server rather than using a generic SSRS account, and all of the above works fine.

The way this is setup means that you can always tell which individual user is logged in and running reports - either to the website or when running reports through report builder. This is a requirement of this application as all users will see slightly different versions of the data models (this is controlled by the underlying views, based off the domain account running the query). So Tom may get 100 rows back from his query but Harry will only get 50, etc.

The Datasource is setup to use integrated security (Which passes through this domain account to SQL Server).

My problem comes when a user creates a report in report builder, and then saves it to the server. When they login to the web application and run the report there - it works fine, but when they try to setup an email subscription, they get this message:

"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"

A bit of research leads me to think that this is down to the integrated security settings, as for some reason SSRS is ignoring the currently logged on user account or cannot use that to setup the subscription.

Everything works if I set the datasource to use a specific account - but then that breaks the data filtering based on user account (obviously, as it would now be just using one account for everyone!).

Any ideas for how I can approach this? Is there a configuration setting that I can play with to try and get this working using integrated security? Any help/comments are appreciated!

Answer 1

To create a report subscription you must meet a few reqs:

First is,

A report that can run unattended (that is, a report that uses stored credentials or no credentials).

To do what your wanting to do I suggest you setup data driven subscriptions. Heres a link to get you started. Data Driven Subscriptions Theres also several good videos of the basic setup. Like this one sqlLunch #8

Here's another walkthrough that's pretty decent: Walkthrough

Hope any of this helps ya.

Thats a nice one to have in your tool belt as well.

Answer 2

If you just drop the subscribed report into a file share instead of emailing it, the users can fill in their username and password, so it will run as them. The only problem with doing it this way is when they change their password, they will need to go and put the new password into the subscription again.

I guess they could have another account with a password that wont expire to run the report as, but that's probably not good practice

Answer 3

what you could do is have the list of users in a table in the database with what they're permissions are, then set up a data-driven subscription to get the list of users and get their permissions and filter the report accordingly.