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 sharing your progress.
Based on my understanding, the following approach could be helpful in resolving the issue:
-
You may add a new data source; however, the Power BI API does not directly support programmatically adding to an existing dataset. To achieve this, update your PBIX file in Power BI Desktop by incorporating the new Azure SQL connection, and then republish it to the workspace.
-
Managed Identity is supported for Azure SQL in Power BI. Kindly configure your Azure SQL Server for Azure AD authentication and grant the Managed Identity the necessary database access. Additionally, update the data source credentials via the API to use Azure AD (OAuth2) with the Managed Identity of your calling application.
If you find our response helpful, we would appreciate it if you could mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.
Thank you.
- BartHuls1 year agoHelper I
Ok, I Configured the SQL server.
v-pnaroju-msft please povide me a working code snippet.... I don't get it to work.
OAuth2 is working with servive proncipals i.c.w. secrets not with Managed Identities.