Web Server Flow¶

The web server flow authentication flow corresponds to the Authorization Code Grant defined by the OAuth 2.0 standard.

This authentication flow provides one important security benefit over the Username and password flow; Denodo is never exposed to the credentials of the user account of Salesforce. Instead, it just knows the credentials of the registered application.

Creating a Salesforce data source using the Web server flow¶

For this flow, enter the following details:

• Name. Name of the new data source.

• Authentication flow. Select Web Server.

• Client identifier and Client secret. These are the Consumer Key and Consumer Secret of the connected app in Salesforce.

• Sandbox. Select this if you are going to connect to a Sandbox environment. This changes the host name that Denodo will use to perform the OAuth authentication.

  • If selected, the host name is https://test.salesforce.com.

  • If cleared, the host name is https://login.salesforce.com.

  This is the host name that is used for authentication purposes. The requests to obtain actual data are sent to the host indicated in the field Base URL.

• Mutual Authentication. Select this to use “Mutual Authentication”. See more about this below, in the section Mutual Authentication).

• If you already obtained the Base URL, Access token and Refresh token, enter them in the boxes below and click Ok. You do not need to use the credentials wizard.

  If you do not have this information, click launch the OAuth 2.0 credentials… to open the wizard that will help you obtain them.

OAuth 2.0 credentials wizard¶

1. The default value of the fields Token endpoint URL and Authorization server URL is correct. Note that it changes depending on if you selected the check box Sandbox or not.

2. In Redirect URI, enter the Callback URL you entered when setting up the “Connected App” in Salesforce.

3. “Scopes” are the actions that Denodo will be able to execute on Salesforce. If you do not add any Scopes here, Denodo will be authorized to perform all the actions you selected in the Selected OAuth Scopes, in the configuration of the “Connected App” in Salesforce.

   If you want to define the scopes, click the button for each scope you want to add and enter its name.

4. Select the check box Set the “state” request.

5. Click Generate the authorization URL and then, Open URL. If the browser is not launched, copy the URL and open it manually.

   On this page, you have to enter the credentials of your Salesforce account to authorize this connected application. That is, enter the user name and password of the Salesforce account, not the registered application.

   If the credentials are correct, Salesforce will redirect your browser to the Redirect URL (this is the callback URL you set up when registering Denodo on your Salesforce account).

6. Copy the URL you have been redirected to and go back to the wizard. In the box of step 3, paste this URL.

   It does not matter if the browser shows an error saying that the page does not exist; copy the URL and paste it in the wizard. You can ignore this error because this page only displays an informational message saying that you have to paste this URL into the wizard.

7. Click on Obtain the OAuth 2.0 credentials.

   The Server will request the OAuth credentials using all the details you have provided and the parameters of the URL you have pasted in the previous step. If authenticated successfully, the Access token, Refresh token, Base URL and API version fields will be filled automatically.

   The response will be the access token, the Salesforce host name to which this data source will have to send requests to and, depending on the configuration of the connected app, the refresh token.

8. Click Ok to close the wizard.

   The wizard will fill the text areas and text fields with the information obtained in the previous step.

• Click Ok to close the “Edit HTTP Connection” dialog and then, Save to create the data source.

• Access token. OAuth 2.0 access token.

• Refresh token. OAuth 2.0 refresh token.

• Base URL. URL of the Salesforce instance to access.

• API version. Version of the Salesforce REST API version to use. This has to be something like “v41.0”. Click Get latest version to send a request to Salesforce to obtain the latest version of the API.

• Delegate GROUP BY. Select to push down the GROUP BY clause to Salesforce. Take into account that Salesforce limits to 2,000 the number of rows a query with GROUP BY can return.

• Include deleted and archived data. Select to include deleted and archived data in the result of queries. By default, Salesforce filters out deleted and archived data.

• In Configure proxy you can set the proxy configuration for this data source. If in this dialog, you select Default, the data source will use the default proxy of the Virtual DataPort server.

Username and Password Flow¶

The username and password flow corresponds to the Resource Owner Password Credentials Grant defined by the OAuth 2.0 standard.

Creating a Salesforce data source using the Username and password flow¶

The following fields are requested when Username and password flow is selected:

• Name. Name of the new data source.

• Authentication flow. Select Username and password.

• Client identifier and Client secret. These are the Consumer Key and Consumer Secret of the connected app in Salesforce.

• User identifier and User password. These are the credentials of the Salesforce user account.

• Security token. This is the security token of this Salesforce account.

• Sandbox. Select this if you are going to connect to a Sandbox environment. This affects the host name that Denodo will use to perform the OAuth authentication.

  • If selected, the host name is https://test.salesforce.com.

  • If cleared, the host name is https://login.salesforce.com.

  This is the host name that is used for authentication purposes. The requests to obtain actual data are sent to a different host name.

• API version. Version of the Salesforce REST API version to use. This has to be something like “v41.0”. Click Get latest version to send a request to Salesforce to obtain the latest version of the API.

• Mutual Authentication. Select this to use “Mutual Authentication”. See more about this below, in the section Mutual Authentication).

• Delegate GROUP BY. Select to push down the GROUP BY clause to Salesforce. Take into account that Salesforce limits to 2,000 the number of rows a query with GROUP BY can return.

