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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
ivanferr92
New Member

Power BI REST API Update datasource credentials

Hi,

 

Is it possible to use the Powerbi REST API to update the login credentials to a dataset?

I found something similar for gateway-managed dataset: https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/adddatasourceuser#example but all my dataset are on Azure Cloud (Azure SQL) so I can't use gateway.

 

Thank you! 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

HI @ivanferr92,

I also check the rest API documents but not found any apis to direct operate the data source credentials. (some of API can update the connection strings but they not mention/contains any parameters to operate on data credentials)
I guess power bi service may not want to share them due to security reasons.

Regards,

Xiaoxin Sheng

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

HI @ivanferr92,

I also check the rest API documents but not found any apis to direct operate the data source credentials. (some of API can update the connection strings but they not mention/contains any parameters to operate on data credentials)
I guess power bi service may not want to share them due to security reasons.

Regards,

Xiaoxin Sheng

Are you looking for Power BI .NET SDK v3 code to update datasource credentials. Here is a code snippet.

 

public static void PatchSqlDatasourceCredentials(Guid WorkspaceId, string DatasetId, string UserName, string UserPassword) {

      PowerBIClient pbiClient = TokenManager.GetPowerBiClient(requiredScopes);
      var datasources = (pbiClient.Datasets.GetDatasourcesInGroup(WorkspaceId, DatasetId)).Value;
      // find the target SQL datasource
      foreach (var datasource in datasources) {
        if (datasource.DatasourceType.ToLower() == "sql") {
          // get the datasourceId and the gatewayId
          var datasourceId = datasource.DatasourceId;
          var gatewayId = datasource.GatewayId;
          // Create UpdateDatasourceRequest to update Azure SQL datasource credentials
          UpdateDatasourceRequest req = new UpdateDatasourceRequest {
            CredentialDetails = new CredentialDetails(
              new BasicCredentials(UserName, UserPassword),
              PrivacyLevel.None,
              EncryptedConnection.NotEncrypted)
          };
          // Execute Patch command to update Azure SQL datasource credentials
          pbiClient.Gateways.UpdateDatasource((Guid)gatewayId, (Guid)datasourceId, req);
        }
      };

    }

 

Source code can be found here: https://github.com/TedPattison/DatasetManagement/blob/master/DatasetManagement/Models/PowerBiManager...

The above code throws me a BadRequest.
Has someone this working, so set the DataSource per model  (and not set the shared one)

btw I came up with the following implementation:

  public async Task PatchSqlDatasourceCredentials(Guid groupId, string datasetId, string reportingSqlServer, string reportingSqldatabase, string userName, string password)
  {
      var pbiClient = await GetPowerBIClient() ?? throw new PowerBiException(PowerBiClientIsNull);

      var datasources = (await pbiClient.Datasets.GetDatasourcesInGroupAsync(groupId, datasetId)).Value;

      var sqlDataSource = datasources.FirstOrDefault(d => d.DatasourceType.Equals("Sql", StringComparison.OrdinalIgnoreCase)) ?? throw new PowerBiException(PowerBiClientIsNull);

      var selectedServer = sqlDataSource.ConnectionDetails.Server;
      var selectedDatabase = sqlDataSource.ConnectionDetails.Database;

      if (!selectedServer.Equals(reportingSqlServer, StringComparison.OrdinalIgnoreCase) ||
          !selectedDatabase.Equals(reportingSqldatabase, StringComparison.OrdinalIgnoreCase))
      {
          await pbiClient.Datasets.UpdateDatasourcesInGroupAsync(groupId, datasetId, new UpdateDatasourcesRequest(new UpdateDatasourceConnectionRequest
          {
              DatasourceSelector = new Datasource
              {
                  DatasourceType = "Sql",
                  ConnectionDetails = new DatasourceConnectionDetails()
                  {
                      Server = selectedServer,
                      Database = selectedDatabase
                  }
              },
              ConnectionDetails = new DatasourceConnectionDetails()
              {
                  Server = reportingSqlServer,
                  Database = reportingSqldatabase
              },

          }));
      }

      datasources = (await pbiClient.Datasets.GetDatasourcesInGroupAsync(groupId, datasetId)).Value;
      sqlDataSource = datasources.FirstOrDefault(d => d.DatasourceType.Equals("Sql", StringComparison.OrdinalIgnoreCase)) ?? throw new PowerBiException(PowerBiClientIsNull);

      var datasourceId = sqlDataSource.DatasourceId!.Value;
      var gatewayId = sqlDataSource.GatewayId!.Value;

      // Create UpdateDatasourceRequest to update Azure SQL datasource credentials
      var updateDatasourceRequest = new UpdateDatasourceRequest
      {
          CredentialDetails = new CredentialDetails(
              new BasicCredentials(userName, password),
              PrivacyLevel.None,
              EncryptedConnection.NotEncrypted)
      };

      // Execute Patch command to update Azure SQL datasource credentials
      await pbiClient.Gateways.UpdateDatasourceAsync(gatewayId, datasourceId, updateDatasourceRequest);
  }

 

above code throw "Operation returned an invalid status code 'Unauthorized'  on "client.Gateways.UpdateDatasource"   event thou I have given all powerBI API permission include Tenant.ReadWrite.   I don't use the gateway, Is this the reason for the error?

Has anyone been able to solve this datasource update for a cloud (not on-prem) gateway?
In my case I need to update a GCP Service Account on a Google Big Quey datasource on a dataflow

 

A
Using the Gateways - Update Datasource "REST API Try It":
If I use the service principal I get a "PowerBINotAuthorizedException",

If I use my own creds, I get an error: DM_GWPipeline_Gateway_DataSourceAccessError.

I have not found an explanation for this in the cloud datasource context.  Does this API work at all for personal, cloud gateway datasources? It seems most of the gateway API's were designed around on-prem gateways.

 
B
Also, trying the same using Powershell, I'm getting a BadRequest (400). How to compile the basic credentials in the request body in PowerShell where the GCP service account key is an entire JSON file full of double quotes which need to be escaped or all enclosed in single quotes?

Any help would be most welcome please.
Also, I'm not sure if I should post this in a new topic?

There is still a gateway ID and you need to use the Gateway API even when there is no actual Gateway. 

 

Are you using service principal? If so, did you go through all the proper configuration to enable service principal access to Power BI API in your tenant and add servide principal as gatewway admin?

When there is no actual gateway, and it's a cloud datasource, I'm able to find a gateway ID as you say, interestingly enough.

However, when I try to update the datasource using this gateway ID + datasource ID, I always get an error: 400 DM_GWPipeline_Gateway_DataSourceAccessError. I also can't lookup this gateway using the get gateway method and retrieve its public key, which I need in order to encrypt the credentials.

Hi, Are you able to find the solution of this problem?

Anonymous
Not applicable

It's still not clear to me how this works as for Azure datasources, the docs say that the credentials are "per user" and so even if setting the credentals by an SP, wouldn't each user then have to enter the credentials themselves anyway?

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

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