Establishing an OAuth connection for CData SalesForce Connector

[doaa_elhadba]

 With the Custom CData Connector, you can use a CData JDBC driver to ingest data from any of the over 200 databases, applications, and object stores supported by CData JDBC drivers. In this article, we are concerned with establishing a connection to SalesForce. However, these steps can also be used for other drivers with a few changes depending on each driver.

There are two sides to establishing an OAuth connection for CData SalesForce JDBC Connector. One is from the datasource side and the other is from Incorta's side.

First, we need to generate a ClientID and Client Secret from the datasource side. 

1. Go to SalesForce and login. On the top right, click Setup and then on the left Apps/App Manager. 
2. Add New Connected App
3. In the Basic Information part, add the Connected App Name, API, Email..etc
4. Under API(Enable OAuth Settings), check the Enable OAuth Settings checkbox. 
5. For the CallBackURL, this is a static value for Incorta. Kindly replace the domain.com in the below URL with your cluster domain name, for example, the domain name for cloud clusters should look like this. 
   

   https://test123.cloud2.incorta.com/incorta/service/datasource/oauthRedirect

   Please note that OAuth works in different ways, however, Incorta only supports authorization applications and does not support direct access tokens. Therefore, some of the options suggested in the CData documentation may not work with Incorta. It is recommended to use authorization apps where you provide a CallBackURL and generate a ClientID and Client Secret and then use them within Incorta. 
6. In the Selected OAuth Scopes, add the following scopes:
   • Full Access(full)
   • Perform requests at any time(refresh_token, offline_access). This is to avoid re-authenticating every time you pull data from SalesForce datasource. 
7. Click Save
   
8. Go to Manage Consumer Details, you will find the consumer ID(ClientID) and consumer secret (Client Secret) which will be used back in Incorta. 

Second, establishing the connection from Incorta

1. Go to Data. Add New Data Source. Choose CData
2. For the Driver Class, please add the following. You can also find it in the documentation of the CData SalesForce JDBC Driver. 
   

   cdata.jdbc.salesforce.SalesforceDriver​

3. The connection string should have the same CallBackURL we have set earlier as follows(replacing the domain with your cluster domain name):
   

   jdbc:salesforce:callbackurl=https://test123.cloud2.incorta.com/incorta/service/datasource/oauthRedirect;​

4. Enable OAuth toggle.
5. Add the ClientID and Client Secret we have acquired earlier from the datasource. 
6. There is an option to Enable HTTPS. This is depending on the redirect URL of the service provider, if your redirect URL is HTTPS then the toggle should be enabled. Otherwise, it should remain disabled.
   For cloud customers, this should always be enabled since the default for cloud is HTTPS.
7. For extra logging, we add the path desired for the logging at the end of the connection string as follows:
   

   jdbc:salesforce:callbackurl=https://test123.cloud2.incorta.com/incorta/service/datasource/oauthRedirect;​Logfile=/home/incorta/IncortaAnalytics/salesforce.log​;

8. You should also add the verbosity property at the end of the connection string taking into consideration the following values 
   • Verbosity=1 logs errors
   • Verbosity=2 logs requests and their responses
   • Verbosity=3 and higher logs data
   • For performance issues, it is recommended to set the verbosity to 2. 
     

     jdbc:salesforce:callbackurl=https://test123.cloud2.incorta.com/incorta/service/datasource/oauthRedirect;​Logfile=/home/incorta/IncortaAnalytics/salesforce.log​;verbosity=2;

     Please note that these logs need backend access and thus are not accessible by Cloud Customers and are only accessible to Cloud and Support teams for debugging purposes.
9. Click Authorize. This should take you to the login page of the datasource and after authorization, it redirects you back to Incorta.
   
10. Click Ok. 

You have successfully connected to SalesForce. You can now create a new schema and specify the datasource we just created and add the desired tables to your schema. 

Note: These steps work for most CData connectors that require OAuth and has already been tested on release 2022.10.0. 

[JoeM]

Great article @doaa_elhadba