Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Service Principal Credential Configuration Error: PowerBiNotAuthorized

Hi. I have a similar problem to the following forum posted by akarkal : Solved: Update DataSet Credentials using Service Principal - Microsoft Fabric Community Similarly, I am trying to update data...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Here is the script that finally worked for me. I was suggested to get the OAuth2 access token in some other way and I did it that way. I am not sure how to tell from the power bi service if the data source is actually authenticated. However, I am assuming that it is authenticated because the script does not output an error. 

    # Set execution policy for Task Scheduler compatibility
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

$tenantId = ''
$vaultName = ""
$secretName = ""
$applicationId = ""

# Power BI Application Authentication
$secret = Get-AzKeyVaultSecret -VaultName $vaultName -Name $secretName -AsPlainText
$securePassword = $secret | ConvertTo-SecureString -AsPlainText -Force
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $applicationId, $securePassword
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId $tenantId

# Get PAT (through OAuth2 client credentials flow)
$clientId = $applicationId
$clientSecret = $secret
$scope = "2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default"

$body = @{
    client_id     = $clientId
    scope         = $scope
    client_secret = $clientSecret
    grant_type    = "client_credentials"
}

$response = Invoke-RestMethod -Method Post `
    -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" `
    -ContentType "application/x-www-form-urlencoded" `
    -Body $body

$accessToken = $response.access_token

# ---------------- MAIN SCRIPT ----------------

$datasetId = ""
$workspaceId = ""

$datasources = Get-PowerBIDatasource -DatasetId $datasetId -WorkspaceId $workspaceId
$datasource = $datasources[0]

# # if no gateway, stop
if (-not $datasource.GatewayId -or -not $datasource.DatasourceId) {
    Write-Host "No datasource found or no gateway associated with the datasource. Exiting script."
    return
}

$gatewayId = $datasource.GatewayId
$datasourceId = $datasource.DatasourceId

# Take Over
Invoke-PowerBIRestMethod -Url ("https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetId/Default.TakeOver") -Method Post -ErrorAction Stop

# Bind to Gateway (because after taking over, the dataset is not bound to the same gateway)
$body = @{
    "gatewayObjectId"     = $gatewayId
    "datasourceObjectIds" = @($datasourceId)
} | ConvertTo-Json -Depth 10


Invoke-PowerBIRestMethod -Url ("https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetId/Default.BindToGateway") `
    -Method Post `
    -Body $body `
    -ContentType "application/json" `
    -ErrorAction Stop

# Reget the datasource after taking over and binding to gateway (this is necessary because the datasource might change after these operations)
$datasources = Get-PowerBIDatasource -DatasetId $datasetId -WorkspaceId $workspaceId
$datasource = $datasources[0]

$datasourceId = $datasource.DatasourceId
$gatewayId = $datasource.GatewayId

$body = @{
    credentialDetails = @{
        credentials                 = "{""credentialData"": [{""name"": ""accessToken"", ""value"": ""$accessToken""}]}"
        credentialType              = "OAuth2"
        encryptedConnection         = "Encrypted"
        encryptionAlgorithm         = "None"
        privacyLevel                = "Public"
        useEndUserOAuth2Credentials = $false
        useCallerAADIdentity        = $false
    }
} |  ConvertTo-Json -Depth 10

# Update the datasource with the new credentials
Invoke-PowerBIRestMethod -Url ("https://api.powerbi.com/v1.0/myorg/gateways/$($gatewayId)/datasources/$($datasourceId)") -Method PATCH -Body $body -ErrorAction Stop -ContentType "application/json"

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous 

You're getting the PowerBiNotAuthorized error likely because Azure Databricks credentials cannot be updated using a service principal with KeyCredentials — it doesn’t work that way for cloud datasources like Databricks.

Here’s what usually works, try this, use Basic credentials (not KeyCredentials), and pass the Databricks Personal Access Token (PAT) as the password.
Like this, In Csharp use - var credentials = new BasicCredentials("token", secret.Value); // "token" can be any string

And then same in csharp use - var credentialDetails = new CredentialDetails(credentials, PrivacyLevel.Private,EncryptedConnection.Encrypted).

And make sure that your service principal is allowed in Admin Portal → Tenant Settings → Developer settings. After calling TakeOverAsync, fetch the datasources again before updating

And remember this, Use BasicCredentials, not KeyCredentials.
                                 Use PAT as password, and any dummy username like "token".
                                 Confirm SP permissions and tenant settings.
                                  Call TakeOverAsync, then get datasources again before updating

-----------------------------------------------------------------------------------------------------------------------------
If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Regards,
Akhil.

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, thank you so much for the lead. However, after the service principal takes over the dataset, the datasource that I get has no datasourceId nor a gatewayId. Relevant code:

 

var datasource = (await pbiClient.Datasets.GetDatasourcesAsAdminAsync(datasetId)).Value.First();

await pbiClient.Datasets.TakeOverAsync(workspaceId, datasetId);

datasource = (await pbiClient.Datasets.GetDatasourcesAsAdminAsync(datasetId)).Value.First();
var gatewayId = datasource.GatewayId ?? throw new InvalidOperationException("Datasource is not associated with a gateway."); // errors with no gateway id
var datasourceId = datasource.DatasourceId ?? throw new InvalidOperationException("Datasource does not have a DatasourceId."); // errors with no datasource id

Console.WriteLine($"Gateway ID: {gatewayId}, Datasource ID: {datasourceId}");

await pbiClient.Gateways.UpdateDatasourceAsync(gatewayId, datasourceId, new UpdateDatasourceRequest(credentialDetails));

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I fixed this issue by binding the dataset to the same gateway and datasource after taking over as the service principal. However, I now get a bad request error with all different credential types I tried with (Basic, Key, and OAuth2)

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hey Anonymous  Glad to hear you made progress and figured out that the dataset needed to be bound again after the service principal took over, that's a tricky step that often gets missed.

      Now, about the BadRequest error you're seeing when trying to update the credentials: I've run into the exact same issue before when working with Azure Databricks and a service principal, and here's what finally worked for me (after lots of trial and error!).

      What finally solved it is, first off, don’t use KeyCredentials for Databricks. It just doesn't work. Instead, go with BasicCredentials, but here's the trick — for the username, just use something like "token", and for the password, use the actual Databricks Personal Access Token (PAT) you fetched from Key Vault.

      So basically in csharp
      var credentials = new BasicCredentials("token", patFromKeyVault);
      var credentialDetails = new CredentialDetails(credentials, PrivacyLevel.Private, EncryptedConnection.Encrypted);

      ***Now here’s the important bit: after you do the TakeOverAsync, you must rebind the dataset to the gateway again before you try updating the credentials. Even if the gateway looks the same, it won’t “stick” unless you explicitly rebind.

      In csharp try the below

      await pbiClient.Datasets.BindToGatewayAsync(datasetId, new BindToGatewayRequest { GatewayObjectId = gatewayId });

      After that, fetch the data sources again don’t reuse the earlier result because now the binding is fresh and should return a valid datasourceId and gatewayId.

      Then update the credentials using those refreshed IDs.

      • So the full flow that worked for me was.
      • Take over the dataset with the service principal
      • Rebind the dataset to the cloud gateway
      • Re-fetch the datasource info
      • Use BasicCredentials with "token" and the actual PAT
      • Update the datasource

      Make sure the service principal is allowed in the Tenant Settings > Developer Settings, and that it has all the right API permissions (you’ve likely done this, but just worth double-checking).

      ---------------------------------------------------------------------------------------------------------
      If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

      Regards,
      Akhil.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous . Thank you for your response again. Actually those are all what I did. However, I still get bad request. That might be because I am just using the wrong PAT or clientId so I'll check on my end. Here is my C# script:

        using var pbiClient = await PowerBIAuth.GetPowerBIClientAsync();
var key = await PowerBIAuth.GetPAT();

var credentials = new BasicCredentials("token", key);

var credentialDetails = new CredentialDetails(
                credentials,
                PrivacyLevel.Private,
                EncryptedConnection.Encrypted
            );

//=================MAIN LOGIC===================

var datasources = (await pbiClient.Datasets.GetDatasourcesAsAdminAsync(datasetId)).Value;
var datasource = datasources.First();

var gatewayId = datasource.GatewayId ?? throw new InvalidOperationException("Datasource is not associated with a gateway.");
var datasourceId = datasource.DatasourceId ?? throw new InvalidOperationException("Datasource does not have a DatasourceId.");

await pbiClient.Datasets.TakeOverAsync(groupId, datasetId);
await pbiClient.Datasets.BindToGatewayInGroupAsync(groupId, datasetId, new BindToGatewayRequest(gatewayId, [datasourceId]));

//Get the datasource again after binding to ensure it has the correct gateway and datasource ID
datasources = (await pbiClient.Datasets.GetDatasourcesAsAdminAsync(datasetId)).Value;
datasource = datasources.First();

gatewayId = datasource.GatewayId ?? throw new InvalidOperationException("Datasource is not associated with a gateway.");
datasourceId = datasource.DatasourceId ?? throw new InvalidOperationException("Datasource does not have a DatasourceId.");

await pbiClient.Gateways.UpdateDatasourceAsync(gatewayId, datasourceId, new UpdateDatasourceRequest(credentialDetails));