cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

 

 

12 REPLIES 12
Highlighted
Microsoft
Microsoft

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

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. 

Highlighted
Frequent Visitor

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

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

Highlighted
Helper I
Helper I

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

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?

Highlighted
Frequent Visitor

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

 

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

 

 

 

Highlighted
Helper I
Helper I

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

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

Helper I
Helper I

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

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.

Highlighted
Frequent Visitor

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

Thanks, 

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

 

Highlighted
Helper I
Helper I

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

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

Highlighted
Frequent Visitor

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

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors