Power BI Reports Connection String change on Power BI Report Server using Rest API

[elitekrishnan]

Hi,

I am trying to publish Power BI Reports to Power BI Report Server in different environment like Dev, SIT, Prod through PowerShell Script using Rest API. While publishing, I am changing the PBI Reports database connection properties like User Name and Password (different for each environment) and reports are publishing successfully.

When I am trying to change the Connection String property, it is not allowing to change and if I change, while publishing, system throws 400 Bad Request Error.

If there is any change in Connection String value, then it is not publishing. I set “IsConnectionStringOverridden” property to ‘true’ also, but same error.

PowerShell Code to Set Datasource properties

$ReportServerUri = "http://<<ServerName>>/PBIReportServer"
$ReportPortalUri = "http://<<ServerName>>/PBIReports"

$WebServiceUri = $ReportServerUri +"/ReportService2010.asmx?WSDL"
$PowerBIReportingProxy = New-WebServiceProxy -uri $WebServiceUri -UseDefaultCredential -namespace "ReportingWebService"
$PowerBIReports = $PowerBIReportingProxy.ListChildren('/', $true) | Where-Object {$_.TypeName -eq "PowerBIReport"}
Foreach($PowerBIReportItem in $PowerBIReports) 
{
	$PowerBIReportDataSource = Get-RsRestItemDataSource -RsItem $PowerBIReportItem.Path -ReportPortalUri $ReportPortalUri
	$PowerBIReportDataSource.DataModelDataSource.AuthType = "UsernamePassword"
	$PowerBIReportDataSource.DataModelDataSource.Username = "xxxxxx"
	$PowerBIReportDataSource.DataModelDataSource.Secret = "xxxxx"
	$PowerBIReportDataSource.ConnectionString = "data source=dbservername;initial catalog=databasename;persist security info=False"
	$PowerBIReportDataSource.IsConnectionStringOverridden = $True        
	Set-RsRestItemDataSource -RsItem $PowerBIReportItem.Path -RsItemType PowerBIReport -DataSource $PowerBIReportDataSource -ReportPortalUri $ReportPortalUri
}

Request passing to API for updating the datasource details

[
    {
        "Id":  "3100fcc0-b539-e811-a875-000d3af8bffc",
        "Name":  null,
        "Description":  null,
        "Path":  null,
        "Type":  "DataSource",
        "Hidden":  false,
        "Size":  0,
        "ModifiedBy":  "XXXXXXXXX",
        "ModifiedDate":  "2018-04-06T17:44:05.067+01:00",
        "CreatedBy":  "xxxxxxxxxxxx",
        "CreatedDate":  "2018-04-06T17:15:43.44+01:00",
        "ParentFolderId":  null,
        "IsFavorite":  false,
        "Roles":  [

                  ],
        "ContentType":  null,
        "Content":  "",
        "IsEnabled":  true,
        "ConnectionString":  "data source=dbservername;initial catalog=databasename;persist security info=False",
        "DataSourceType":  null,
        "IsOriginalConnectionStringExpressionBased":  false,
        "IsConnectionStringOverridden":  true,
        "CredentialRetrieval":  "prompt",
        "CredentialsByUser":  null,
        "CredentialsInServer":  null,
        "IsReference":  false,
        "DataSourceSubType":  "DataModel",
        "DataModelDataSource":  {
                                    "Type":  "DirectQuery",
                                    "Kind":  "SQL",
                                    "AuthType":  "UsernamePassword",
                                    "SupportedAuthTypes":  [
                                                               "Integrated",
                                                               "Windows",
                                                               "UsernamePassword"
                                                           ],
                                    "Username":  "ReportDataUser",
                                    "Secret":  "XXXXXX",
                                    "ModelConnectionName":  "7e2bd505-4a2b-4661-8c4d-853517800b50"
                                }
    }
]

Please help on to update the Connection String Property for Power BI Reports.

Thanks,

Navaneethakrishnan Thangaraj