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 datasource credentials of a dataset using a service principal through the C# PowerBI Client Library. 
However, no fixes on my part have worked. 

Here is the setup:

  • tenant settings has the toggle enabled for security group in which the service principal is a member of
  • Service Principal is an admin of the workspace
  • Service Principal takes over the dataset inside the C# script
  • Service Principal has Dataset.ReadWrite.All and Gateway.ReadWrite.All
  • The datasource that I want to change is a cloud datasource (Azure Databricks). As such, it is using a cloud gateway. 
  • I am trying to update the credentials through KEY credentials. 

Here is the 2 main parts of my script after getting an authenticated Power BI Client:
Getting the Key (PAT):

var secretClient = new SecretClient(new Uri(keyVaultUri), new DefaultAzureCredential());
KeyVaultSecret secret = await secretClient.GetSecretAsync(secretName, secretVersion);

 Trying to update datasource:

 

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.");

var credentials = new KeyCredentials(key);

var credentialDetails = new CredentialDetails(
                credentials,
                PrivacyLevel.None,
                EncryptedConnection.NotEncrypted
            );

await pbiClient.Datasets.TakeOverAsync(workspaceId, datasetId);
await pbiClient.Gateways.UpdateDatasourceAsync(gatewayId, datasourceId, new UpdateDatasourceRequest(credentialDetails));

At the last line of code, I get the PowerBINotAuthorized error. 

I also tried doing a basic authentication where the username is the SP's ClientId and the password is the SP's password. However, I got the following error:

Failed to update datasource: One or more errors occurred.
Inner Exception: {
  "code": "BadRequest",
  "message": "Bad Request",
  "details": [
    {
      "message": "Invalid value",
      "target": "datasourceDelta"
    }
  ]
}

 This error might be because Basic credentials might not work with Service Principals. 
I haven't really found a solution even with the related forum. As such, any help would be appreciated. 

 

Thanks. 

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"

     

13 Replies

  • 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

    Hi Anonymous 

    Just checking in one last time. Were you able to try out any of the suggestions shared earlier? If your issue is resolved, marking the accepted solution would be a big help to others who might be facing the same scenario.

    If you went in a different direction or still need support, feel free to drop a quick update, we’re happy to keep helping.

    Regards,
    Akhil.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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"