How do you get SQL 2008 reporting services to securely work for multiple apps?

Question

We have a setup where we have multiple instances of an application - one instance for each customer.

We call a lot of our reports via URL, passing in parameters on the querystring.

Early on, when we were on 2005, we identified a problem with this: I could change my querystring a bit and get into someone else's data.

We got around the problem by spoofing a user.

Now, due to some intermittent instability in our 2005 report services install, we are taking the opportuntiy to upgrade to 2008. However, the spoofing situation doesn't seem to work any more.

The technet articles that appear relevant seem to say that we need to create a very large security extension (article). This seems like overkill. Surely there is an easier way to call a URL-based report.

How are you accomplishing this in your applications?

Note: This is a repost (paraphrased) of my colleague's question. He didn't get any answers, and since he doesn't have any reputation he couldn't try out the bounty system. I reworded it and decided to give it a whirl. Please be tolerant - we really need an answer to this one. :)

Answer 1

I'm curious, what were you doing in 2005 for user spoofing that doesn't work in 2008?

But to the real question, it sounds to me like you probably should use custom authentication. The sample you linked to actually does a pretty good job of explaining what is going on and guides you through the basics. I had a similar problem to yours where we have many clients accessing reports and it's extremely important that there is no way for one client to get access to another client's data.

I ended up writing a custom authentication extension that creates client specific folders and permissions only the client specific user (which I set via the custom authentication) Browser access to all reports in that folder.

I'd also suggest that you look at http://www.gotreportviewer.com/ if you're writing an application that lives outside of the /Reports/ area. I unfortunately learned that this existed after I'd invested too much time in my custom authentication scheme.

Good luck!