how to Fetch datasource usernames of ALL reports

Question

Below script fetches datasource for individual RsItem, but we have over 800 reports on reportserver and so i want to get a detailed list of all reports' usernames (i dont want to have specify each RsItem)

Install-Module ReportingServicesTools
# establish session w/ Report Server
$session = New-RsRestSession -ReportPortalUri https://sql-dev02.domain.com/Reports
# get data source object
$dataSources = Get-RsRestItemDataSource -WebSession $session -RsItem '/RefreshInfoSample'
# get username
$dataSources[0].DataModelDataSource.Username

Desired output: A list like this

ReportName ReportFullPath DatasourceUsername
report1 https://sql-dev02.domain.com/reports/powerbi/RefreshInfoSample Srvc_Account2

If there is more than one datasource connection/report, list usernames on individual rows separately

How to achieve this?

Im trying to adjust the script to something like this, but not sure the correct approach to loop through the items:

# establish session w/ Report Server
     $session = New-RsRestSession -ReportPortalUri https://sql-dev02.domain.com/Reports
foreach (-RsItem $item) {
 # get data source object
 $dataSources = Get-RsRestItemDataSource -WebSession $session -RsItem $item
 # get username
 $dataSources[0].DataModelDataSource.Username
}

Answer 1

Hi @Cataster ,
You said before that the password is encrypted through the query, and the user name itself is also encrypted.

The ConnectionString column in DataSources contains informations about embedded data sources of reports; they are sensitive data and therefore encrypted; there is no official to encrypt them, even not for a sysadmin. But you can look them up via Report Manager URL.

You can only query the connection string for shared data sources. Transact-SQL script to get connection string of all SSRS Shared Datasources

Through the following statement:

select  
CONVERT(NVARCHAR(max),CONVERT(VARBINARY(max),ConnectionString)) As ConversionAttempt,  
  ConnectionString,*  
  from ReportServer..DataSource  

The username and password obtained from the query are encrypted, you could try the script in this link: https://www.sqlservercentral.com/forums/topic/decipher-reportserver-datasource-connectionstring

Hope this helps.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.