Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dataset refresh error Service principal

Hi,   we have deployed dataset using a service principal with Azure DevOps. The deployment works, but when we want to refresh the dataset (manually or with api calls or using Power Bi Actions in De...
Need Help
  • Jayendran's avatar
    Jayendran
    6 years ago

    Hi Anonymous ,

     

    The gateway used here is a deafult gateway which internally microsoft using for all the dataset. First use the below script in your local and use the fiddler to catch the exact error message. There are several reasons for bad reqeust. Find what is the reason for the bad request from the fiddler.

     

    $applicationId = "" # Need to pass the clientid from devops variable 
$clientsec = "" | ConvertTo-SecureString -AsPlainText -Force # Need to pass from devops secret variable 
 
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $applicationId, $clientsec 
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId "" # Need to pass from devops variable  
 
 
 
$workspacename="PowerBI_CICD_PROD" 
$datasetname="AdventureReports" 
 
 
## user credentials 
 
$username= "sadmin" 
$password= "Password@123" # Need to pass from devops secret variable  
 
 
##Getworksapce 
 
$workspace =Get-PowerBIWorkspace -Name $workspacename 
 
# GetDataSets 
$DatasetResponse=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets" -Method Get | ConvertFrom-Json 
 
 
# Get DataSet 
$datasets = $DatasetResponse.value 
 
     foreach($dataset in $datasets){ 
                if($dataset.name -eq $datasetname){ 
                $datasetid= $dataset.id; 
                break; 
                } 
 
            } 
 
## Take Over DataSet 
 
Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.TakeOver" -Method Post 
 
## update data source credentials 
 
$BounGateway=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.GetBoundGatewayDataSources" -Method GET | ConvertFrom-Json 
 
 
$UpdateUserCredential = @{ 
            credentialType ="Basic" 
            basicCredentials = @{             
            username= $username 
            password=$password 
            } 
} | ConvertTo-Json 
 
 
 
Invoke-PowerBIRestMethod -Url "gateways/$($BounGateway.value.gatewayId)/datasources/$($BounGateway.value.id)" -Method PATCH -Body $UpdateUserCredential | ConvertFrom-Json

     

     

Jayendran's avatar
Jayendran
Solution Sage
6 years ago

Hi Anonymous ,

 

The gateway used here is a deafult gateway which internally microsoft using for all the dataset. First use the below script in your local and use the fiddler to catch the exact error message. There are several reasons for bad reqeust. Find what is the reason for the bad request from the fiddler.

 

$applicationId = "" # Need to pass the clientid from devops variable 
$clientsec = "" | ConvertTo-SecureString -AsPlainText -Force # Need to pass from devops secret variable 
 
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $applicationId, $clientsec 
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId "" # Need to pass from devops variable  
 
 
 
$workspacename="PowerBI_CICD_PROD" 
$datasetname="AdventureReports" 
 
 
## user credentials 
 
$username= "sadmin" 
$password= "Password@123" # Need to pass from devops secret variable  
 
 
##Getworksapce 
 
$workspace =Get-PowerBIWorkspace -Name $workspacename 
 
# GetDataSets 
$DatasetResponse=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets" -Method Get | ConvertFrom-Json 
 
 
# Get DataSet 
$datasets = $DatasetResponse.value 
 
     foreach($dataset in $datasets){ 
                if($dataset.name -eq $datasetname){ 
                $datasetid= $dataset.id; 
                break; 
                } 
 
            } 
 
## Take Over DataSet 
 
Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.TakeOver" -Method Post 
 
## update data source credentials 
 
$BounGateway=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.GetBoundGatewayDataSources" -Method GET | ConvertFrom-Json 
 
 
$UpdateUserCredential = @{ 
            credentialType ="Basic" 
            basicCredentials = @{             
            username= $username 
            password=$password 
            } 
} | ConvertTo-Json 
 
 
 
Invoke-PowerBIRestMethod -Url "gateways/$($BounGateway.value.gatewayId)/datasources/$($BounGateway.value.id)" -Method PATCH -Body $UpdateUserCredential | ConvertFrom-Json

 

 

xli629b's avatar
xli629b
Helper II
4 years ago

I'm also trying to update credentials for a cloud data source as a service principal. I tried finding the default gateway (the one that isn't one of my own on-premises gateways) which was used by the data source. However, when I call Updata Datasource on this default gateway with the new credentials, I always get:

{
    "error": {
      "code": "DM_GWPipeline_Gateway_DataSourceAccessError",
      "pbi.error": {
        "code": "DM_GWPipeline_Gateway_DataSourceAccessError",
        "parameters": {},
        "details": [],
        "exceptionCulprit": 1
      }
    }
  }


However, Update Datasource works fine if I use it on a data source on an actual on-premises gateway. I'm wondering if this is an inherent limitation of cloud data sources because I see that this says "Cloud data sources and their corresponding credentials are managed at the user level."