Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
elitekrishnan
Frequent Visitor

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

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

 

 

13 REPLIES 13
Piquet65
Frequent Visitor

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

Jon-Heide
Employee
Employee

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. 

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

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?

 

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

 

 

 

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 😞

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

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.

Thanks, 

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

 

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

Anonymous
Not applicable

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

Oops, sorry, forgot to mention that!

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.