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,
Thank you for your valuable input.
We appreciate you pointing that out. Based on my understanding, I would like to share a point regarding the use of Managed Identity with Power BI and Azure SQL:
1.Managed Identity is supported in Power BI, but only in certain scenarios such as Power BI Desktop, Dataflows (Gen2), and Fabric Pipelines.
2.However, when using the Power BI REST API or SDK, Managed Identity is currently not supported for updating credentials. As you rightly mentioned, the REST API requires OAuth2 authentication using a Service Principal (i.e., an App Registration with a Client ID and Secret).
If your application is using Managed Identity to access Power BI APIs, it will not be able to update credentials using the REST API. In such a case, it is advisable to use a Service Principal configured for Azure SQL Authentication.
If you find this information helpful, kindly mark our response as the accepted solution and consider giving kudos. This will help other community members who might have similar questions.
Thank you once again.