cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Moderator Eric_Zhang
Moderator

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

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
Advisor

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 Smiley Wink

 

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
Frequent Visitor

Re: Possible to update credentials via REST API?

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

Anuj_Shaubhari Frequent Visitor
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
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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 359 members 3,917 guests
Please welcome our newest community members: