UpdateDatasourcesInGroup on a v3 datasource / dataset does not work -- need to change connection str

[caiken]

Hi all -- I have a need to change the server, database, username, and password for Datasets on a Power BI workspace with attached premium capacity, using Azure Power BI Embedded.  All data sources are Azure PaaS SQL.

Historically, I have used SetAllDatasetConnectionsInGroup to change the server and database, and then used UpdateDatasource to change the username and password.  This has worked great -- but I knew that SetAllDatasetConnectionsInGroup  was depreciated and it would eventually break -- and that day has come. 

It no longer works with newer Power BI files that have the "v3" data sources enabeld, called "enhanced metadata) in Power BI Desktop (see  https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enhanced-dataset-metadata ).  The SetAllDatasetConnectionsInGroup  now returns the error PowerBIUpdateMetadataUnsupportedModelWithV3DataSourceException.

To try to fix this, I moved to using the UpdateDatasourcesInGroup, but this doesn't seem to work.  It changes the server and database as shown in the Power BI portal, but when I access the report, it shows an error, and that error contains the OLD servername and database.  

So -- what is the proper way to change the server and database on a v3 datasource usign the REST API when the datasource is conencted via DirectQuery to Azure PaaS SQL?

BTW -- I tried many workarounds, including using parameters for servername/database (result: parameters not permitted on the PowerBI service for DirectQuery SQL), editing the PBIX file before uploading (not possible as connection string is embedded in compressed data in the PBIX for v3 data sources), using a PBIT (not allowed in REST upload), etc.

Help -- and thanks in advance.

[TedPattison]

This works for me...

public static void UpdateSqlDatabaseConnectionString(Guid WorkspaceId, string DatasetId, 
                                                     string Server, string Database) {

  var pbiClient = TokenManager.GetPowerBiClient(requiredScopes);

  Datasource targetDatasource = 
             pbiClient.Datasets.GetDatasourcesInGroup(WorkspaceId, DatasetId).Value.First();

  string currentServer = targetDatasource.ConnectionDetails.Server;
  string currentDatabase = targetDatasource.ConnectionDetails.Database;

  if (Server.ToLower().Equals(currentServer.ToLower()) && 
      Database.ToLower().Equals(currentDatabase.ToLower())) {
    Console.WriteLine("New server and database name are the same as the old names");
    return;
  }

  DatasourceConnectionDetails connectionDetails = new DatasourceConnectionDetails {
    Database = Database,
    Server = Server
  };

  UpdateDatasourceConnectionRequest updateConnRequest =
    new UpdateDatasourceConnectionRequest {
      DatasourceSelector = targetDatasource,
      ConnectionDetails = connectionDetails
    };

  UpdateDatasourcesRequest updateDatasourcesRequest = 
                           new UpdateDatasourcesRequest(updateConnRequest);
  
  pbiClient.Datasets.UpdateDatasourcesInGroup(WorkspaceId, DatasetId, updateDatasourcesRequest);

}

[caiken]

Anyone?  Certainly someone is trying to change connection strings on the fly and experiencing trouble like I am?

[Anonymous]

HI @caiken , 

Did you find a solution ? I am facing same issue now. Please share you approach. Thanks 

[dmarkham]

Hi, we are also facing the same issue. Has anyone found any solution?

Thanks