• Include deleted and archived data. Select to include deleted and archived data in query results. By default, Salesforce filters out deleted and archived data.

• In Configure proxy you can set the proxy configuration for this data source. If in this dialog, you select Default, the data source will use the default proxy of the Virtual DataPort server.

Click Save () to create the data source.

In the Metadata tab, set the folder where the data source will be stored and provide a description. When editing the data source, you can change its owner by clicking the button .

With the flow Username and Password, you do not have to enter the “Base URL” as you have to do with the Web server flow. The reason is that with the former, when you create the data source, the Server follows this process:

1. It sends a request to Salesforce to obtain an access token.

2. The response contains the access token and the base URL.

3. The Server saves the information you entered and this URL. If you see the VQL of the data source, you will see these values.

Mutual Authentication¶

You can add an additional level of security to the connections with Salesforce by enabling Mutual authentication. With this type of authentication (also known as “two-way SSL/TLS”), the client application that connects to Salesforce (in this case Denodo) has to present a certificate to validate its identity.

Before enabling this authentication method, you have to set up a mutual authentication certificate in your Salesforce account.

Mutual authentication configuration for a Salesforce data source¶

Once mutual authentication is enabled in Salesforce, you need to provide the following:

• Base URL: enter the URL to your Salesforce instance with the port 8443. E.g. “https://c15.salesforce.com:8443”. Client applications (i.e. Denodo) that use this type of authentication have to present their certificate on this port.

• Certificate password: enter the password of the file containing the private key that Denodo will use.

• Click Load certificate and select the file that contains the private key. The supported formats are PKCS#12 and JKS.

Using the Variable WHEREEXPRESSION in SOQL Queries¶

As explained above, the SOQL Query used to create a base view can contain interpolation variables to parameterize the SOQL Query.

Virtual DataPort provides a predefined interpolation variable called WHEREEXPRESSION that simplifies the process of creating a base view from a SOQL query. At runtime, the Server will replace WHEREEXPRESSION with the condition sent to the base view.

After clicking Ok, you have to provide the values for the variables used, so Virtual DataPort can execute a query to obtain the metadata required for creating the base view.

Then, click Save () to create the base view.

If you need to filter the results of a SOQL query base view with a WHERE condition that involves fields that cannot be projected, type the name of these fields separated with commas, in the box at the bottom of the dialog. These fields will belong to the base view but the Server will not project their value. They should only be used in the WHERE clause of the queries that involve this base view and the values provided for them will be used only to generate the query sent to the database.

To avoid forcing the queries to always have the WHERE clause, Virtual DataPort provides the interpolation function ExecuteIfIsNotNull. The syntax of the function is:

^ExecuteIfIsNotNull( <prefix if the variable is not NULL>
    ,@<variable name>
    ,<suffix if the variable is not NULL>
    [,<value if the variable is NULL> ] )

At runtime, if the value of the variable is not defined, the function replaces @<variable name> with the value of the variable and adds a prefix and a suffix to this value.

• Do not leave any white space between the parameters of the function.

• Use double quotes, not single quotes, to surround the parameters.

• In the second parameter, the name of the interpolation variable has to be indicated with the syntax @VARIABLE_NAME, without braces. In other scenarios, you can indicate variables with the syntax @{VARIABLE_NAME}, but not in this function.

SELECT id, name FROM account ^ExecuteIfIsNotNull("WHERE ",@WHEREEXPRESSION,"")

At runtime, if the query to this view has WHERE clause, @WHEREEXPRESSION will be substituted by the query condition, prefixed by WHERE and with an empty string at the end.

If you set the fourth parameter (optional) of ExecuteIfIsNotNull, the function will always be executed. If the value of the interpolation variable is NULL, the function returns the value of the fourth parameter. For example, if the base view is built with this SQL query:

SELECT id, name
FROM account ^ExecuteIfIsNotNull('WHERE',@WHEREEXPRESSION,'','WHERE name <> NULL')

At runtime, if the query does not have the WHERE clause, the Server will execute this query in the database:

SELECT id, name FROM account
WHERE name <> NULL

Insert or Update Binary Fields¶

To insert or update a binary field on a Salesforce record, you have to provide its value encoded in Base64. Example:

UPDATE contentversion
SET versiondata = 'VGhpcyB0ZXh0IGlzIGVuY29kZWQgaW4gQmFzZTY0dkldkldklklaUUIII....'
WHERE id = '0680Y000002H9KfQAK'

Bulk Mode Parameters¶

The management of the Salesforce Bulk batches implies that VDP server will regularly check for the status of them through the Salesforce Bulk API until a they have finished or a maximum time is reached. You can parameterize this by executing the following commands with the values that are more appropriate for your case:

-- You do not need to restart to apply these changes:
SET 'com.denodo.vdb.misc.datasource.SalesforceDataSource.bulk.batchStatus.maxWaitMillis'='1800000';
SET 'com.denodo.vdb.misc.datasource.SalesforceDataSource.bulk.batchStatus.timeBetweenRequestsMillis'='2000';

where each parameter means:

• maxWaitMillis: maximum time in milliseconds to wait for a batch to be in a final state (finished or erroneous).

• timeBetweenRequestsMillis: number of milliseconds between requests to check the state of the batch.

Take into consideration that this will have an impact in the number of requests made to Salesforce or the time required to complete the query.