Forum Discussion
Possible to update credentials via REST API?
- 9 years ago
@v-lvzhan-msft wrote:I've voted for your idea.
Before I acutally did some research and thought I was close as I find this api Set Credentials. The datasource_id and gateway_id can be found via calling Get BoundGatewayDatasources. However when calling the Set Credential API, I've stuck by a "DMTS_InvalidEncryptionAlgorithmError" error. I'm going to escalate this internally and will post back if there comes any update.
By the way, I don't find any API to create a data source for a dataset in a gateway, even though above two APIs work, We still need such an API otherwise we may have to configure the datasource manually.
Here's the update from the Product team. The SET Credential API is for cloud datasources.
Based on my test, I can set credential with the API when the dataset is connecting to a Azure SQL database.
to get the Datasource and gateway id the user should use this API:
GET https://api.powerbi.com/v1.0/myorg/datasets/{dataset_id}/Default.GetBoundGatewayDataSources
and then to use the set credentials API.
PATCH https://api.powerbi.com/v1.0/myorg/gateways/{gateway_id}/datasources/{datasource_id}
I thought the gateway_id in the PATCH API was an real gateway. While I got the declarification that It's not a real GW in this case, but this is what the API refers to.
So if you're using SQL Azure database, then I think you can obfuscate the data source credentials from the end-user.
Ok, I'm trying to implement this in PowerShell, and hopefully for both Basic credentials and OAuth2.
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:
- 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.
- When using OAuth2 authentication, I assume I need one of the following set to true in the patch Json: useCallerAADIdentity or useEndUserOAuth2Credentials.
- 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.
- 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
I haven't heard from anyone on this. I'm going to also start a new thread to see if I can get some response.