0

This is one of those "is it possible?" questions.

I'm looking to create a report that will be deployed to 3 different servers (all sql2008R2) each has its own shared data-source with different credentials. When run against each server is it possible for the report to detect which server and use the appropriate datasource/credentials?

The closest I've found so far is here: https://www.mssqltips.com/sqlservertip/4302/implement-dynamic-data-sources-in-sql-server-reporting-services/

The issue I have with this solution is I don't want the user to view/choose the server this process should be automatic and behind the scenes.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Mr.B
  • 397
  • 5
  • 16
  • 1
    Does each server have it's own reports databases? If so then if the datasource name is identical on each then simply deploying the report will be OK. So on our dev server the datasource "MyDatabase" points to a database on the dev server and a datasource with the same name sits on the production server pointing to a different database. – Alan Schofield Nov 07 '18 at 17:09
  • Yes the name of the database remains the same, only the server changes. – Mr.B Nov 08 '18 at 10:07
  • 1
    OK, so just make sure the datasource is the same name on each server and there is nothing else to do other than deploy the report to each server. Just make sure that 'OverwriteDataSources' is set to False in the project properties (it is by default). – Alan Schofield Nov 08 '18 at 10:41

1 Answers1

0

Yes. That is the entire concept between using a datasource and deploying to SSRS. Namely that you define a datasource for each environment, with credentials specific to that environment. When you deploy your report, you use the SSRS admin screen to attach the report to the (shared) datasource (on each/any server).

enter image description here

tgolisch
  • 6,549
  • 3
  • 24
  • 42
  • 1
    When you have a separate server for each environment, I think you should use the same name for the data sources on each server but the one on DEV points to DEV, QA points to QA, etc. This way you don't need to update the data source later (and the data source is not available in other environments). – Hannover Fist Nov 08 '18 at 23:48