Access On-Premises Data from Salesforce securely via OAuth2 using OData

Introduction

Integrating external data with Salesforce applications has never been easier thanks to Salesforce Connect that can help you consume data from any OData producer. External data objects can be configured in minutes. But the picture isn't complete without the real-time connectivity provided by our Hybrid Connectivity Service - DataDirect Hybrid Data Pipeline.

In this article we will walk you through on how you can access On-premises data securely via OAuth2 using OData generated by Hybrid Data Pipeline.

Download and Install Hybrid Data Pipeline Server

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

For AWS

https://www.progress.com/tutorials/cloud-and-hybrid/deploying-progress-datadirect-hybrid-data-pipeline-on-amazon-aws

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://www.progress.com/tutorials/cloud-and-hybrid/deploying-progress-datadirect-hybrid-data-pipeline-on-amazon-aws

 

If Hybrid Data Pipeline Server is on Azure

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

Creating OData 4 endpoint

  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.hdp-login
  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.hdp-datastores
  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 connectionConfigure SQL Server3

    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.

    select tables

  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

Register New Application for Salesforce Connect

  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":"SalesforceAuth",
     
    "description":"Salesforce Lightning Connect via OData",
     
    "redirectUrls":["dummy.callback.url"]
     
    }
     
    Authentication Basic

     

    Note: The Redirect URL’s can be dummy, they need not to be valid at this stage. This will be updated later with valid Callback URL that will be provided by Salesforce.

     

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

 

{
 
    "id": 1,
 
    "name": "SalesforceAuth",
 
    "description": "Salesforce Lightning Connect via OData",
 
    "redirectUrls": [
 
        "dummy.callback.url"
 
    ],
 
    "clientId": "xxxxx",
 
    "clientSecret": "xxxxxx"
 
}

Create OAuth Authentication Provider in Salesforce

  1. Go to Quick Find and Search for Auth. Providers, and click on the Auth. Providers link.
  2. Click on New to create a new Auth Provider.
  3. Choose Provider Type to Open ID Connect and you should now see a form as below.

    new external-data sources

  4. Fill the
    1. Name,
    2. Consumer Key (Client ID that you got from previous section)
    3. Consumer Secret (Client secret that you got from previous section)
    4. Authorize Endpoint URL: http://<hdp-server> /oauth2/authorize (https if you have valid SSL certificate)
    5. Token Endpoint URL: http://<hdp-server> /oauth2/token (https if you have valid SSL certificate)

      Auth Provider config

    6. Click on Save. Now you should see Salesforce Configuration which will show you the actual callback URL as shown below.

      call back url

    7. Open Postman and this time update the application that you have created in previous chapter for OAuth.

      PUT http://<Server>/api/mgmt/oauth/client/applications/<id>
      Body
      {
      "name":"SalesforceAuth",
      "description":"Salesforce Lightning Connect via OData",
      }
      Authentication Basic

      Note: Copy the Callback URL provided by Salesforce to the redirectUrls array in the above body. The <id> is the same <id> that was generated when you first registered the application.

      If you don’t remember the <id> for the application, send a GET request to http://<server>/api/mgmt/oauth/client/applications to list all applications that are registered for OAuth2.0 with Hybrid Data Pipeline.

Create External Data Source and Objects in Salesforce

  1. Go to Develop -> External Data Sources and create a new External Data source.
  2. Fill in
    1. External data source name
    2. Name
    3. Type: Salesforce Connect: OData 4.0
    4. URL: https://<hdp-server>/api/odata4/sqlserver/ (From Creating OData 4.0 endpoint section)
    5. Identity Type: Named Principal
    6. Authentication Protocol: OAuth 2.0
    7. Authentication Provider: Choose the authentication provider you created in previous chapter
    8. Scope: api.access.odata
    9. Start Authentication Flow on Save: Check
  3. Click on Save. You should now be redirected to Hybrid Data Pipeline login page as shown below.

    hdp-login

  4. Login with your credentials, and you should now see a prompt as shown below. Click on Allow.

    Authorize Salesforce connect9

  5. You should now be redirected to Salesforce, and the External Data source should be created with Authentication Status as Authenticated

    validate and sync

  6. Click on Validate and Sync, this will show you all the tables that are being accessible from OData 4.0 endpoint of your On-premise data as shown below.

    Sync Tables

  7. Choose the tables that you want to sync and click on Sync to create External Objects for your On-premise Data in Salesforce.

We hope this tutorial helped you to access your On-premises data securely using OData generated by Progress Hybrid Data Pipeline from Salesforce. 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 Salesforce. If you have any questions or issues, feel free to contact us.


OData, Cloud and Hybrid TUTORIAL

Access On-Premises Data from Salesforce securely via OAuth2 using OData

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers