Power BI Custom Data Connectors (Custom Authentication in ISV app)

By Tsuyoshi Matsuzaki on 2017-11-02 • ( 2 Comments )

In this post I describe how ISVs can implement their own Power BI Data Connector with custom authentication experience.
When users select “Get Data” in Power BI, your custom connector will be appeared. Your connector can provide custom authentication UI and custom data.
Even when some ISVs create (and submit) custom template content pack, this connector’s flow is needed when using custom OAuth authentication.

In this post I explain straightforward about this flow with developer’s view.

Note (Added on Nov 2018) : Power BI Custom Connectors are now Generally Available and now fully supported in Power BI Desktop, the On Premise Data Gateway, and Power BI Service.

Before staring…

Before starting, please enable the custom connector feature in your Power BI Desktop for development as follows.

Launch Power BI Desktop
Select “File” – “Options and settings” – “Options” menu
In the dialog window, select “Preview features” and enable “Custom data connectors”

Note : Currently, Data Connectors are only supported in Power BI Desktop. (You cannot also publish to Power BI service.)

Next you must install Power Query SDK in your Visual Studio. After installed, you can see the data connector project template in your Visual Studio.

Overall Flow

For developing custom authentication, you must understand how OAuth works with API applications.
You can use your own favorite OAuth provider (Google account, your own custom IdP, etc) for building OAuth supported Data Connector, but here we use Azure Active Directory (Azure AD) for implementation. (Here we use Azure AD v1 endpoint. See the note below.)

Step1 ) Beforehand you must register your api application (A) and your client application(=Power BI Data Connector) (B) in Azure AD. This operation is needed once for only ISV. (The user doesn’t need to register.)

Step2 ) When the user uses your Power BI Data Connector, your data connector (B) shows the following url in web browser or browser component for login.

https://login.microsoftonline.com/common/oauth2/authorize
  ?response_type=code
  &client_id={app id of B}
  &resource={app uri of A}
  &redirect_uri={redirected url for B}

Step3 ) With Power BI Data Connector, the previous “redirected url for B” is the fixed url, https://preview.powerbi.com/views/oauthredirect.html.
After the user has logged-in, code is returned by the query string as follows. Then your data connector (B) can retrieve the returned code value.

https://preview.powerbi.com/views/oauthredirect.html?code={returned code}

Step4 ) Next your data connector (B) posts the following HTTP request against Azure AD (without UI). Then the access token is returned as http response as follows.

POST https://login.microsoftonline.com/common/oauth2/token
Content-Type: application/x-www-form-urlencoded

grant_type=authorization_code
&code={returned code}
&client_id={app id of B}
&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "token_type": "Bearer",
  "scope": "user_impersonation",
  "expires_in": "3599",
  "ext_expires_in": "0",
  "not_before": "{start time of this token}",
  "expires_on": "{expired time of this token}",
  "resource": "{app uri of A}",
  "access_token": "{returned access token}",
  "refresh_token": "{returned refresh token}",
  "id_token": "{returned id token}"
}

Step5 ) Your data connector (B) calls your api application (A) with the previous access token as follows.

GET https://contoso.com/testapi
Authorization: Bearer {access token}

Step6 ) Your api application (A) can verify the passed token and return the result dataset if it’s valid. (See “Develop your API with Azure AD” for verification.)
The result dataset is shown in your Power BI client.

Note : Currently the native application in Azure AD v2 endpoint cannot have the redirect urls with “http” or “https” protocols. You cannot use https://preview.powerbi.com/views/oauthredirect.html as redirected urls.
Therefore we’re using Azure AD v1 endpoint in this post.

Here I explain how to setup or implement with corresponding above steps.

Register your app in Azure AD (Step 1)

First you must register your api application (A) and your client application(=Power BI Data Connector) (B) with Azure Portal.

