POC Service Principal Export and Import a DataFlow

[bmukes]

I am trying to test exporting and importing a DataFlow from one workspace to another using a service principal.

I can successfully export the DataFlow.

• I am calling the api Imports - Post Import In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs to import the DataFlow.
• I am using the nuget package Microsoft.PowerBI.Api, Version=4.5.0.0 using C# 8 to make the call.

The call does not throw an exception and returns me an Import instance containing a guid but I do not see the imported DataFlow in the workspace.

I am guessing that I have a permission issue.  The documentation indicates that the application registration needs permissions Dataset.ReadWrite.All but the screen shot from AAD shows that those permissions apply when the application needs to access the API as the signed-in user. 

This is an POC to determine if an Azure Synapse Pipeline (using a service principal) can automate the creation of ISV workspaces by copying DataFlows and reports from a template workspace into client workspaces.

 Is there another API I should be using? or is this just not possible using a service principal with just clientid and client secret and without using a signed in user?

[bmukes]

Wonder if the Service Principal Limitations listed here Embed Power BI content in an embedded analytics application with service principal and an applicatio... are the issue.  Could it be that importing a DataFrame counts as DataFlow management?  If that was the case why let me export it.

[bmukes · Accepted solution]

Ok this took lots of GoogleFU.  Basically I had to read and understand all of the materials related to service principals, application registrations, API scopes, and then do a test once I thought I had it figured out.  If I did not have my own Visual Studio Pro account setting this up would not be possible.  

So in my POC I wanted to export/import dataflows using a service principal.

1. Is this possible?  No not with just a service principal.  It MIGHT be possible with a manged identity but not something I need to test at the moment.
2. Is this possible using a the GitHub - PowerBiDevCamp/App-Owns-Data-Starter-Kit: App-Owns-Data Starter Kit is a developer sample d... YES because that application forces you to login to AAD.
   1. Follow the normal setup instructions and ensure you have put the Power BI API permissions on the application registration that you need.  I put all of them on for now.
   2. You have to get an access token for the logged in user see the HomeController.cs file.
   3. You have to pass that access token to the PowerBIClient CTOR
   4. Then your export and import calls will work. 

I guess figuring out if this will work with a managed identity is next.

I included code for two files you can play with if you want to try this to see that it works

HomeController.cs

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using AppOwnsDataShared.Models;
using AppOwnsDataShared.Services;
using AppOwnsDataAdmin.Models;
using AppOwnsDataAdmin.Services;
using Microsoft.Identity.Web;

namespace AppOwnsDataAdmin.Controllers {

  [Authorize]
  public class HomeController : Controller {

    private PowerBiServiceApi powerBiServiceApi;
    private AppOwnsDataDBService AppOwnsDataDBService;
    ITokenAcquisition _tokenAcquisition;
    public HomeController(PowerBiServiceApi powerBiServiceApi, AppOwnsDataDBService AppOwnsDataDBService, ITokenAcquisition tokenAcquisition) {
      this.powerBiServiceApi = powerBiServiceApi;
      this.AppOwnsDataDBService = AppOwnsDataDBService;
      this._tokenAcquisition = tokenAcquisition;
    }

    [AllowAnonymous]
    public IActionResult Index() {
      return View();
    }

    public IActionResult Tenants() {
      var model = AppOwnsDataDBService.GetTenants();
      return View(model);
    }

    public IActionResult Tenant(string Name) {
      var model = AppOwnsDataDBService.GetTenant(Name);
      var modelWithDetails = powerBiServiceApi.GetTenantDetails(model);
      return View(modelWithDetails);
    }

    public class OnboardTenantModel {
      public string TenantName { get; set; }
      public string SuggestedDatabase { get; set; }
      public List<SelectListItem> DatabaseOptions { get; set; }
      public string SuggestedAppIdentity { get; set; }
      public List<SelectListItem> AppIdentityOptions { get; set; }
    }

    public IActionResult OnboardTenant() {

      var model = new OnboardTenantModel {
        TenantName = this.AppOwnsDataDBService.GetNextTenantName(),
        SuggestedDatabase = "WingtipSales",
        DatabaseOptions = new List<SelectListItem> {
          new SelectListItem{ Text="AcmeCorpSales", Value="AcmeCorpSales" },
          new SelectListItem{ Text="ContosoSales", Value="ContosoSales" },
          new SelectListItem{ Text="MegaCorpSales", Value="MegaCorpSales" }
        }
      };

      return View(model);
    }

