Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
malagari
Responsive Resident
Responsive Resident

Possible to update credentials via REST API?

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.

Dan Malagari
Consultant at Headspring
1 ACCEPTED SOLUTION


@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

21 REPLIES 21
Anonymous
Not applicable

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.

Anuj_Shaubhari
Frequent Visitor

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

TedPattison
Employee
Employee

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

 

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

Dan Malagari
Consultant at Headspring

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.

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. 

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

Dan Malagari
Consultant at Headspring
Eric_Zhang
Employee
Employee

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

 

 

 


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

 

 

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

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

Dan Malagari
Consultant at Headspring

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.

 

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

not sure if you are still interested, but I got it to work, but did not need to pass headers to the api call. be sure that the dataset is owned by the service principal, and that you connect to power bi rest api first with the sp

Anonymous
Not applicable

Yes I am interested, I would be curious to know which code you used ( @TedPattison  or @xhead ) or your own. Also if you think it could be modified for other types datasources.

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.

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

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

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.

 

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.