Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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.
$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 🙂
Oops, sorry, forgot to mention that!
User | Count |
---|---|
11 | |
9 | |
4 | |
1 | |
1 |