    [HttpPost]
        [AuthorizeForScopes(Scopes = new[] { "https://analysis.windows.net/powerbi/api/.default" })]
        public IActionResult OnboardTenant(string TenantName, string DatabaseServer, string DatabaseName, string DatabaseUserName, string DatabaseUserPassword) {

            var accessToken = _tokenAcquisition.GetAccessTokenForUserAsync(new string[] { "https://analysis.windows.net/powerbi/api/.default" }).Result;
            var tenant = new PowerBiTenant {
        Name = TenantName,
        DatabaseServer = DatabaseServer,
        DatabaseName = DatabaseName,
        DatabaseUserName = DatabaseUserName,
        DatabaseUserPassword = DatabaseUserPassword,
      };
            tenant = this.powerBiServiceApi.OnboardNewTenant(tenant, accessToken);
      this.AppOwnsDataDBService.OnboardNewTenant(tenant);

      return RedirectToAction("Tenants");

    }

    public IActionResult DeleteTenant(string TenantName) {
      var tenant = this.AppOwnsDataDBService.GetTenant(TenantName);
      this.powerBiServiceApi.DeleteWorkspace(tenant);
      this.AppOwnsDataDBService.DeleteTenant(tenant);
      return RedirectToAction("Tenants");
    }

    public IActionResult Embed(string AppIdentity, string Tenant) {
      var viewModel = this.powerBiServiceApi.GetReportEmbeddingData(AppIdentity, Tenant).Result;
      return View(viewModel);
    }


    public IActionResult Users() {
      var model = AppOwnsDataDBService.GetUsers();
      return View(model);
    }
    public IActionResult GetUser(string LoginId) {
      var model = AppOwnsDataDBService.GetUser(LoginId);
      return View(model);
    }

    public class EditUserModel {
      public User User { get; set; }
      public List<SelectListItem> TenantOptions { get; set; }
    }

    public IActionResult EditUser(string LoginId) {
      var model = new EditUserModel{
        User = AppOwnsDataDBService.GetUser(LoginId),
        TenantOptions = this.AppOwnsDataDBService.GetTenants().Select(tenant => new SelectListItem {
                Text = tenant.Name,
                Value = tenant.Name
              }).ToList(),
      };
      return View(model);
    }

    [HttpPost]
    public IActionResult EditUser(User user) {
      var model = AppOwnsDataDBService.UpdateUser(user);
      return RedirectToAction("Users");
    }



    public class CreateUserModel {
      public List<SelectListItem> TenantOptions { get; set; }
    }

    public IActionResult CreateUser() {
      var model = new CreateUserModel {
        TenantOptions = this.AppOwnsDataDBService.GetTenants().Select(tenant => new SelectListItem {
          Text = tenant.Name,
          Value = tenant.Name
        }).ToList(),
      };
      return View(model);
    }

    [HttpPost]
    public IActionResult CreateUser(User user) {
      var model = AppOwnsDataDBService.CreateUser(user);
      return RedirectToAction("Users");
    }

    public IActionResult DeleteUser(string LoginId) {
      var user = this.AppOwnsDataDBService.GetUser(LoginId);
      this.AppOwnsDataDBService.DeleteUser(user);
      return RedirectToAction("Users");
    }

    [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
    public IActionResult Error() {
      return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
    }
  }
}

PowerBiServiceApi.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Threading.Tasks;
using Newtonsoft.Json;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.PowerBI.Api;
using Microsoft.PowerBI.Api.Models;
using Microsoft.PowerBI.Api.Models.Credentials;
using Microsoft.Rest;
using AppOwnsDataShared.Models;
using Microsoft.Identity.Web;
using AppOwnsDataShared.Services;

using Newtonsoft.Json.Linq;

namespace AppOwnsDataAdmin.Services {

  public class EmbeddedReportViewModel {
    public string ReportId;
    public string Name;
    public string EmbedUrl;
    public string Token;
    public string TenantName;
  }

  public class PowerBiTenantDetails : PowerBiTenant {
    public IList<Report> Reports { get; set; }
    public IList<Dataset> Datasets { get; set; }
    public IList<GroupUser> Members { get; set; }
  }

  public class PowerBiServiceApi {

    private readonly AppOwnsDataDBService AppOwnsDataDBService;
    private readonly IConfiguration Configuration;
    private readonly IWebHostEnvironment Env;

    private ITokenAcquisition tokenAcquisition { get; }
    private string urlPowerBiServiceApiRoot { get; }

    public PowerBiServiceApi(IConfiguration configuration, ITokenAcquisition tokenAcquisition, AppOwnsDataDBService AppOwnsDataDBService, IWebHostEnvironment env) {
      this.Configuration = configuration;
      this.urlPowerBiServiceApiRoot = configuration["PowerBi:ServiceRootUrl"];
      this.tokenAcquisition = tokenAcquisition;
      this.AppOwnsDataDBService = AppOwnsDataDBService;
      this.Env = env;
    }

    public const string powerbiApiDefaultScope = "https://analysis.windows.net/powerbi/api/.default";

    public string GetAccessToken() {
      return this.tokenAcquisition.GetAccessTokenForAppAsync(powerbiApiDefaultScope).Result;
    }

    public PowerBIClient GetPowerBiClient() {
      var tokenCredentials = new TokenCredentials(GetAccessToken(), "Bearer");
      return new PowerBIClient(new Uri(urlPowerBiServiceApiRoot), tokenCredentials);
    }

    public async Task<EmbeddedReportViewModel> GetReport(Guid WorkspaceId, Guid ReportId) {

      PowerBIClient pbiClient = GetPowerBiClient();

      // call to Power BI Service API to get embedding data
      var report = await pbiClient.Reports.GetReportInGroupAsync(WorkspaceId, ReportId);

      // generate read-only embed token for the report
      var datasetId = report.DatasetId;
      var tokenRequest = new GenerateTokenRequest(TokenAccessLevel.View, datasetId);
      var embedTokenResponse = await pbiClient.Reports.GenerateTokenAsync(WorkspaceId, ReportId, tokenRequest);
      var embedToken = embedTokenResponse.Token;

      // return report embedding data to caller
      return new EmbeddedReportViewModel {
        ReportId = report.Id.ToString(),
        EmbedUrl = report.EmbedUrl,
        Name = report.Name,
        Token = embedToken
      };
    }

    public Dataset GetDataset(PowerBIClient pbiClient, Guid WorkspaceId, string DatasetName) {
      var datasets = pbiClient.Datasets.GetDatasetsInGroup(WorkspaceId).Value;
      foreach (var dataset in datasets) {
        if (dataset.Name.Equals(DatasetName)) {
          return dataset;
        }
      }
      return null;
    }

    public async Task<IList<Group>> GetTenantWorkspaces(PowerBIClient pbiClient) {
      var workspaces = (await pbiClient.Groups.GetGroupsAsync()).Value;
      return workspaces;
    }

    public PowerBiTenant OnboardNewTenant(PowerBiTenant tenant,string accessToken) {

      PowerBIClient pbiClient = this.GetPowerBiClient();

      // create new app workspace
      GroupCreationRequest request = new GroupCreationRequest(tenant.Name);
      Group workspace = pbiClient.Groups.CreateGroup(request);

      tenant.WorkspaceId = workspace.Id.ToString();
      tenant.WorkspaceUrl = "https://app.powerbi.com/groups/" + workspace.Id.ToString() + "/";

      // add user as new workspace admin to make demoing easier
      string adminUser = Configuration["DemoSettings:AdminUser"];
      if (!string.IsNullOrEmpty(adminUser)) {
        pbiClient.Groups.AddGroupUser(workspace.Id, new GroupUser {
          EmailAddress = adminUser,
          GroupUserAccessRight = "Admin"
        });
      }

      // upload sample PBIX file #1
      string pbixPath = this.Env.WebRootPath + @"/PBIX/SalesReportTemplate.pbix";
      string importName = "Sales";
      PublishPBIX(pbiClient, workspace.Id, pbixPath, importName);

      Dataset dataset = GetDataset(pbiClient, workspace.Id, importName);

      UpdateMashupParametersRequest req =
        new UpdateMashupParametersRequest(new List<UpdateMashupParameterDetails>() {
          new UpdateMashupParameterDetails { Name = "DatabaseServer", NewValue = tenant.DatabaseServer },
          new UpdateMashupParameterDetails { Name = "DatabaseName", NewValue = tenant.DatabaseName }
      });

      pbiClient.Datasets.UpdateParametersInGroup(workspace.Id, dataset.Id, req);

      PatchSqlDatasourceCredentials(pbiClient, workspace.Id, dataset.Id, tenant.DatabaseUserName, tenant.DatabaseUserPassword);

      pbiClient.Datasets.RefreshDatasetInGroup(workspace.Id, dataset.Id);

    try
    {
        string dataflowId = "67ff3929-25f7-434f-95f1-0cb4abe12d92";
        ImportExportDataFlow(new Guid(dataflowId), workspace.Name, workspace.Id,accessToken);
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debugger.Break();
    }


      return tenant;
    }
        void ImportExportDataFlow(Guid dataFlowId, string name,Guid workflowId, string accessToken)
        {
            //string[] scopes = new string[] { "https://analysis.windows.net/powerbi/api/.default" };
            //string accessToken =  tokenAcquisition.GetAccessTokenForUserAsync(scopes).Result;

            var tokenCredentials = new TokenCredentials(accessToken, "Bearer");
            PowerBIClient pbiClient = new PowerBIClient(new Uri(urlPowerBiServiceApiRoot), tokenCredentials);

            using Stream stream = pbiClient.Dataflows.ExportDataflowAsAdmin(dataFlowId);
            using StreamReader reader = new StreamReader(stream);
            JObject data = JObject.Parse(reader.ReadToEnd());
            data["name"] = name + "DF";
            MemoryStream memoryStream = new MemoryStream();
            StreamWriter writer = new StreamWriter(memoryStream);
            JsonTextWriter jsonWriter = new JsonTextWriter(writer);
            data.WriteTo(jsonWriter);
            jsonWriter.Flush();
            memoryStream.Seek(0, SeekOrigin.Begin);
            pbiClient.Imports.PostImportWithFileInGroup(workflowId, memoryStream, "model.json");
        }

