Sql reporting Services - Programmatically setting a datasource?

Question

I've got a bunch of reports deployed as RDL's to a SSRS. Because of high security requirements, the db passwords change very frequently. It's become a huge task to keep up with the changes and having to modify dozens upon dozens of reports. This leads to my question...

Is it possible to programmatically set a data source or a connection string for a deployed report?

• Could this be done using an app that modifies something in the report itself as it sits on the server?

• Could this be done by modifying a shared data source from an app as the DS sits on the server?

• Could it be done by embedding a script inside the report itself which retrieves the connection from say a web service?

Thanks

Answer 1

Hi,

This can be done in multiple ways, I think one of the simplest is using the API of SSRS web service. The web service lets you manipulate all the reporting entities including the Data Sources.

As a solution to this issue it's possible to update the data sources credentials using calls to the web service every time the DB password changes (even automate it using some sort of trigger?).

I did something similar in a project where RDLs and data sources had to be generated, deployed and manipulated in runtime and it worked fine so updating data source must be feasible.