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

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

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