Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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.
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.
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);
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:
@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.
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.
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.
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: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.
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.
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.
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:
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
20 | |
3 | |
2 | |
2 | |
2 |