Forum Discussion

NandanHegde's avatar
NandanHegde
Super User
6 years ago

Update Credential to OAUTH2 type via REST API

Hello, I am trying to update the credentials of an Azure SQL database and Azure Analysis services via REST API through powershell. I was able to successfully update the cred of Azure SQL database u...
Need Help
NandanHegde's avatar
NandanHegde
Super User
6 years ago

I also tried the below scenarios wherein :

Initially generated the access token for the user :

$password = "” | ConvertTo-SecureString -asPlainText -Force

$username = ""

$credentialSelf = New-Object System.Management.Automation.PSCredential($username, $password)

 

Login-PowerBI -Credential $credentialSelf

 

$headers = Get-PowerBIAccessToken

 

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

 

Then implemented the below code to update the cred:


$datasetname="<<name>>"
$workspacename="PowerAutomation"


$clientsec = "$(ClientSecret)" | ConvertTo-SecureString -AsPlainText -Force

$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "<<Client ID>>", $clientsec
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId "<<tenantID>>"

$workspace =Get-PowerBIWorkspace -Name $workspacename

$workspace


# 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;
}

}


$BounGateway=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.GetBoundGatewayDataSources" -Method GET | ConvertFrom-Json

 


$Body =
@{
credentialDetails = @{
credentialType = "OAuth2";
credentials = "{`"credentialData`":[{`"name`":`"accessToken`", `"value`":`"$(Accesstoken)`"}]}";
encryptedConnection = "Encrypted";
encryptionAlgorithm = "None";
privacyLevel = "None"
}
} | ConvertTo-Json

 


Invoke-PowerBIRestMethod -Url "gateways/$($BounGateway.value.gatewayId)/datasources/$($BounGateway.value.id)" -Method PATCH -Body $Body | ConvertFrom-Json

 

I referred the below link for the logic:

https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/updatedatasource#basic-credentials-example

which was succesful for key and basic auth but unable to update for OAuth.

 

So can someone help me with that?

Jayendran  : I referred to your code for Power BI automation but it had only cred update via basic auth.
So can you please help me with this 🙂

MGroeneveld's avatar
MGroeneveld
Frequent Visitor
6 years ago

Hi NandanHegde 

 

You wrote it was succesful for key and basic. Can you share your code for key? I got it working for basic, but it won't work for key.

 

I used this code for 'basic':

 

$UpdateUserCredentialBasic = @{
credentialType ="Basic"
basicCredentials = @{
username= '$(username)'
password= '$(password)'
}} | ConvertTo-Json;

 

Thanks in advance. Unfortunately I cannot help you with your problem. 

  • NandanHegde's avatar
    NandanHegde
    Super User
    6 years ago

    Hi MGroeneveld :

    Please refer to the below code for key pair:

    $UpdateUserCredential =
    @{
    credentialDetails = @{
    credentialType = "Key";
    credentials = "{`"credentialData`":[{`"name`":`"key`", `"value`":`"$(key)`"}]}";
    encryptedConnection = "Encrypted";
    encryptionAlgorithm = "None";
    privacyLevel = "None"
    }
    } | ConvertTo-Json

    where $(key) is the access key passed as a parameter in Azure devops

     

    Hope this provides you a clarity 

     

    • MGroeneveld's avatar
      MGroeneveld
      Frequent Visitor
      6 years ago

      NandanHegde 

       

      Thanks for the reply. I used the code as you suggested. The pipeline deploys succesful, however the credentials aren't properly updated. On the powerBI website, I still get the message that the credentials of one of my datasources are missing. 

       

      Do I miss something else? Do I need to change some policies of access control settings?