Sql reporting services - programmatically configure a data source?

Question

I have many reports deployed as RDL for SSRS. Due to the high security requirements, db passwords change very often. It becomes a huge task to keep up with the changes and force dozens of reports to change. This leads to my question ...

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

• Can this be done using an application that modifies something in the report itself, since it is located on the server?

• Can this be done by changing the shared data source from the application, since the DS is on the server?

• Can this be done by embedding the script inside the report itself, which retrieves the connection from the web service?

thanks

Answer 1

There are several ways to do this, I think one of the easiest is to use the SSRS web service API. The web service allows you to manipulate all reporting objects, including data sources .

As a solution to this problem, you can update the credentials of data sources using web service calls every time the database password changes (even automate it using some kind of trigger?).

I did something similar in a project where RDL and data sources had to be created, deployed and processed at runtime, and it worked fine, so updating the data source should be feasible.

Answer 2

Reporting Service (http://ReportServerHost.mydomain.tld/ReportServer/ReportService2005.asmx). :

    public static void ChangeDataSourcePassword(string dataSourcePath, string password)
    {
        using (ReportingService2005SoapClient reportingService = new ReportingService2005SoapClient())
        {
            reportingService.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

            try
            {  
                ServerInfoHeader serverInfo = null;
                DataSourceDefinition dataSourceDefinition = null;

                serverInfo = reportingService.GetDataSourceContents(dataSourcePath, out dataSourceDefinition);
                dataSourceDefinition.Password = password;
                serverInfo = reportingService.SetDataSourceContents(null, dataSourcePath, dataSourceDefinition);
            }
            catch (FaultException ex)
            {
                // Do something with the exception. Rethrow it and/or show it to the user.
                Console.WriteLine(string.Format("Failed to change the password on {0}: {1}", dataSourcePath, ex.Message));
                throw new ApplicationException("Failed to change the password on the Data Source.", ex);
            }
        }
    }