Use ReportingService API to read custom data source credentials

Question!

We have an SQL 2008 R2 server running reporting services and I would like to identify reports that use specific credentials to connect to a data source. In this situation, the reports connect to the data source using a custom data source with a specific user account that has the proper security on the SQL server to run the reports.

Plan plan was to query [ReportServer].[dbo].[Catalog] to get a list of reports. Then run it through the code below to identify the reports using credentials I'm interested in. All of this would be done in an SSIS package.

Using the ReportingService API, I can read the user name of the credentials in the connection strings of shared data sources. The code is listed below. However, in my case, I need to load the data source credentials from a custom data source specific to the report, not a shared data sources. The GetDataSourceContents method doesn't seem to support this. If I supply the report name & path instead of the shared data source, it generates an error.

Is there another way to approach this? We have hundreds of reports, so using the UI to look this information up would be impractical.

Thanks in advance.

NOTE: "web_service" is the web service reference to the report server (http://[servername]/ReportServer/ReportService2005.asmx?WSDL)

Dim rs As New web_service.ReportingService2005()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim ds_def As web_service.DataSourceDefinition

ds_def = rs.GetDataSourceContents("/Data Sources/data_source_1")

With MyCredentialsOutputBuffer
    .AddRow()
    .UserName = ds_def.UserName
End With


Answers

You will need to pull down the rdl content and parse it:(

Embedded data source information is stored in the .rdl and not a shared data set item obtained via the api. Even then you can't retrieve data source passwords.

By : Ross Bush


This video can help you solving your question :)
By: admin