2

I'm looking to use SSRS for multi-tenant reporting and I'd like the ability to have runtime-chosen Shared Data Sources for my reports. What do I mean by this? Well, I could be flexible but I think the two most likely possibilities are (however, I'm also open to other possibilities):

  1. The Shared Data Source is dictated by the client's authentication. In my case, the "client" is a .NET application and not the user, so if this is a viable path then I'd like to somehow have the MainDB (that's what I'm calling it) Shared Data Source selected by the Service Account that the client logs in as.
  2. Pass the name of the Shared Data Source as a parameter and let that dictate which one to use. Given that all of my clients are "trusted players", I am comfortable with this approach. While each client will have its own representative Service Account, it's just for good measure and should not be important. So instead of just calling the data source MainDB, we could instead have Client1DB and Client2DB, etc. It's okay if a new data source means a new deployment but I need this to scale easily enough as well to ~50 different data sources over time.

Why? Because we have multiple/duplicate copies of our production application for multiple customers but we don't want to duplicate everything, just the web apps and databases. We're fine with some common "back-end" things. And for SSRS, because of how expensive licenses are (and how rarely reports are ran by our users), we really want to have just a single back-end for all of our customers (I actually have a second one on standby for manual disaster recovery situations - we don't need to be too fancy here as reports are the least important DR concern we have).

I have seen this question which points to this post but I was really hoping there was a better way than this. Because of all of those additional steps/efforts/limitations/etc, I'd rather just use PowerShell to script duplicate deployments of the reports with tweaked hardcoded data sources instead of standardizing on the steps in that post. That solution feels WAY too hacky to me and doesn't seem to scale very well at all.

Community
  • 1
  • 1
Jaxidian
  • 13,081
  • 8
  • 83
  • 125
  • 1
    This is an old question but I wonder if you ever got through with option #2? I have the same issue and passing the connection string as a parameter to the embedded data source is an issue un my scenario. I need a way to make the report call different shared data sources based a parameter. – PedroC88 Sep 09 '18 at 03:40

1 Answers1

4

I've done this a bunch of terrible ways (usually hardcoded in a dynamic script), and then I discovered its actually quite simple.

Instead of using Shared Connection, use the Embedded Connection and create your Connection string based on params (or any string manipulation code)....

enter image description here

Trubs
  • 2,829
  • 1
  • 24
  • 33
  • 1
    Sadly, I need to use Shared Data Sources "because reasons" (along the lines of so these connectionstrings can be managed post-deployment by system administrators). The consuming applications don't have the database secrets/knowledge to pass in the connectionstring info so this just isn't a good option for us although it's probably great for many others! We ended up just going with the very non-ideal option I linked in my question. However, since this answer is probably very useful for many others, I'm going to go ahead and accept it even though it doesn't work for us. – Jaxidian Feb 01 '17 at 17:43