Forum Discussion
How to set a dataconnection of a DataSet
- 1 year ago
After some struggling:
public async Task PatchSqlDatasourceCredentials(Guid groupId, string datasetId, string reportingSqlServer, string reportingSqldatabase, string userName, string password) { var pbiClient = await GetPowerBIClient() ?? throw new PowerBiException(PowerBiClientIsNull); var datasources = (await pbiClient.Datasets.GetDatasourcesInGroupAsync(groupId, datasetId)).Value; var sqlDataSource = datasources.FirstOrDefault(d => d.DatasourceType.Equals("Sql", StringComparison.OrdinalIgnoreCase)) ?? throw new PowerBiException(PowerBiClientIsNull); var selectedServer = sqlDataSource.ConnectionDetails.Server; var selectedDatabase = sqlDataSource.ConnectionDetails.Database; if (!selectedServer.Equals(reportingSqlServer, StringComparison.OrdinalIgnoreCase) || !selectedDatabase.Equals(reportingSqldatabase, StringComparison.OrdinalIgnoreCase)) { await pbiClient.Datasets.UpdateDatasourcesInGroupAsync(groupId, datasetId, new UpdateDatasourcesRequest(new UpdateDatasourceConnectionRequest { DatasourceSelector = new Datasource { DatasourceType = "Sql", ConnectionDetails = new DatasourceConnectionDetails() { Server = selectedServer, Database = selectedDatabase } }, ConnectionDetails = new DatasourceConnectionDetails() { Server = reportingSqlServer, Database = reportingSqldatabase }, })); } datasources = (await pbiClient.Datasets.GetDatasourcesInGroupAsync(groupId, datasetId)).Value; sqlDataSource = datasources.FirstOrDefault(d => d.DatasourceType.Equals("Sql", StringComparison.OrdinalIgnoreCase)) ?? throw new PowerBiException(PowerBiClientIsNull); var datasourceId = sqlDataSource.DatasourceId!.Value; var gatewayId = sqlDataSource.GatewayId!.Value; // Create UpdateDatasourceRequest to update Azure SQL datasource credentials var updateDatasourceRequest = new UpdateDatasourceRequest { CredentialDetails = new CredentialDetails( new BasicCredentials(userName, password), PrivacyLevel.None, EncryptedConnection.NotEncrypted) }; // Execute Patch command to update Azure SQL datasource credentials await pbiClient.Gateways.UpdateDatasourceAsync(gatewayId, datasourceId, updateDatasourceRequest); }Apperently I must "Replace" the SQL connection.
Question: Is it also possible to add or create a connection.
Can I also make the connection based on a Mangedd Identity
Hi BartHuls,
We sincerely appreciate your inquiry on the Microsoft Fabric Community Forum.
Kindly follow the steps outlined below to resolve the issue and ensure a seamless configuration:
-
In the Azure Portal, navigate to your SQL Server and review the firewall and virtual network settings. Enable Azure services and resources to access this server to facilitate a connection from Power BI without requiring a gateway.
-
Ensure that your account or service principal has Contributor access to both the workspace and dataset in Power BI.
-
Use the Power BI REST API to update the dataset’s data source.Set the data source type to SQL and provide the Azure SQL Server name (e.g., yourserver.database.windows.net) along with the database name.A BadRequest error typically indicates incorrect server or database names, or improper formatting. Kindly verify that these details match your Azure SQL configuration.
-
After updating the connection, configure the required credentials.Use SQL authentication (username and password) or Azure AD authentication, if configured.Ensure that the credentials provided are valid and have the necessary access to the database.
-
Trigger a dataset refresh in Power BI to confirm a successful connection.Monitor the refresh status in the Power BI Service.
Additionally, please refer to the following links for further guidance:
Datasets - Update Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Datasets - Update Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Learn
If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.
Thank you.