How to set a dataconnection of a DataSet

[BartHuls]

I loaded a workspace with a Semantic Semantic (c# PowerBIClient SDK)
Of this Semantic Model I want to set the "GateWay and cloud connects"
The datasource is a Sql server which is available in the azure cloud (same tenant). I assume that a GateWay is not needed, since I'm connecting not to a local datasource

I'm trying to use the UpdateDatasourcesRequest, but I'm getting a "BadRequest"  exception.

Any help will be appreciated.

Reagrds,
Bart

[v-pnaroju-msft]

Hi BartHuls,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi BartHuls,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

Hi BartHuls,

Thank you for sharing your progress.

Based on my understanding, the following approach could be helpful in resolving the issue:

1. 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.

2. 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.

[BartHuls]

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.

[BartHuls · Accepted solution]

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

[v-pnaroju-msft]

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:

1. 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.

2. Ensure that your account or service principal has Contributor access to both the workspace and dataset in Power BI.

3. 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.

4. 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.

5. 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.