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