    public PowerBiTenantDetails GetTenantDetails(PowerBiTenant tenant) {

      PowerBIClient pbiClient = this.GetPowerBiClient();

      return new PowerBiTenantDetails {
        Name = tenant.Name,
        DatabaseName = tenant.DatabaseName,
        DatabaseServer = tenant.DatabaseServer,
        DatabaseUserName = tenant.DatabaseUserName,
        DatabaseUserPassword = tenant.DatabaseUserPassword,
        WorkspaceId = tenant.WorkspaceId,
        WorkspaceUrl = tenant.WorkspaceUrl,
        Members = pbiClient.Groups.GetGroupUsers(new Guid(tenant.WorkspaceId)).Value,
        Datasets = pbiClient.Datasets.GetDatasetsInGroup(new Guid(tenant.WorkspaceId)).Value,
        Reports = pbiClient.Reports.GetReportsInGroup(new Guid(tenant.WorkspaceId)).Value
      };

    }

    public PowerBiTenant CreateAppWorkspace(PowerBIClient pbiClient, PowerBiTenant tenant) {

      // create new app workspace
      GroupCreationRequest request = new GroupCreationRequest(tenant.Name);
      Group workspace = pbiClient.Groups.CreateGroup(request);

      // add user as new workspace admin to make demoing easier
      string adminUser = Configuration["DemoSettings:AdminUser"];
      if (!string.IsNullOrEmpty(adminUser)) {
        pbiClient.Groups.AddGroupUser(workspace.Id, new GroupUser {
          EmailAddress = adminUser,
          GroupUserAccessRight = "Admin"
        });
      }

      tenant.WorkspaceId = workspace.Id.ToString();

      return tenant;
    }

    public void DeleteWorkspace(PowerBiTenant tenant) {
      PowerBIClient pbiClient = this.GetPowerBiClient();
      Guid workspaceIdGuid = new Guid(tenant.WorkspaceId);
      pbiClient.Groups.DeleteGroup(workspaceIdGuid);
    }

    public void PublishPBIX(PowerBIClient pbiClient, Guid WorkspaceId, string PbixFilePath, string ImportName) {

      FileStream stream = new FileStream(PbixFilePath, FileMode.Open, FileAccess.Read);

      var import = pbiClient.Imports.PostImportWithFileInGroup(WorkspaceId, stream, ImportName);

      while (import.ImportState != "Succeeded") {
        import = pbiClient.Imports.GetImportInGroup(WorkspaceId, import.Id);
      }

    }

    public void PatchSqlDatasourceCredentials(PowerBIClient pbiClient, Guid WorkspaceId, string DatasetId, string SqlUserName, string SqlUserPassword) {

      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(SqlUserName, SqlUserPassword),
              PrivacyLevel.None,
              EncryptedConnection.NotEncrypted)
          };
          // Execute Patch command to update Azure SQL datasource credentials
          pbiClient.Gateways.UpdateDatasource((Guid)gatewayId, (Guid)datasourceId, req);
        }
      };

    }

    public async Task<EmbeddedReportViewModel> GetReportEmbeddingData(string AppIdentity, string Tenant) {

      PowerBIClient pbiClient = GetPowerBiClient();

      var tenant = this.AppOwnsDataDBService.GetTenant(Tenant);
      Guid workspaceId = new Guid(tenant.WorkspaceId);
      var reports = (await pbiClient.Reports.GetReportsInGroupAsync(workspaceId)).Value;

      var report = reports.Where(report => report.Name.Equals("Sales")).First();

      GenerateTokenRequest generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "View");

      // call to Power BI Service API and pass GenerateTokenRequest object to generate embed token
      string embedToken = pbiClient.Reports.GenerateTokenInGroup(workspaceId, report.Id,
                                                                 generateTokenRequestParameters).Token;

      return new EmbeddedReportViewModel {
        ReportId = report.Id.ToString(),
        Name = report.Name,
        EmbedUrl = report.EmbedUrl,
        Token = embedToken,
        TenantName = Tenant
      };

    }


  }

}