topic Re: Possible to update credentials via REST API? in Developer

Possible to update credentials via REST API?

malagari — Thu, 09 Mar 2017 15:52:06 GMT

Does the current REST API support updating the data source credentials? I see that I'm able to update the connection string, but what about credentials? The only documentation I'm seeing is for credentials in a Data Gateway.

My main goal is to obfuscate the data source credentials from the end-user, and to deploy the .PBIX and all necessary connection information from the API.

Edit: To add clarification, I am talking about the PowerBI.com REST API, not the Embedded API.

Re: Possible to update credentials via REST API?

Eric_Zhang — Wed, 08 Mar 2017 07:29:50 GMT

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

Re: Possible to update credentials via REST API?

malagari — Wed, 08 Mar 2017 16:47:21 GMT

Hey @Eric_Zhang,

Thanks for following up and voting for my idea. I really think this is crucial for the programmability of Power BI (and being able to control it completely through the API). The Gateway API is close, but only works when using the On-premises Data Gateway. I'm looking to be able to update the credentials / connection string of any data source.

I guess I'll have to keep an eye out for future updates.

Re: Possible to update credentials via REST API?

TedPattison — Wed, 08 Mar 2017 21:13:51 GMT

Yes, I had to fight the code a bit but I got it working. This code allows me to update creds for connection to Azure SQL as required when doing DirectQuery.

static void UpdateAzureSqlDataSource(string workspaceCollectionName, string workspaceId, string datasetId) {
  using (var client = CreatePowerBIClient()) {
    IList<Dataset> datasets = client.Datasets.GetDatasetsAsync(workspaceCollectionName, workspaceId).Result.Value;
    foreach (Dataset dataset in datasets) {
      if (dataset.Name == datasetId) {
        var datasources = client.Datasets.GetGatewayDatasourcesAsync(workspaceCollectionName, workspaceId, dataset.Id).Result;
        // Reset your connection credentials
        var delta = new GatewayDatasource {
          CredentialType = "Basic",
          BasicCredentials = new BasicCredentials {
            Username = azureSqlUser,
            Password = azureSqlPassword
          }
        };
        // Update the datasource with the specified credentials
        client.Gateways.PatchDatasourceAsync(workspaceCollectionName, 
                                              workspaceId, 
                                              datasources.Value[0].GatewayId, 
                                              datasources.Value[0].Id,
                                              delta).Wait();
      }
    }
  }
}

The complete sample program is up in a GitHub report at this URL:

https://github.com/CriticalPathTraining/PowerBiEmbedded/blob/master/PBIEmbeddedDemo_Provisioning/PBIEmbeddedDemo_Provisioning/Program.cs

Re: Possible to update credentials via REST API?

malagari — Wed, 08 Mar 2017 21:58:56 GMT

@TedPattison - interesting. Your code makes it seem as though you're just using the same exact call to update "Gateway" credentials for datasets. Any idea.. is this strictly limited to DirectQuery still? I'm hoping to use something similar for an Azure Analysis Services live connection.

Re: Possible to update credentials via REST API?

TedPattison — Wed, 08 Mar 2017 23:28:16 GMT

I have used this code with imported datasets in Power BI as well as datasets that use DirectQuery against a Azure SQL database. When you have an imported dataset, you do not need to patch the data source credentials to make thing work at first because the data has already been imported into the Power BI cloud. Therefore, you only need to patch credentials for an imported datasets if you want to be able to refresh the dataset on demand or to schedule data refresh inside the Power BI service. DirectQuery datasets do not have imported data so you have to patch credentials for them to work at all.

This code was a bit confusing to me at first because you have to create a new GatewayDatasource object even when you are accessing an Azure SQL database without going through a Power BI Gateway.

My understanding is that this programming technique is also supposed to be used for datasets based on a live connections to a tabular database in Azure Ana;ysis Service. However, I have never tested it myself so I don't want to say it works until I have seen it work with my own eyes. I would love to hear from anyone in the Power BI community that has patched credentials to a Azure Analysis Services database.

Re: Possible to update credentials via REST API?

Eric_Zhang — Thu, 09 Mar 2017 01:21:29 GMT

Just have you two synced up.

@TedPattison

