cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
Microsoft

Re: Possible to update credentials via REST API?


@v-lvzhan-msft wrote:

@malagari

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.

 

 

 


@malagari@TedPattison

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.

 

@malagari

So if you're using SQL Azure database, then I think you can obfuscate the data source credentials from the end-user.

 

 

View solution in original post

malagari Resolver IV
Resolver IV

Re: Possible to update credentials via REST API?

Thanks @Eric_Zhang. I'll give this a shot with Postman and see how it turns out.

Dan Malagari
Consultant at Headspring
TedPattison Responsive Resident
Responsive Resident

Re: Possible to update credentials via REST API?

After a few hours coding, I can say that I have succesffully written the code to patch credentials for a SQL Azure dataset in PowerBI.com running in DirectQuery mode. I have posted a simple C# console application to use as a reference project at https://github.com/CriticalPathTraining/PbixInstallerForPowerBI.

 

You can see all the code required in a single C# file at here

 

First, you need to retrieve the Gateway ID and the Datasource ID for the target dataset to be updated.

 

fiddler1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After that, you use the gateway id and the datasource ID to execute a HTTP PATCH operation to update the credentials.

 

fiddler2.png

 

After that, bob's your unkle and the DirectQuery report is able to connect to the SQL Azure database. For me, the confusing part is that you must reference a gateway ID even though there isn't really a Power BI gateway invovled such as a personal gateway or the On-premises gateway. I guess it's a conceptual gateway. It's also a bit ironic that it doesn't work yet if an actual gateway is involved 😉

 

The unfortunate part about this is that there is a handy .NET API that wraps all these Power BI REST calls and makes it much easier to code. But this API only works for Power BI Embedded and not for PowerBI.com even though the underlying REST calls between PowerBI.com and Power BI Embedded are almost identical. My guess is that Microsoft needed to get this API out the door in a hurry to give customers traction in getting started with Power BI Embedded. Hopefully, this Power BI .NET API will be modified in the future to use PowerBI.com workspaces in additon to Power BI Embedded worksspces. Until then we will have to write our code just as I have in the PbixInstallerForPowerBI reference application.

 

Jeff_973
Frequent Visitor

Re: Possible to update credentials via REST API?

Is it possible to update the connection details via the patch web request? 

Highlighted
Anuj_Shaubhari
Frequent Visitor

Re: Possible to update credentials via REST API?

Update Power BI dataset Connection with Power BI Service.



var error = GetWebConfigErrors();
if (error != null)
{
return View(new EmbedConfig()
{
ErrorMessage = error
});
}

// Create a user password cradentials.
var credential = new UserPasswordCredential(Username, Password);

// Authenticate using created credentials
var authenticationContext = new AuthenticationContext(AuthorityUrl);
var authenticationResult = await authenticationContext.AcquireTokenAsync(ResourceUrl, ClientId, credential);

if (authenticationResult == null)
{
return View(new EmbedConfig()
{
ErrorMessage = "Authentication Failed."
});
}

var tokenCredentials = new TokenCredentials(authenticationResult.AccessToken, "Bearer");

string connectionString = ""data source=MyServer.database.windows.net;initial catalog=MyDatabase;persist security info=True;encrypt=True;trustservercertificate=False"";


// Create a Power BI Client object. It will be used to call Power BI APIs.
using (var client = new PowerBIClient(new Uri(ApiUrl), tokenCredentials))
{
// Get the newly created dataset from the previous import process
var datasets = await client.Datasets.GetDatasetsInGroupAsync(GroupId);

// Optionally udpate the connectionstring details if preent
if (!string.IsNullOrWhiteSpace(connectionString))
{
var connectionParameters = new ConnectionDetails
{
ConnectionString = connectionString
};

await client.Datasets.SetAllDatasetConnectionsInGroupAsync(GroupId, datasets.Value[0].Id, connectionParameters);
}


// Get the datasources from the dataset
var datasources = await client.Datasets.GetGatewayDatasourcesAsync(GroupId, datasets.Value[0].Id);

// Reset your connection credentials
var delta = new UpdateDatasourceRequest
{
CredentialDetails = new CredentialDetails
{
CredentialType = "Basic",
Credentials = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"anuj\"},{\"name\":\"password\", \"value\":\"******\"}]}",
EncryptedConnection = "Encrypted",
EncryptionAlgorithm = "None",
PrivacyLevel = "None"
}
};


// Update the datasource with the specified credentials
await client.Gateways.UpdateDatasourceAsync(datasources.Value[datasources.Value.Count - 1].GatewayId, datasources.Value[datasources.Value.Count - 1].Id, delta);

ddonahue
Frequent Visitor

Re: Possible to update credentials via REST API?

@TedPattison - This was incredibly helpful, thank you for taking the time to document your experience. For the record, this worked for me in updating creds for a Redshift connection.

xhead Helper I
Helper I

Re: Possible to update credentials via REST API?

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:

  1. 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.
  2. When using OAuth2 authentication, I assume I need one of the following set to true in the patch Json: useCallerAADIdentity or useEndUserOAuth2Credentials.
    1. 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. 
  3. 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

xhead Helper I
Helper I

Re: Possible to update credentials via REST API?

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.