Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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:
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "<<Client ID>>", $clientsec $workspace =Get-PowerBIWorkspace -Name $workspacename $workspace
foreach($dataset in $datasets){ }
|
I referred the below link for the logic:
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 🙂
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.
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
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?
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 ?
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
3 | |
1 | |
1 | |
1 |
User | Count |
---|---|
26 | |
3 | |
2 | |
2 | |
2 |