When you register your api application (A), you must define the scope (permission or role) in manifest. (See below.)
You can use the appropriate scope values like “read” and “write” along with your API requirements. In this post we use the default settings as follows (scope value is “user_impersonation”), and your data connector (B) must use the scope “{app uri}/user_impersonation” (ex: https://test.onmicrosoft.com/235426d2-485d-46e0-ad0d-059501ab58a4/user_impersonation) for accessing your api application.

When you register your data connector (B), the app type must be “native application”, and you must include https://preview.powerbi.com/views/oauthredirect.html (common for all data connectors) as the redirect urls.

After you’ve done, you must open the required permission setting of data connector application (B) and add the scope (here “user_impersonation” scope) for accessing api application (A).

Note : If it’s production application, you must set these applications (A and B) as multi-tenanted applications and the user must consent your applications in the user’s tenant before using your data connector. In this post we skip these settings.

Implement your API – Create OData Feed service compliant with Power BI (Step 6)

Before implementing your data connector, now we shall implement the api application (B) beforehand.

You can create your api as simple rest api (consumed as Web.Contents() in your data connector) or odata-compliant rest api (consumed as OData.Feed() in your data connector) with your favorite programming languages.
Here we implement as OData v4 feed service with ASP.NET (.NET Framework) as follows. (See “OData.org : How to Use Web API OData to Build an OData V4 Service without Entity Framework” for details.)

First, create your “ASP.NET Web Application” project and please select [Empty] and [Web API] in the creation wizard as follows.

Add Microsoft.AspNet.OData package with NuGet. (Not Microsoft.AspNet.WebApi.OData, because it’s not v4.)

Note : You cannot use .NET Framework 4.6.2 with latest Microsoft.AspNet.OData 6.1.0 because of the dll confliction.

Add and implement your MVC controller. Your controller must inherit ODataController as follows.
Here I created the following simple controller which returns the data (3 records) of SalesRevenue class.

...
using System.Web.OData;
...

public class TestController : ODataController
{
  [EnableQuery]
  public IQueryable<SalesRevenue> Get()
  {
    return new List<SalesRevenue>()
    {
      new SalesRevenue { Id = 1, Country = "US", Value = 200 },
      new SalesRevenue { Id = 2, Country = "Japan", Value = 80 },
      new SalesRevenue { Id = 3, Country = "Germany", Value = 120 }
    }.AsQueryable();
  }
}

public class SalesRevenue
{
  public int Id { get; set; }
  public string Country { get; set; }
  public int Value { get; set; }
}
...

Edit your WebApiConfig.cs as follows.

...
using System.Web.OData.Builder;
using System.Web.OData.Extensions;
using Microsoft.OData.Edm;
...

public static class WebApiConfig
{
  public static void Register(HttpConfiguration config)
  {
    // Web API configuration and services

    // Web API routes
    config.MapHttpAttributeRoutes();

    config.Routes.MapHttpRoute(
      name: "DefaultApi",
      routeTemplate: "api/{controller}/{id}",
      defaults: new { id = RouteParameter.Optional }
    );

    // Add here
    ODataModelBuilder builder = new ODataConventionModelBuilder();
    builder.Namespace = "Demos";
    builder.ContainerName = "DefaultContainer";
    builder.EntitySet<Controllers.SalesRevenue>("Test");
    IEdmModel model = builder.GetEdmModel();
    config.MapODataServiceRoute("ODataRoute", "osalesdat", model);
  }
}
...

Now your api is consumed as https://{your api hosted url}/osalesdat with Power BI Desktop. (Please try with OData feed connector !)

Finally you must add the authentication in your api application.
As I mentioned earlier, your api will receive the following HTTP request from Power BI Data Connector. Your api application must verify the access token (the following Authorization header value), retreive several claims, and return data as you like.
For example, your api application can retrieve tenant-id from token, and can return each tenant’s data with your programming code. (i.e, you can easily implement multi-tenancy.)

GET https://contoso.com/api/test
Accept: */*
Accept-Encoding: gzip, deflate
Authorization: Bearer eyJ0eXAiOi...

For this implementation, please see my earlier post “Build your own Web API protected by Azure AD v2.0 endpoint with custom scopes“, but with ASP.NET you just select “Configure Azure AD Authentication” with the right-click on your project, or you can configure in the project creation wizard.

Note : If you select “new app” instead of “existing app” in the configuration dialog, your api application is automatically registered in Azure AD. In this case, you don’t need to register your api app manually in step 1.

When you want to protect api, please add Authorize attribute as follows.

public class TestController : ODataController
{
  [Authorize]
  [EnableQuery]
  public IQueryable<SalesRevenue> Get()
  {
    return new List<SalesRevenue>()
    {
      new SalesRevenue { Id = 1, Country = "US", Value = 200 },
      new SalesRevenue { Id = 2, Country = "Japan", Value = 80 },
      new SalesRevenue { Id = 3, Country = "Germany", Value = 120 }
    }.AsQueryable();
  }
}

Implement your Data Connector (All)

Now let’s create your data connector (B) in turn.
First you start to create your project with “Data Connector Project” template in Visual Studio.

Power BI Data Connector should be written by Power Query M formula language. (See M function reference for details.) All logic is written in “PQExtension1.pq” file. (We’re assuming our project named “PQExtension1”.)
Here I show you overall sample code (PQExtension1.pq) as follows. (Here I’m skipping the code for exception handling, etc.)
Now let’s see what this code is doing step by step.

section PQExtension1;

[DataSource.Kind="PQExtension1", Publish="PQExtension1.Publish"]
shared PQExtension1.Contents = (optional message as text) =>
  let
    source = OData.Feed(
      "https://contoso.com/osalesdat",
      null,
      [ ODataVersion = 4, MoreColumns = true ])
  in
    source;
 
PQExtension1 = [
  Authentication = [
    OAuth = [
      StartLogin = StartLogin,
      FinishLogin = FinishLogin
    ]
  ],
  Label = "Test API Connector"
];

StartLogin = (resourceUrl, state, display) =>
  let
    authorizeUrl = "https://login.microsoftonline.com/common/oauth2/authorize"
      & "?response_type=code"
      & "&client_id=97f213a1-6c29-4235-a37b-a82dda14365c"
      & "&resource=https%3A%2F%2Ftest.onmicrosoft.com%2F235426d2-485d-46e0-ad0d-059501ab58a4"
      & "&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html"
  in
    [
      LoginUri = authorizeUrl,
      CallbackUri = "https://preview.powerbi.com/views/oauthredirect.html",
      WindowHeight = 720,
      WindowWidth = 1024,
      Context = null
    ];

FinishLogin = (context, callbackUri, state) =>
  let
    query = Uri.Parts(callbackUri)[Query],
    tokenResponse = Web.Contents("https://login.microsoftonline.com/common/oauth2/token", [
      Content = Text.ToBinary("grant_type=authorization_code"
        & "&code=" & query
        & "&client_id=97f213a1-6c29-4235-a37b-a82dda14365c"
        & "&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html"),
      Headers = [
        #"Content-type" = "application/x-www-form-urlencoded",
        #"Accept" = "application/json"
      ]
    ]),
    result = Json.Document(tokenResponse)
  in
    result;

...

Note : Use Fiddler for debugging HTTP flow.

Implement your Data Connector - Signing-in (Step 2)

When you collaborate with OAuth authentication flow with your Power BI Data Connector, first you define your connector as follows. The following "Label" text will be displayed in the wizard window in Power BI. (See the screenshot in following "Run !" section.)

PQExtension1 = [
  Authentication = [
    OAuth = [
      ...
    ]
  ],
  Label = "Test API Connector"
];

When you want to navigate to the login UI with OAuth, you must add the following StartLogin.
Here 97f213a1-6c29-4235-a37b-a82dda14365c is application id (client id) for your data connector (B), and https%3A%2F%2Ftest.onmicrosoft.com%2F235426d2-485d-46e0-ad0d-059501ab58a4 is the url-encoded string for app uri of your api app (B). Please change for your appropriate values.

PQExtension1 = [
  Authentication = [
    OAuth = [
      StartLogin = StartLogin,
      ...
    ]
  ],
  Label = "Test API Connector"
];

StartLogin = (resourceUrl, state, display) =>
  let
    authorizeUrl = "https://login.microsoftonline.com/common/oauth2/authorize"
      & "?response_type=code"
      & "&client_id=97f213a1-6c29-4235-a37b-a82dda14365c"
      & "&resource=https%3A%2F%2Ftest.onmicrosoft.com%2F235426d2-485d-46e0-ad0d-059501ab58a4"
      & "&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html"
  in
    [
      LoginUri = authorizeUrl,
      CallbackUri = "https://preview.powerbi.com/views/oauthredirect.html",
      WindowHeight = 720,
      WindowWidth = 1024,
      Context = null
    ];

Implement your Data Connector - Get auth code (Step 3)

After the user has succeeded login with UI, code is returned to callback uri (https://preview.powerbi.com/views/oauthredirect.html) as part of query strings. When your data connector receives this code value, you must write as follows.
The following FinishLogin is invoked by connector framework after the login is succeeded.

PQExtension1 = [
  Authentication = [
    OAuth = [
      StartLogin = StartLogin,
      FinishLogin = FinishLogin
    ]
  ],
  Label = "Test API Connector"
];
...

FinishLogin = (context, callbackUri, state) =>
  let
    query = Uri.Parts(callbackUri)[Query],
    code = query
    ...
  in
    result;

Implement your Data Connector - Get access token (Step 4)

With code value, your data connector can retrieve access token as follows.
Here M function Web.Contents() posts http-request without UI (silently).

PQExtension1 = [
  Authentication = [
    OAuth = [
      StartLogin = StartLogin,
      FinishLogin = FinishLogin
    ]
  ],
  Label = "Test API Connector"
];
...

FinishLogin = (context, callbackUri, state) =>
  let
    query = Uri.Parts(callbackUri)[Query],
    tokenResponse = Web.Contents("https://login.microsoftonline.com/common/oauth2/token", [
      Content = Text.ToBinary("grant_type=authorization_code"
        & "&code=" & query
        & "&client_id=97f213a1-6c29-4235-a37b-a82dda14365c"
        & "&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html"),
      Headers = [
        #"Content-type" = "application/x-www-form-urlencoded",
        #"Accept" = "application/json"
      ]
    ]),
    result = Json.Document(tokenResponse)
  in
    result;

As you can see, FinishLogin returns OAuth JWT (json web token) to Power BI. (The access token and refresh token are included in this JWT.)

Implement your Data Connector - Call your api application (Step 5)

Finally you call your api application (previously generated OData feed service) as follows.
As you can see, here you don't need to specify Authorization header in your M code. The framework will automatically set access token in the request.

[DataSource.Kind="PQExtension1", Publish="PQExtension1.Publish"]
shared PQExtension1.Contents = (optional message as text) =>
  let
    source = OData.Feed(
      "https://contoso.com/osalesdat",
      null,
      [ ODataVersion = 4, MoreColumns = true ])
  in
    source;

Run !

Now let's start to build, deploy, and run !

Build your project in Visual Studio and copy the generated .mez file (which is the zip file format) into "%UserProfile%\Documents\Microsoft Power BI Desktop\Custom Connectors" folder.
Launch Power BI Desktop and select "Get Data". As you can see below, here's your data connector in the list.

When you select your custom data connector, the following dialog with "Sign In" button is displayed.

When you push "Sign In", the login-UI (in this case, Azure AD sign-in) is displayed.
Here we used Azure AD for our trivial example, but you can use your own provider (sign-in experience), as I mentioned earlier.

When you successfully logged-in and push "Connect" button, the following navigator is displayed along with your OData feed metadata.
In this case, we defined only "Test" entity in the previous code, but you can also include functions (not only entities), if needed.

When you select entities and proceed, you can use dataset in Power BI as follows.
As you see here, the end-user doesn't concern about your implementation (OAuth, OData, etc). The user just uses the given connector and can securely get all required data with custom authentication.

Note : Once you connect the data source, the connection is cached in Power BI Desktop. Please clear connection cache using "File" - "Options and settings" - "Data source settings" menu.

The following is the sample code which is handling exceptions and others (which is accessing Microsoft Graph), and please refer for your understanding.

[Github] MyGraph Connector Sample
https://github.com/Microsoft/DataConnectors/tree/master/samples/MyGraph

Reference : [Github] Getting Started with Data Connectors
https://github.com/Microsoft/DataConnectors

Categories: Uncategorized

Tagged as: PowerBI

2 replies »

Kyle Reed says:

2018-02-28 at 06:36

Will the V2 Azure AD endpoint work now?

LikeLike

Enhistorias says:

2018-12-06 at 22:51

Hi,

This example does not seem to work anymore, at least with Power BI Version: 2.64.5285.741 64-bit (November 2018).

Besides that. How would you add an input text to your custom connector so the URL (e.g. https://contoso.com/api/test) is not hardcoded?

Cheers

LikeLike

tsmatz

Professional Development, Data Science

Power BI Custom Data Connectors (Custom Authentication in ISV app)

Before staring…

Overall Flow

Register your app in Azure AD (Step 1)

Implement your API – Create OData Feed service compliant with Power BI (Step 6)

Implement your Data Connector (All)

Implement your Data Connector - Signing-in (Step 2)

Implement your Data Connector - Get auth code (Step 3)

Implement your Data Connector - Get access token (Step 4)

Implement your Data Connector - Call your api application (Step 5)

Run !

2 replies »

Leave a Reply Cancel reply

Recent Posts

Reinforcement Learning

Language (NLP)

Databricks Hands-On

Azure ML Hands-On

Others (GitHub)

Tags

Follow

Power BI Custom Data Connectors (Custom Authentication in ISV app)

Before staring…

Overall Flow

Register your app in Azure AD (Step 1)

Implement your API – Create OData Feed service compliant with Power BI (Step 6)

Implement your Data Connector (All)

Implement your Data Connector - Signing-in (Step 2)

Implement your Data Connector - Get auth code (Step 3)

Implement your Data Connector - Get access token (Step 4)

Implement your Data Connector - Call your api application (Step 5)

Run !

Share this:

Related

2 replies »

Leave a Reply Cancel reply

Recent Posts

Reinforcement Learning

Language (NLP)

Databricks Hands-On

Azure ML Hands-On

Others (GitHub)

Tags

Follow