Connect to OData from Power BI using OAuth2 Authentication

In this article, we will walk you through how you can connect to your data in Relational, Big Data or SaaS data sources via OData generated using Progress Hybrid Data Pipeline from Power BI. As Hybrid Data Pipeline supports OAuth2 for OData, we will be showing you how you can use OAuth 2 with OData in Power BI.

Pre-requisites

1. Power BI
2. Visual Studio 2017
3. Power Query SDK installed in your Visual Studio

Follow the below tutorials on how to install Progress Hybrid Data Pipeline in Azure or AWS or any Linux server

For AWS

https://docs.progress.com/bundle/datadirect-hybrid-data-pipeline-aws/page/Hybrid-Data-Pipeline-on-AWS.html

For Azure

https://www.progress.com/tutorials/cloud-and-hybrid/deploying-hybrid-data-pipeline-on-microsoft-azure

Enabling On-Premise Connectivity (if applicable)

If your database is behind a corporate firewall or in a private network, you can use our On-Premises connector which will enable a secure connection to your database for Hybrid Data Pipeline Server, without having to open any ports in your firewall. To get started, you would need to install this On-Premises Connector on your on-premise infrastructure as the name suggests. Follow the below tutorial on how to install the On-Premises Connector.

If Hybrid Data Pipeline Server is on AWS

https://docs.progress.com/bundle/datadirect-hybrid-data-pipeline-aws/page/Hybrid-Data-Pipeline-on-AWS.html

If Hybrid Data Pipeline Server is on Azure

https://www.progress.com/tutorials/odbc/configuring-on-premise-connector-for-hybrid-data-pipeline-on-azure

1. Open your browser and go to go to http://<server_ip_address> and you should see a login page as shown below. You should be able to access the UI without any port in the URL after you have redirected traffic on 80 to 8080 or 443 to 8443 for https.

    

    

    

2. Login with the credentials that you have supplied during installation and you should be seeing the dashboard. Click on Data Sources tab on the side bar and you should see all the data stores that are supported.

    

    

    

3. Click on SQL Server or your own database and you should now see a connection configuration page as shown below. Fill it up with your connection information for your database and click on Test Connect button to verify the connection.

    

    

   Note: If you are connecting to a database On-Premise, you should find the On-Premise Connector ID, which you have configured above, in Connector ID drop down.

    

    

4. Now go to OData tab and Click on Configure Schema button. You should now see all the schemas in your database, select your schema and now you should see all the tables in the schema as shown below. Select the tables that you want to access through.

    

    

    

    

5. Click on Save & Close. Now go to OData tab and you should find the endpoint URL as shown below. If you don’t have the SSL enabled, fallback to http. The URL might be of following format: http://<server>/api/odata4/sqlserver

1. You need to Register Salesforce Connect as an application in Hybrid Data Pipeline for OAuth 2.0 Authorization.
2. To do that, Open Postman and send a POST REQUEST as shown below

    

   POST https://<server>/api/mgmt/oauth/client/applications

    

   Body

    

   {

    

   "name":"PowerBIAuth",

    

   "description":"Connect to Power BI via OAuth2",

    

   "redirectUrls":["https://preview.powerbi.com/views/oauthredirect.html"]

    

   }

    

   Authentication Basic
   
   

    

3. The Response from the Server will contain your Client Key and Client Secret and should be like something below.

    

   {

    

       "id": 1,

    

       "name": "PowerBIAuth",

    

       "description": "Connect to Power BI via OAuth2",

    

       "redirectUrls": ["https://preview.powerbi.com/views/oauthredirect.html"],

    

       "clientId": "xxxxx",

    

       "clientSecret": "xxxxxx"

    

   }
   
   

    

4. Keep a note of these Client ID and Client Secret, as you will use it later.

1. Enable the Custom data connectors preview feature in Power BI Desktop (under File | Options and settings | Custom data connectors)

1. Open Visual Studio and Create New -> Project. In the left pane, choose Power Query under templates. Choose Data Connector Project and create New Project with name ‘HdpOAuthConnect’

    

   

2. In the Solution, find HDPOAuthConnect.pq file and copy the code from this Github Project
3. In the above code, replace the values for
   1. Your Hybrid Data Pipeline OData Service URL
   2. Your Hybrid Data Pipeline Client ID
   3. Your Hybrid Data Pipeline Client Secret
   4. Your Hybrid Data Pipeline Authorize URL
   5. Your Hybrid Data Pipeline Token URL
4. Save the file and Build the Solution. Now go to bin folder and you should find HDPOAuthConnect.mez file. This is the Power BI extension.
5. On your PC, Go to Documents and create a folder named Microsoft Power BI Desktop.
6. Under the folder Microsoft Power BI Desktop, create another folder Custom Connectors
7. Copy the HDPOAuthConnect.mez to the folder Custom Connectors

1. If your Power BI is already running, save your work and Restart Power BI for the extension to show up.

   

    

2. In Power BI, click on Get Data -> All -> HDPAOuthConnect. Choose it and you should now see a form as shown below where it will ask you to sign in.
   
   
   

3. Click on Sign in and you should now see a login page for DataDirect Hybrid Data Pipeline.

    

   

    

    

4. Login with your credentials, and you should now see the below authorization screen. Click on Allow.

    

   
   

5. You should now be back on Power BI Sign in form, you should now be signed in. Click on Connect to see your data.

We hope this tutorial helped you to access your data securely using OData generated by Progress Hybrid Data Pipeline from Power BI. You can use Hybrid Data Pipeline in a similar fashion to connect to Oracle, Postgres, MySQL, DB2 etc., and even if they are On-premise you would be able to access the data from Power BI. If you have any questions or issues, feel free to contact us.