Update Credential to OAUTH2 type via REST API

[NandanHegde]

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

[NandanHegde]

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

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]

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]

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]

@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?

[Jayendran]

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]

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.

[Jayendran]

Hi @NandanHegde ,

I'm not sure the powerbi access token is correct token for sql.

Can you try the below link for the sql token and pass it to the API.

https://techcommunity.microsoft.com/t5/azure-sql-database/azure-ad-service-principal-authentication-...