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 using basic auth.
But I am not able to update the cred to OAUTh type for either Azure DB or AAS .
So can some one please help me the same .

Below is my code :

 

$datasetname="xyz"
$workspacename="ABC"


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

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

$workspace =Get-PowerBIWorkspace -Name $workspacename

$token = Get-PowerBIAccessToken

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


$DatasetResponse=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets" -Method Get | ConvertFrom-Json

$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


$UpdateUserCredential = @{
credentialType ="OAUTH2"
CredentialData = @{
name= "accessToken"
value= $accessToken
}
} | ConvertTo-Json

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

 

Need Help

7 Replies

  • 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 🙂

    • Jayendran's avatar
      Jayendran
      Solution Sage
      6 years ago

      Hi NandanHegde ,

       

      Could you please explain what do you mean by unable to work / not working ? Are you seeing any error ? Pls share that also

       

      What is your data source ? Is that a sharepoint where you are trying to modify the OAuth2 token ?

      • NandanHegde's avatar
        NandanHegde
        Super User
        6 years ago

        Hi Jayendran 

        My datasources are Azure SQL database and Azure Analysis services for which I need to update the credentials via OAUTH2.

         

        Basically after updating the datasource details , I am trying to update the credentials of Azure SQL database which was successful via basic Auth but not via OAUTh.

        As a part of security measure we are moving away from basic auth to OAuth in our organization for databases.

         

        So I am unable to update the creds via OAUTH.

         

        When I tried executing the below powershell query, I am getting the error:

        HTTPS: bad request.

         

        So I am not sure what I am doing wrong there?
        And no where there is any example or documentation for updating of creds via OAUTh.

        So wanted your help for the same.

    • 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