I think your code is for Power BI Embedded while I think @malagari is looking for the REST API for Power BI Service.

Re: Possible to update credentials via REST API?

malagari — Thu, 09 Mar 2017 15:37:27 GMT

@Eric_Zhang is correct, I am looking at the Power BI.com API, not the PBI Embedded API. While @TedPattison's code doesn't answer my question directly, it does provide some insight into the classes under-the-hood.

I'm going to spend a little bit of time poking around either way.

Re: Possible to update credentials via REST API?

TedPattison — Thu, 09 Mar 2017 19:50:03 GMT

I am trying to reverse engineer what works in Power BI embedded and get it working in PowerBi.com. I have not found the secret to succsss yet, but I am getting closer.

When I ran the code that works for Power BI embedded and examine the calls with Fiddler. As you mentioned, there is a call to Default.GetBoundGatewayDatasources.

/v1.0/collections/myWSC/workspaces/GUID-WS/datasets/GUID-DS/Default.GetBoundGatewayDatasources

The call to Default.GetBoundGatewayDatasources returns the following JSON.

{  "value": [
    {
      "id": "bcb4e3d7-f906-4255-b3b6-60c94db791bb",
      "gatewayId": "7d8b1437-fbbc-44a4-84d8-9738eff0e176",
      "datasourceType": "Sql",
      "connectionDetails": "{\"server\":\"cpt.database.windows.net\",\"database\":\"wingtipsalesdb\"}"
    }
  ]
}

So far, so good. But then I tried updating the data source credentials using the datasource_id and the gateway_id. I changed the REST URL to a PowerBI.com URL shown here and executed an HTTP patch operation.

/v1.0/collections/myWSC/workspaces/GUID-WS/gateways/GUID-GW/datasources/GUID-DS

I passed the exact same JSON body as was passed in the Power BI embedde code .

{
  "credentialType": "Basic",
  "basicCredentials": {
    "username": "CptStudent",
    "password": "pass@word1"
  }
}

When I try to make this call, I get the following error .

{
  "error": {
    "code": "BadRequest",
    "message": "Bad Request",
    "details": [
      {
        "message": "'datasourceDelta' is a required parameter",
        "target": "datasourceDelta"
      }
    ]
  }
}

I am not exectly sure how to interpret this error, but it seems like the parameter named datasourceDelta needs to be added into the request body or into the URL.

Getting closer, but not there yet.

Re: Possible to update credentials via REST API?

Eric_Zhang — Fri, 10 Mar 2017 00:23:33 GMT

@TedPattison

datasourceDelta is where I've reached, then no further findings. It would be great if you could share any further findings, meanwhiling I'm trying to consult the product team.

Re: Possible to update credentials via REST API?

Eric_Zhang — Wed, 15 Mar 2017 02:46:26 GMT

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

Re: Possible to update credentials via REST API?

malagari — Thu, 16 Mar 2017 21:21:22 GMT

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

Re: Possible to update credentials via REST API?

TedPattison — Fri, 17 Mar 2017 15:11:24 GMT

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.

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

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.

Re: Possible to update credentials via REST API?

Jeff_973 — Thu, 03 Aug 2017 22:15:41 GMT

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

Re: Possible to update credentials via REST API?

Anuj_Shaubhari — Fri, 17 Aug 2018 09:17:24 GMT

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);

Re: Possible to update credentials via REST API?

ddonahue — Tue, 28 Aug 2018 22:59:48 GMT

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

Re: Possible to update credentials via REST API?

xhead — Tue, 10 Dec 2019 21:28:58 GMT

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

Re: Possible to update credentials via REST API?

xhead — Wed, 18 Dec 2019 18:28:38 GMT

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.

Re: Possible to update credentials via REST API?

Anonymous — Wed, 03 Mar 2021 08:25:55 GMT

Has anyone managed to get the PATCH for datasource credentials for powerbi.com (NOT embedded) working yet? I'm trying to patch a datasource which connects to azure sql db directly. It'll take ownership and update the server ok, but it won't change the credentials or set the sso tickbox.

Re: Possible to update credentials via REST API?

jessegorter — Wed, 30 Jun 2021 14:33:03 GMT

this works actually, but do make sure the service principal is the owner of the dataset, otherwise you get a 401