Forum Discussion

ivanferr92's avatar
ivanferr92
New Member
6 years ago
Solved

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/powe...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • TedPattison's avatar
    TedPattison
    Microsoft Employee
    6 years ago

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

    • paritoshmishra's avatar
      paritoshmishra
      Helper I
      5 years ago

      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?

      • TedPattison's avatar
        TedPattison
        Microsoft Employee
        5 years ago

        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?

    • BartHuls's avatar
      BartHuls
      Helper I
      1 year ago

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

      • BartHuls's avatar
        BartHuls
        Helper I
        1 year ago

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