We are designing a multitenant asp.net solution with SSRS reporting services. We are planning on isolating customer data in separate database instances (industry regulations), so we will have one database per client/tenant. Each client will have the same set of reports, and we have a single SSRS instance. Our connection strings are defined in the reports via the reports data source. How can I have our single SSRS instance query the correct tenant database on the fly? Is it possible to simply pass in a full connection string to the report? Is it possible to dynamically change a reports data source? If so, what effect does that have on concurrency, when other reports attempt to use the data source? Is there another solution to this, maybe architectural? Thanks!

In a report, the connection string for an embedded data source can be an expression. So you can do something like: Static data source: "Server=hostmachine\tenantInstance;intial catalog=AppDBName" Then you can use an expression to set this based on the user context: ="Server=" & Code.LookupDataBaseLocation(User!UserID) & "; initial catalog=AppDBName" You can then use a custom function within the Code of the report to make a call to your external server which stores the mapping from user name running the report to the database server that houses the data. I recommend you do this using a custom assembly since you'll need to grant that code CAS rights to access the network. A different way of doing this, which I would not recommend due to security concerns is to pass the value via a parameter. Only use this shortcut if you fully trust the process that is calling the report - if a malicious user could run the report, they could provide any value they wanted... potentially giving tenants access to other tenants' data. Hope this helps, -LukaszGot a bug or feature request? Tell me about it at http://connect.microsoft.com. This posting is provided "AS IS" with no warranties, and confers no rights.