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

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Jayendran  i did a try to update the credentials with your script and my parameters, but i always receive the error message:

"Message : Response status code does not indicate success: 400 (Bad Request).
StackTrace : at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__31.MoveNext()
Exception : System.Net.Http.HttpRequestException

"

 

Is it possible to change the credentails when you use a connection to Azure sql database, because you don't use a gateway for that?

 

The strange thing is that when i manually take over the dataset, i can manually refresh the dataset. And also the service principal can do a refresh, after i have been take over the dataset.

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