topic Re: UpdateDatasourcesInGroup on a v3 datasource / dataset does not work -- need to change connection in Developer

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

caiken — Wed, 13 May 2020 16:14:21 GMT

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.

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

caiken — Fri, 22 May 2020 21:15:01 GMT

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

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

Anonymous — Tue, 01 Sep 2020 09:25:52 GMT

HI @caiken ,

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

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

dmarkham — Fri, 30 Oct 2020 14:05:08 GMT

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

Thanks

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

TedPattison — Fri, 30 Oct 2020 18:25:01 GMT

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); }