Denodo - Creating Data Sources from Box/SharePoint
Box Custom Apps and SharePoint Online integrations use OAuth 2.0 authentication. OAuth 2.0 is a token-based authentication system.
Files
Best Practice #1: Use CSV files rather than Excel files. CSV files tend to be more stable in Denodo in general and we have also noticed that Excel files accessed over HTTP paths have limitations. Excel limitations can be avoided by converting them to CSV before integrating them with Denodo.
Best Practice #2: Use the same basic format for all your CSV files. Not only is consistency a good idea, but it will make the SharePoint integration more stable as well. In this document we will standardize using the comma ( , ) character for the column delimiter, \n for the end of line delimiter, and the first row will have column headers.
OAuth 2.0 Tokens
This document will not cover all the details of the OAuth 2.0 specification, but it is helpful to know a little bit about what is happening so the next steps make a little more sense.
Once an integration app is set up in Box/SharePoint it will have a Client ID and Client Secret associated with it. You can think of this Client ID and Client Secret like an application service account with a password. However, the Client ID and Client Secret are not sufficient to fully authenticate into Box/SharePoint and access data. Instead, the client application, Denodo, in this case, will request an access token from the Box/SharePoint OAuth server. When Denodo asks for an access token the OAuth server returns both and access token and a refresh token. This access token is used to make requests for data within Box/SharePoint. However, the access token is only good for a limited amount of time. After that time, the client application (Denodo) will need to use the refresh token to request a new access token from the OAuth server.
Denodo will perform the actions of using refresh tokens to get new access tokens, but there are things that cause this process to become unstable. The suggestions in this document are made to help avoid those issues.
Creating Box Integrations
Step 1 of 2
- Log into the Box.com Developer Console: https://uofi.app.box.com/developers/console
- Click Create New App and choose Custom App.
- Enter a name for the app according to your naming standards in the App Name field.
- Enter a concise Description.
- For Purpose, select Integration.
- For Categories, select Collaboration.
- For Which external system are you integrating with?, enter “Denodo” into the field.
- For Who is building this application?, select Customer.
- Select Next.
Step 2 of 2
- For Authentication Method, select User Authentication (OAuth 2.0).
- Select Create App.
Configuration Settings for Your New App
- Select the Configuration tab.
- Scroll down to the OAuth 2.0 Credentials section.
- Copy and paste the Client ID and Client Secret to a convenient, easily accessible location (such as Notepad).
- NOTE: The developer token is not necessary for our use. However, you will need the Client ID and Client Secret for the data source configuration in Denodo.
- Add the OAuth 2.0 Redirect URI by selecting Add a URI. Add all three redirect URIs so you will be able to access the files in any Denodo environment you have access to.
- Production: https://datahub.uillinois.edu/oauth/2.0/redirectURL.jsp
- QA: https://qa.datahub.uillinois.edu:9443/oauth/2.0/redirectURL.jsp
- Dev: https://dev.datahub.uillinois.edu:9443/oauth/2.0/redirectURL.jsp
- For Application Scopes, select both Content Actions (Read all files and folders stored in Box and Write all files and folders stored in Box).
- Select Save Changes.
- NOTE: If you navigate to My Apps, the app you just created will appear in the list.
Base URL to Use in Denodo to Access Files Using This Custom Box App
Generate the URL by combining the following components:
- https://api.box.com/2.0/files/
- NOTE: This is necessary for the api to locate and read the file.
- The file ID from the URL of the data source file in Box.
- Example: https://uofi.app.box.com/file/1445697894187
- End with /content to allow Denodo to read the contents of the file.
Example Base URL: https://api.box.com/2.0/files/1445697894187/content
Creating SharePoint Integrations
App Registrations
- Navigate to and log in to the Azure Portal: https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps
- Click New Registration.
- Enter the Name.
- For Supported account types, set to Accounts in this organizational directory only.
- For Redirect URI, select Web and enter one of the following URIs:
- Production: https://datahub.uillinois.edu/oauth/2.0/redirectURL.jsp
- QA: https://qa.datahub.uillinois.edu:9443/oauth/2.0/redirectURL.jsp
- Dev: https://dev.datahub.uillinois.edu:9443/oauth/2.0/redirectURL.jsp
- NOTE: Due to a SharePoint limitation, the value you set for Redirect URI will be the only one you can use as a redirect URI for this app. Azure supports adding more redirect URIs to an app, but SharePoint only supports one. Be sure you are adding the URI of the Denodo environment you wish to use.
- Click Register.
Once the app is registered, we will need to grant permissions for clients to access SharePoint Online using the app.
Integration Assistant
- Click the Integration assistant from the left-hand menu.
- For What application types are you building?, select Daemon (background process or automation).
- Click Evaluate my app registration.
API Permissions
- Under Manage in the left-hand menu, click API permissions.
- Click Add a permission.
- Select SharePoint from the list of Commonly used Microsoft APIs.
- Select Delegated permissions.
- Under Permission, expand the AllSites menu.
- Select AllSites.Read.
- Click Add permissions.
Certificates and Secrets
- Under Manage in the left-hand menu, click Certificates and secrets.
- Click New client secret.
- Enter a Description.
- For Expires, select an expiration timeframe.
- Click Add.
- NOTE: You will see that the new client secret has been added to the Client secrets list.
- Copy and paste the Value of the client secret to a convenient, easily accessible location (such as Notepad).
- NOTE: This is the only time you can view the value! You will need this value when you create Denodo data source connections using this SharePoint app.
- In the left-hand menu, click Overview.
- Copy and paste the Application (client) ID to a convenient, easily accessible location (such as Notepad).
- NOTE: You will need this value when you create Denodo data source connections using this SharePoint app.
- Copy and paste the Directory (tenant) ID to a convenient, easily accessible location (such as Notepad).
- NOTE: You will need this value when you create Denodo data source connections using this SharePoint app.
Base URL to Use in Denodo to Access Files Using This SharePoint App
https://uillinoisedu.sharepoint.com/sites/[SiteName]/_api/Web/GetFileByServerRelativePath(decodedurl=''@sp_path'')/$value
For [SiteName] in the Base URL, use the name of a SharePoint site (without spaces) that you have access to.
- For example, TestSiteforDenodoImplementation.
This uses an interpolation variable for the “path.” This is what will make the connection reusable.
Best Practice: Only use the SharePoint App credentials in a single Denodo connection. Creating more than one Denodo connection that re-uses the same SharePoint Client ID will cause the access tokens associated with that Client ID to interrupt one another and your integration will become unstable.