topic Re: Power BI Reports Connection String change on Power BI Report Server using Rest API in Report Server

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

elitekrishnan — Fri, 06 Apr 2018 17:58:04 GMT

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

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

Jon-Heide — Fri, 06 Apr 2018 22:19:51 GMT

Unfortunately this is not currently supported. Changes to data source connection strings must be made the PBI report and then uploaded. They can't be changed after saving to the server.

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

elitekrishnan — Sat, 07 Apr 2018 09:04:30 GMT

Hi Jon,

Thanks for your reply.

In this case, for a single report, we need to keep duplicate files with each environment connection string. This will be hard to manage in development and deployment.

We are having more than 100 Power BI Reports and these needs to deploy in multiple environments, it will be more work to change connection property and upload in each nvironment.

Is there any alternate way to deploy the Power BI reports to multiple environment with different connection string/ data source?

or any option avaialble to update the connection string property in PBIX file through coding?

Thanks,

Navaneethakrishnan Thangaraj

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

Plantje — Fri, 20 Apr 2018 11:55:52 GMT

I can now deploy the reports. But, since I have been searching this long to get that working I forgot that I read here that it is impossible to change the data source afterwards :( Why is that read only?

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

elitekrishnan — Fri, 20 Apr 2018 12:20:46 GMT

Hi,

As Jon-Heide mention, at the moment PowerBI is not supporting it.

For this issue, I done some workaround as below.

In ReportServer database, "DataModelDataSource" table has connection string details of each datasource in PowerBI Report (with ItemId refeence) but this is in varbinary/encrypted format.
For Example if you are moving the report from Dev. environment to Prod. Environment
Create dummy report with Prod env as datasource details and upload that report in development environment, then you can get connection string (in varbinary) from DataModelDataSource table, keep this string as your new datasource connection string for prod environment.
After publishing the reports in prod environment, for this particular itemid and datasource id, repalce the new connection string.
You can get the Item ID and Datasource ID "Get-RsRestItemDataSource" method.
You need to maintain the Prod. env encrypted connection string (combination of server name and database name) and need to pass to PowerShell script while publishing the report.
Using belowquery, you can update the new connection string (Prod. env) for each report.

$Query = "Declare @varCharConnectionString varchar(max); 
		SELECT @varCharConnectionString = '" + $EncryptedConnectionString + "'; 
		Update DataModelDataSource set ConnectionString = convert(varbinary(max),@varCharConnectionString,1) 
		Where ItemId = '" + $PowerBIReportItem.Id + "' and DataSourceID ='" + $PowerBIReportDataSource.Id + "'"
Invoke-Sqlcmd -Query $Query -ServerInstance $ReportServerName -Database $ReportServerDatabaseName -Username $ReportServerDatabaseUserName -Password $ReportServerDatabasePassword

I hope this will help you..

Thanks,

Navaneethakrishnan Thangaraj

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

Plantje — Fri, 20 Apr 2018 12:24:05 GMT

Cool! Thanks! That is most likely something I can use in the coming sprints when creating a deployment tool

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

Plantje — Mon, 23 Apr 2018 13:19:57 GMT

I have found another work around.

If you edit the C:\Windows\System32\drivers\etc\hosts\file file (correct, no extension)

And add something like this:

[IP of your SQL Server DB] SQLServer

Then you can do that on all your application servers and in the report you can use "SQLServer" as server name. Then you can deploy the reports without editting them.

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

elitekrishnan — Mon, 23 Apr 2018 13:28:52 GMT

Thanks,

In my case, need to change both database and server name in all environments.

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

Plantje — Mon, 23 Apr 2018 13:50:59 GMT

Ah ok, that will be harder. Microsoft should just do an update :)

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

Anonymous — Thu, 14 Jun 2018 23:27:02 GMT

I tried to change host file but change only reflects when you restart the powerBI report server

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

Anonymous — Thu, 14 Jun 2018 23:32:14 GMT

I tried with host file and found that host file change only works if we restart the power bi report server after host file changed

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

Plantje — Mon, 18 Jun 2018 20:45:29 GMT

Oops, sorry, forgot to mention that!

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

craiha — Wed, 31 Oct 2018 08:34:53 GMT

I'm curios if anyou really managed to get this working.

We have implemented the connectionstring update in the ReportServer database and all looks well in the Power BI Report Server. I.e. on the DataSources page of the report. However when you create a Scheduled refresh it doesn't use the new value from the database, instead it uses the value in the pbix file :(

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

Piquet65 — Mon, 24 Apr 2023 07:53:46 GMT

Is it just me, or does the inability for Power BI Report Server deployments to modify the connection string without modifying the Power BI report being deployed each time seem like a significant functional hole in the platform?

I'm sure most organisations would have multiple PBIRS environments up-stream from PRODUCTION (i.e. DEV, TEST, etc.) to deploy to after appropriate testing has been completed, & it seems wrong / high risk to have to be opening up and editing the code in a deployment asset each time it needs to progress to a downstream environment.

This requirement to modify the PBIX for each environment has been reported several years ago now and is still a requirement, suggesting that it's not considered an issue worth worrying about by the Power BI team @ Microsoft.

What point am I missing here? i.e. Why isn't this considered a significant issue and hence already addressed since 2018?

Are there any plans to address it? Or do we need to struggle along modifying each PBIX as we deploy to a downstream environment and/or look at developing our own PBIRS report deployment tool?

Simply retaining the data source connection details when a PBIX report REPLACES an existing report on PBIRS would be a great step forward/improvement. Currently it seems that automating Power BI report deployments wired up for downstream envuironments is not a feasible option.

Am I right, or am I missing something?

Piquet