OData-enable your data sources to quickly support Salesforce Connect integration

Integrating external data with Salesforce applications has never been easier thanks to the 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 tutorial, we show SQL Server as the data source, but any of the data sources available in Hybrid Data Pipeline can be OData-enabled for use with Salesforce Connect. This includes sources such as Google BigQuery, PostgreSQL, Oracle or even REST sources such as Jira, BigCommerce and internal REST APIs.

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. You can also use basic authentication to connect to Hybrid Data Pipeline for a simpler deployment.

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-premises infrastructure as the name suggests. Follow the tutorial below 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 https://<server_ip_address> and you should see a login page as shown below.
2. Login to Hybrid Data Pipeline and you should be seeing the dashboard. Click on the Data Sources tab on the side bar and you should see the data sources which have been configured. To add a new data source, click on New Data Source.
3. Create your data source in Hybrid Data Pipeline by clicking on the data source type and providing the connection details. An example for SQL Server is shown below:
   
   

   Note: If you are connecting to an On-Premises data source, select the appropriate Connector ID in the drop down. For directly accessible sources, this can be left blank.

4. Now go to OData tab and Click on Edit/View button. You should now see 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 and columns that you want to access with OData.
   
   
   
5. Click on Save Map and then click Update on the following screen. Now, if you go to OData tab you should find the endpoint URL as shown below with the red arrow:
   

There are two methods to authenticate to the Hybrid Data Pipeline from Salesforce. The first is using your Hybrid Data Pipeline username and password by specifying a named principal account in Salesforce. The second is using OAuth 2.0. Both methods are listed below:

No further configuration is required in Hybrid Data Pipeline. Proceed to the Salesforce External Data configuration, select Named Principal as the Identity Type, Password Authentication as the Authentication Protocol, as well as Username and Password for your Hybrid Data Pipeline user.

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 a dummy entry. It does not need 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. Example:

1. Go to Quick Find and Search for Auth. Providers. 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.
   
   
   
   
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)
      
      
      
      
   6. Click on Save. Now you should see Salesforce Configuration which will show you the actual callback URL as shown below.
      
      
      
      
   7. Open Postman and update the application that you have created in previous section for OAuth.
      
      
      PUT http://<Server>/api/mgmt/oauth/client/applications/<id>

      Body

      {

      "name":"SalesforceAuth",

      "description":"Salesforce Lightning Connect via OData",

      "redirectUrls":["https://login.salesforce.com/services/authcallback/00D37000000IueCEAS/HDPOAuth"]

      }

      Authentication Basic
      
      

      Note: Copy the Callback URL provided by Salesforce to the redirect Urls array in the above body. The is the same 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.

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.
   
   
4. Login with your credentials, and you should now see a prompt as shown below. Click on Allow.
   
   
   
   
5. You should now be redirected to Salesforce, and the External Data source should be created with Authentication Status as Authenticated
   
   
   
   
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-premises data as shown below.
   
   
   
   
7. Choose the tables that you want to sync and click on Sync to create External Objects for your On-premises 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. Remember, you can use Hybrid Data Pipeline in a similar fashion to connect to Oracle, Postgres, MySQL, DB2 etc. If you have any questions or issues, feel free to contact us.