question

SQL_DBA_20 avatar image
SQL_DBA_20 asked

Update Embedded Datasource for all SSRS Reports

I have a Report Server on SQL 2019 (https://MyReportserver.com/ReportServer). There are around 3000 reports and most of them use expression driven embedded data sources. I need to change the authentication mode for these embedded reports. I have installed the Reportingservices powershell module https://github.com/microsoft/ReportingServicesTools.

I have been able to get this to work for one report at a time via the below code.

$dataSources = Get-RsItemDataSource -RsItem '/Folder/MyReport' | Where-Object {$_.Name -eq "EmbeddedDataSource"}
$dataSources[0].Item.CredentialRetrieval = 'Store'
$dataSources[0].Item.Username = 'Domain\user'
$dataSources[0].Item.Password = 'PWD'
$dataSources[0].Item.WindowsCredentials = $true
$dataSources[0].Item.ImpersonateUser = $false
Set-RsItemDataSource -RsItem '/Folder/MyReport' -DataSource $dataSources

However I am unable to write a loop to get this working for all applicable reports under https://MyReportserver.com/ReportServer (sub-folders included). Can someone please assist with this issue?

powershellreporting services
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
dropthunder avatar image
dropthunder answered

Did it with Power BI Report Server RestApi:

[string] $uri = "https://xxx/Reports"
$session = New-RsRestSession -ReportPortalUri $uri
$reports = Get-RsRestFolderContent -WebSession $session -RsFolder / -Recurse | Where-Object {$_.Type -eq "PowerBIReport"}


$reports | ForEach-Object {
$dataSources = Get-RsRestItemDataSource -WebSession $session -RsItem $_.Path | Where-Object {$_.ConnectionString -eq "yyy;zzz"}
#$dataSources[0].DataModelDataSource.AuthType = 'Windows'
$dataSources[0].DataModelDataSource.Username = 'domain\user'
$dataSources[0].DataModelDataSource.Secret = 'password'
Set-RsRestItemDataSource -WebSession $session -RsItem $_.Path -RsItemType 'PowerBIReport' -DataSources $dataSources
}
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.