How to get SSRS Data Source credentials and open an SQL Connection in .NET

Question

I am using an ASP.Net and C# front end to run some reports. I want to open an SQL Connection to the data source used by the report.

When the report uses integrated security it is easy enough to create a connection, however I want to create a connection when the user name and password are stored by the reporting server.

I can get SQL Server path and initial catalogue, using

    DataSource[] dataSources;
    DataSourceReference dsReference;
    DataSourceDefinition dsDefinition;
    string dsPath;
    ReportingService2005 rs = new ReportingService2005();

    dataSources = rs.GetItemDataSources(reportPath);
    if (dataSources.Length > 0)
    {
        dsReference = (DataSourceReference)dataSources[0].Item;
        dsPath = dsReference.Reference;
        dsDefinition = rs.GetDataSourceContents(dsPath);
        // ....
    }

I can also get the user name using

    username = dsDefinition.UserName;

however if I use

    password = dsDefinition.Password;

password = null, and can't be used to open the Sql Connection.

Is there a way to create an SQLConnection that uses the connection string and username and password credentials of a data source?

I am using Reporting Services 2008, and .NET 3.5 with web references to ReportService2005.asmx and ReportExecution2005.asmx

Answer 1

I really don't think it's possible, as this would pretty much constitute a security hole. I know this isn't the answer you are looking for, but I would parameterise the location of your reports (i.e. the IP/name of your MSRS server) and store them in your web.config along with a matching SQL instance. While it's not exactly what you're after, I think that's about as close as you are going to get.

Answer 2

I think you should read this page: http://msdn.microsoft.com/en-us/library/reportservice2005.datasourcedefinition_properties.aspx

it shows the password field is write-only, so you can't set its value,but read it only.