Unable to update credentials via REST API?

[xhead]

I'm trying to use PowerShell to update the credentials for a datasource that is in a dataset in a workspace in PowerBI.Com, and hopefully for both Basic credentials and OAuth2. Right now I'm focusing on a dataset that has a single SQL data source, which is an Azure SQL database.

(This is a copy of a forum post here, that I'm reposting as new issue, since I'm not getting any response to the other post.)

I'm basing my code mostly from the documentation on this call here: https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/updatedatasource and also on the C# code here. by @TedPattison 

I'm getting an error in both my attempts, first using OAuth2, and second using Basic.

My assumptions in this code:

1. When using Basic authentication for a data source of type SQL, I assume that the username/password should be a valid SQL login name and password.
2. When using OAuth2 authentication, I assume I need one of the following set to true in the patch Json: useCallerAADIdentity or useEndUserOAuth2Credentials.
   1. I also assume that the value for AccessToken in the credentials attribute is the same access token that is used in the Authorization header (without the prefix "Bearer "), if I'm using useCallerAADIdentity: true. This assumption seems to be a big one - the docs don't say anything about how to generate the access token for the PATCH. 
3. I assume you can use this PATCH call to change from Basic to OAuth2 authentication, and vice versa. Or that the existing data source doesn't have any valid authentication defined for it (such as the case when you have just published the PBIX file and you haven't gone to the dataset settings in the service).

The error I get for Basic authentication is this response:

Response: 400: Bad Request

Response Body:

41
{"error":{"code":"BadRequest","pbi.error":{"code":"BadRequest"}}}
0

The error I get for OAuth2 authentication is this response:

Response: 400: Bad Request

Response Body:

77
{"error":{"code":"InvalidRequest","message":"Specifying UseCallerOAuthIdentity requires credential type to be OAuth2"}}
0

Here is the code:

$powerbiUrl = "https://api.powerbi.com/v1.0"

Function Update-PowerBIGatewayDataSourceCredentials {
    Param(
        [parameter(Mandatory = $true)]$gatewayId,
        [parameter(Mandatory = $true)]$datasourceId,
        [parameter(Mandatory = $true)]$AccessToken,
        [parameter(Mandatory = $true)]$credentialType,
        [parameter(Mandatory = $false)]$userName,
        [parameter(Mandatory = $false)]$password
     )

    # PATCH https://api.powerbi.com/v1.0/myorg/gateways/{gatewayId}/datasources/{datasourceId}
    $url = $powerbiUrl + "/myorg/gateways/$gatewayId/datasources/$datasourceId"

    if ($credentialType -eq "OAuth2") {
$body = @"
{
    "credentialDetails": 
    {
        "credentialType": "OAuth2",
        "credentials": "{ \"credentialData\": [{\"name\":\"accessToken\", \"value\": \"$accessToken\"}]}",
        "useCallerAADIdentity" : true
    }
}
"@

}

if ($credentialType -eq "Basic") {

$body = @"
{
    "credentialDetails": 
    {
        "credentialType": "Basic",
        "credentials": "{ \"credentialData\": [{\"name\":\"username\", \"value\": \"$userName\"},{\"name\":\"password\", \"value\": \"$password\"}]}"
    }
}
"@

}


 $apiHeaders = @{
        'Content-Type'  = 'application/json'
        'Accept'  = 'application/json'
        'Authorization' = "Bearer $AccessToken"
    }

    $result = Invoke-RestMethod -Uri $Url -Headers $apiHeaders -Method "Patch" -Body $Body

}

# ********************************************************************************************
#
# Main entry point
#
# ********************************************************************************************

# existing Power BI report with a single connection for a SQL Azure database. 
# datasource is Import mode, not DirectQuery mode.

$workspaceName = "<a PowerBI workspace>"
$datasetName = "<a published dataset>"

#SQL login and password
$userName = "<sql login>"
$password = "<password for sql login>"


try {
    $token = Get-PowerBIAccessToken 
} 
catch [System.Exception] {
    Connect-PowerBIServiceAccount
    $token = Get-PowerBIAccessToken 
}

$accessToken = $token.Values -replace "Bearer ", ""

$ws = Get-PowerBIWorkspace | Where {$_.Name -eq $workspaceName }

$dataset = Get-PowerBIDataset -WorkspaceId $ws.Id | where {$_.Name -eq $datasetName }

$ds = Get-PowerBIDatasource -WorkspaceId $ws.Id -DatasetId $dataset.Id

$ds 

# set credentials using OAuth2 
Update-PowerBIGatewayDataSourceCredentials -gatewayId $ds.GatewayId -datasourceId $ds.DatasourceId -AccessToken $accessToken -credentialType "OAuth2" 

# set credentials using Basic (SQL login and password)

Update-PowerBIGatewayDataSourceCredentials -gatewayId $ds.GatewayId -datasourceId $ds.DatasourceId -AccessToken $accessToken -credentialType "Basic" -userName  $Username -password $password

Can anyone see what I'm doing wrong? 

Mike

[Jayendran]

Hi @xhead ,

If you want you can use my powershell script where i did the same Basic update credentials without having any issues

$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 
 
 
## update parameter API 
 
   $postParams = @{ 
            updateDetails =@( 
            @{ 
            name="blob" 
            newValue="https://demo.blob.core.windows.net/" 
            } 
            ) 
} | ConvertTo-Json 
 
 
 
Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.UpdateParameters" -Method Post -Body $postParams | ConvertFrom-Json 
 

[Anonymous]

The docs specify the following Request Body schema for OAuth2 authentication:

$UpdateUserCredential = @{

    credentialDetails = @{

        credentialType = "OAuth2"

        credentials = "{`"credentialData`":[{`"name`":`"accessToken`", `"value`":`"$token`"}]}"

        encryptedConnection = "Encrypted"

        encryptionAlgorithm = "None"

        privacyLevel = "None"

    }

} | ConvertTo-Json

Like you I'm also extracting the access token from Authorization Header (tried with and without Bearer prefix) however I'm getting the error "value=The credentials provided for the SQL source are invalid. (Source at ServerName;DatabaseName.)" despite having granted permissions to the account.

Good luck.