Dataset refresh error Service principal

[KoenVdB]

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 DevOps) we always receive the following error message:

Data source error:

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.

the owner of the dataset is the service principal that has deployed the dataset.

When i look at the details of the dataset, they are disabled because of the service principal is the owner.

As a test, i did a "take over" of the dataset. And did a manually refresh in the service. The Dataset will refresh, without changing credentials. The source of the dataset is a Azure SQL database using a SQL Server login.

Is there a way that the dataset can be refreshed with a service principal as owner of the dataset? Does the service principal need extra rights?

The service principal has the following rights for API calls and is added as admin of the related workspace

Thanks in advance

[Jayendran]

Hi @KoenVdB ,

Once you deployed your dataset in powerbi service. You have to modify the sql credentails in order to do the refresh.

Please see my below article, where i explained in sequence order on how to do that

https://community.powerbi.com/t5/Community-Blog/PowerBI-CICD-using-Azure-DevOps/ba-p/769244

[KoenVdB]

@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 · Accepted solution]

Hi @KoenVdB ,

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]

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

[markharing]

Hi Jayendran,

I have read your blog and answer above but I still have no idea what to do next; 

(BTW: your script is not available anymor in the gallery so I cant get it there; https://gallery.technet.microsoft.com/AzureDevOps-CICD-for-fefd58b8)

[KoenVdB]

@Jayendran 

thanks for the fiddler tip. 

The update with the credentails is working now. and i can refresh with the service principal.

It is strange that when i did a take over with my account i can refresh the dataset without entering the credentials. But it is solved.

Thank you.

[v-xuding-msft]

Hi @KoenVdB ,

Please check if it hits the limitations:

• Service principal only works with new workspaces.
• My Workspace isn't supported when using service principal.
• Dedicated capacity is required when moving to production.
• You can't sign into the Power BI portal using service principal.
• Power BI admin rights are required to enable service principal in developer settings within the Power BI admin portal.
• You can't install or manage an on-premises data gateway using service principal.
• Embed for your organization applications can't use service principal.
• Dataflows management is not supported.
• Service principal currently does not support any admin APIs.
• When using service principal with an Azure Analysis Services data source, the service principal itself must have an Azure Analysis Services instance permissions. Using a security group that contains the service principal for this purpose, doesn't work.

I'm not familiar with Azure DevOps. You could reference these documents. Hope they can help you.

The service principal is now available for Power BI Embedded

Embedding Power BI content with service principal and application secret

Power BI Developer community January update 

If these can't work, you could create a support ticket to get the dedicate help.

[KoenVdB]

Hi @v-xuding-msft 

i have created a support ticket, because i have checked all the things you mentioned.

Because also when the owner is changed for the dataset. The service principal is also allowed to refresh the dataset using an API call

[v-xuding-msft]

Hi @KoenVdB ,

Hope the issue will be resolved soon.  And please share the solution here while it is solved. More people will benefit from this thread.  Thanks in advance!