Reply
Member
Posts: 50
Registered: ‎04-15-2016
Accepted Solution

Possible to update credentials via REST API?

[ Edited ]

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.


Accepted Solutions
Moderator
Posts: 2,173
Registered: ‎03-06-2016

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


All Replies
Moderator
Posts: 2,173
Registered: ‎03-06-2016

Re: Possible to update credentials via REST API?

[ Edited ]

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

 

 

 

Member
Posts: 50
Registered: ‎04-15-2016

Re: Possible to update credentials via REST API?

Hey @v-lvzhan-msft,

 

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.

 

 

Member
Posts: 66
Registered: ‎11-11-2015

Re: Possible to update credentials via REST API?

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

 

Member
Posts: 50
Registered: ‎04-15-2016

Re: Possible to update credentials via REST API?

[ Edited ]

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

Member
Posts: 66
Registered: ‎11-11-2015

Re: Possible to update credentials via REST API?

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.

Moderator
Posts: 2,173
Registered: ‎03-06-2016

Re: Possible to update credentials via REST API?

[ Edited ]

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. 

Member
Posts: 50
Registered: ‎04-15-2016

Re: Possible to update credentials via REST API?

@v-lvzhan-msft 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.

Member
Posts: 66
Registered: ‎11-11-2015

Re: Possible to update credentials via REST API?

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.

 

 

 

Moderator
Posts: 2,173
Registered: ‎03-06-2016

Re: Possible to update credentials via REST API?

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