Problem with Refreshing Dataset after Parameter Change with Service Principal

[fxs7576_02]

Hi PowerBI Community,

I have a few semantic models (sourced from AWS/GCS and not via. Gateway) that are not owned by Service Principal. My goal is to programmatically change some parameters that the semantic models are using and then apply the change, and these are the steps I took:

1. Take over the dataset from the original user to Service Principal. The Service Principal must the dataset owner to update the parameter. (endpoint)
2. Update the parameter of the dataset (endpoint)
3. Refresh the dataset to apply the parameter change to the dataset. (endpoint)

But on step 3, I always receive the following message, and most of the dataset settings are greyed out.

"It looks like scheduled refresh failed because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources."

Does anyone know another way to change and apply the new parameter using REST API, or solve the refresh issue on step 3 (maybe allowing Service Principal to refresh any semantic models regardless of the ownership in the workspace)?

Best regards

[Anonymous]

Hi @fxs7576_02 ,

Base on your description, it sounds like you're running into an issue with data source credentials after you update the parameters and try to refresh the dataset. Please check if the service principal hit the following limitations:

In addition, the following link is the one which has the similar problem as yours. Hope its solution can help you solve the problem.

Solved: Dataset refresh error Service principal - Microsoft Fabric Community

Best Regards

[fxs7576_02]

Hi @Anonymous ,

I don't think the Service Principal is facing those limitations.

When I looked at the solution in Solved: Dataset refresh error Service principal - Microsoft Fabric Community thread, they used the endpoint to update the datasource credential for Gateway.

$BounGateway=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.GetBoundGatewayDataSources" -Method GET | ConvertFrom-Json

I'm not yet familiar with gateway connection. Is the above endpoint still relevant if I use a personal cloud connection (i.e. RDS MySQL) as opposed to gateway?

[lbendlin]

Your process is missing a step.  After a semantic model takeover all data source credentials are removed and need to be reapplied.

Datasets - Update Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

[fxs7576_02]

Thanks @lbendlin .

After the 'Takeover Dataset' step, I tried the Datasources Update endpoint

https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/datasets/{dataset_id}/Default.UpdateDatasou... 

with the following request body:

{
    'updateDetails': [
        {'datasourceSelector':
         {
             'datasourceType': 'MySql',
             'connectionDetails': {
                 'server': 'demo_server_01',
                 'database': 'demo_database_01'
            }
         },
         'connectionDetails': {
             'server': 'demo_server_01',
             'database': 'demo_database_01'
         }
        }
    ]
}

But received the following message:

{"error":{"code":"InvalidRequest","message":"The given connection details for selector # are invalid"}}

Is there any information in the request body that I need to add or modify?

[jbauti10]

Any solution for this?

[LJonesStripe]

I raised an MS support ticket which made it through to the PowerBI 3rd line support team. 

I was told this API endpoint is not actually designed for updating to a connection that already exists and there is currently no endpoint available for it. Which seems wild to me, it seems like exactly what it's for. 

I gave up in the end

[lbendlin]

I would recommend you raise a DCR.

[fxs7576_02]

Hey @LJonesStripe and @jbauti10 , apology for the super late response on this.

I managed to find the solution to this using the following process:

1. Create a gateway, and save its gateway_id.
2. Take over the dataset from the original user to Service Principal. The Service Principal must the dataset owner to update the parameter. (POST endpoint) 
3. Update the parameter of the dataset (POST endpoint)
4. Get the data source from the dataset, and retrieve its [gatewayId] (GET endpoint)
5. If the gateway_id from step #1 is not the same as that from step #4, run this POST endpoint to bind the gateway. <--- For some reason this is required. Not sure why yet.
6. Refresh the dataset to apply the parameter change to the dataset. (POST endpoint)

Let me know if it works on your end.

[jbauti10]

Hi @fxs7576_02, thanks for your response!

Unfortunately, it still doesn't work for me. My datasource is PostgreSQL on Azure. I don’t currently have any gateways set up in Power BI Service, since I assumed for cloud Postgres no gateway is needed, but I’m not sure if that’s wrong.

Here's my process until now:
1. Get access token for service principal (I use the msal python library for this like in this example).
2. Take over dataset with this endpoint (POST endpoint).
3. Get gateway ID and datasource ID from the get datasource in group endpoint (GET endpoint)
4. Update the parameters of the dataset (POST endpoint)
5. Update datasource credentials (POST endpoint) using the gateway ID and datasource ID I get after taking over the dataset. I include this step because when the refresh is triggered I get an error saying: "It looks like scheduled refresh failed because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources".
6. Refresh the dataset to apply the parameter changes. (POST endpoint)

The issue happens at step 5, I keep getting either:

{"error":{"code":"InvalidRequest","message":"The given connection details for selector # are invalid"}}

or

{"error":{"code":"ItemNotFound","message":"Dataset doesn't contain a datasource that matches selector #1"}}

In the update datasource credentials step. The two body requests I've been using are the following:

body = {
    "updateDetails": [
        {
            "datasourceSelector": {
                "datasourceType": "PostgreSql",
                "connectionDetails": {
                    "server": settings.PG_AZURE_HOST,
                    "database": settings.PG_AZURE_DATABASE
                },
                "gatewayId": gatewayID,
                "datasourceId": datasourceID
            },
            "connectionDetails": {
                "server": settings.PG_AZURE_HOST,
                "database": settings.PG_AZURE_DATABASE
            },
            "credentialDetails": {
                "credentialType": "Basic",
                "credentials": json.dumps({
                    "username": settings.PG_AZURE_USER,
                    "password": settings.PG_AZURE_PASSWORD
                }),
                "encryptedConnection": "Encrypted",
                "privacyLevel": "Private"
            }
        }
    ]
}​

or

body = {
            "updateDetails": [
                {
                    "datasourceSelector": {
                        "dataSourceType": "PostgreSQL",
                        "gatewayId": gatewayID,
                        "datasourceId": datasourceID
                    },
                    "connectionDetails": {
                        "server": settings.PG_AZURE_HOST,
                        "database": settings.PG_AZURE_DATABASE
                    },
                    "credentialDetails": {
                        "credentialType": "Basic",
                        "credentials": json.dumps({
                            "username": settings.PG_AZURE_USER,
                            "password": settings.PG_AZURE_PASSWORD
                        }),
                        "encryptedConnection": "Encrypted",
                        "privacyLevel": "Private"
                    }
                }
            ]
        }

but neither work.

Questions:

• For Azure Postgres, do I need to set up a gateway in Power BI Service for this API call to succeed?
• Is my datasourceSelector wrong (maybe I shouldn’t include datasourceType / connectionDetails)?
• Or is there something else I’m missing in the credential update step?

Any kind of help is hugely appreciated!

Thanks.

[LJonesStripe]

Did you ever manage to find the solution to this?