DEV Community

Kenichiro Nakamura
Kenichiro Nakamura

Posted on

Develop Power BI Custom Connector : Microsoft Graph Connector with AAD

In the previous article, I explain how to migrate existing M code snippets into Power BI custom connector.

In this article, I explain how to build connector which require AAD authentication and some extra features.

I use the sample code found here. I did some modifications so that I can simplify the explanation.

Microsoft Graph

If you are using Microsoft 365 (ex. Office 365) such as Exchange Online, SharePoint Online and/or Microsoft teams, then you should be interested in Microsoft Graph. Why? Because you can access almost any data via the single endpoint.

You can access Graph Explorer to try it out.

AAD Authentication for Custom Connector

There are two steps to enable AAD authentication.

  • Register an application in Azure AD
  • Use the registered information in Custom Connector

This means you need, at least, application creation role. If you are not sure or you don't have it, go ahead to create Microsoft E3/E5 trial for testing this.

Register Application in AAD

1. Go to [https://portal.office.com], and click Azure Active Directory in Admin centers.

Alt Text

2. Navigate to Azure Active Directory | App registrations. Click New registration button.

Alt Text

3. Enter an application name and select single tenant for supported account type.

Alt Text

4. Select Public client/native type and enter https://oauth.powerbi.com/views/oauthredirect.html as redirect url. Then click Register.

Alt Text

5. Once the application is created, then set permission settings. Click API permissions and click Add a permission.

Alt Text

6. You specify which permission you want to give to the connector. Click Microsoft Graph.

Alt Text

7. Microsoft Graph API provides granular control of permissions. Click Delegated permissions.

Alt Text

8. Thought the sample uses more privilege, I keep it simple here. Select following permissions.

  • User.Read (Should be pre-selected)
  • Calendars.Read
  • People.Read

Alt Text

9. Finally, click Overview and copy **Application (client) ID.

Alt Text

Develop Custom Connector

Now, create the custom connector. Even though we have sample in GitHub, I will explain how to develop it from scratch.

Create project

See previous article and create Data Connector project. I name it MyGraphConnector here.

Update DataSource Kind

DataSource.Kind defines data source authentication information. By default, it set anonymous authentication (Implicit) only like below. It uses same name as the project name.

// Data Source Kind description
MyGraphConnector = [
    Authentication = [
        // Key = [],
        // UsernamePassword = [],
        // Windows = [],
        Implicit = []
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

Comment out the Implicit and add OAuth section. The OAuth supports several functions.

OAuth = [
    StartLogin=StartLogin,
    FinishLogin=FinishLogin
]
  • StartLogin: Invokes when user logs in to kick the login flow.
  • FinishLogin: Invokes after user logs in to extract the access_token.

It also supports other functions such as Refresh or Logout.

As we didn't define any function yet, it shows with red lines.

Alt Text

Add StartLogin

Add StartLogin which I copied from the sample code and tweak a bit.

client_id = "88c77539-b1f1-4719-9223-7f28fca09528";

StartLogin = (resourceUrl, state, display) =>
    let
        authorizeUrl = "https://login.microsoftonline.com/organizations/oauth2/v2.0/authorize?" & Uri.BuildQueryString([
            client_id = client_id,  
            redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html",
            state = state,
            scope = "offline_access User.Read Calendars.Read People.Read",
            response_type = "code",
            response_mode = "query",
            login = "login"
        ])
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = "https://oauth.powerbi.com/views/oauthredirect.html",
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];

If you are a developer and knows OAuth 2.0 flow, you already know what this is. If you don't know, then no worries. It simply hit endpoints and get token for you. Azure AD provides some common endpoints for all organizations, but you need to give client_id you just created as it's varies. Replace the id to your own application id.

Add FinishLogin

When StartLogin successfully completed, then FinishLogin is invoked OAuth flow actually has two steps. StartLogin just obtained "code" which you can get access token. Paste following code.

FinishLogin = (context, callbackUri, state) =>
    let
        parts = Uri.Parts(callbackUri)[Query],
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("authorization_code", "code", parts[code])
    in
        result;

TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            client_id = client_id,
            scope = "offline_access User.Read Calendars.Read People.Read",
            grant_type = grantType,
            redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html"
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        tokenResponse = Web.Contents("https://login.microsoftonline.com/organizations/oauth2/v2.0/token", [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

Even though this is a bit long, it simply calls another Web API call and obtain body which contains access_token.

Update shared function

This time, simply expose one function. Update shared function as below.

[DataSource.Kind="MyGraphConnector", Publish="MyGraphConnector.Publish"]
shared MyGraphConnector.Contents = () =>
    let
        source = OData.Feed("https://graph.microsoft.com/v1.0/me/", null, [ ODataVersion = 4, MoreColumns = true ])
    in
        source;

Test the connector

Hit F5 and try the code. I need to login first. Select OAuth2 as credential type and click Login.

Alt Text

Once you logged in, click Set Credential.

Alt Text

Run the test again by hitting F5. You see the result.

Alt Text

Deploy and execute the connect

We can now deploy the connector. Follow the steps in Deploy section of previous article and load into Power BI Desktop.

You can find and use the MyGraph connector as expected.

Alt Text

As I set Calendars.Read permission, I can see my own calendar items.

Alt Text

Summary

In this article, I explain how you can use OAuth authentication to access Microsoft Graph endpoint. There are still so many questions remain. I may write other articles if I find interesting topic.

If you want to learn more detail, take a look the sample code as it contains more than I explained.

Top comments (4)

Collapse
 
roshankrishnan7 profile image
roshan krishnan thirikkott

Is there a way to read a value from Azure App Configuration using such a custom connector?

Collapse
 
kenakamu profile image
Kenichiro Nakamura

If there are APIs available, but I am not sure. sorry.

Collapse
 
dominic_putka_fe6cbcd2eb8 profile image
DP • Edited

Hi, I am getting this error when trying to refresh following the set credentials set.

I believe I have followed all the steps correctly.
dev-to-uploads.s3.amazonaws.com/up...

Collapse
 
kenakamu profile image
Kenichiro Nakamura

I don't have much idea from the error message but it simply says schema issue. Do you have issue if you re-create the datasource or use brand new vanilla environment?