Denodo - Creating a One Drive File Data Source

This document describes how to create a Denodo data source against a One Drive file using either an Authenticated or Unauthenticated method. 

Authenticated Method

If the file is not shared, shared privately with specific users, or shared via a link with users in the U-M organization, authenticated calls via the Microsoft Graph API are required. Azure API utilities is used to generate an OAuth token that can be stored in Denodo.

Configure Azure App

  1. Register an App using the Microsoft Azure Portal using the following configuration:
    1. Supported account types: Accounts in this organizational directory only
    2. Redirect URI: Type: Web; Value: https://localhost
    3. Click Register which registers the app and redirects to the app overview page
    4. Copy the Application (client) ID
    5. Click Endpoints and copy
      1. OAuth 2.0 authorization endpoint (v2) URL
      2. OAuth 2.0 token endpoint (v2) URL
  2. Click Certificates & secrets in the left navigation menu and do the following:
    1. Click + New client secret in the Client secrets section, and set Expires to Never.
    2. Copy the client secret Value (not the ID).
  3. Click API permissions in the left navigation menu and do the following:
    1. Select + Add a permission.
    2. Select Microsoft Graph.
    3. Select Delegated permissions.
    4. Add offline_access in the OpenId Permissions category.
    5. Add Files.Read in the Files category.
  4. Click Add permissions.

Create OAuth Access and Refresh Tokens

Complete the following steps in the Denodo VDP client.

  1. Open the VDP client.
  2. Connect to the Denodo environment. 
  3. Navigate to Tools > OAuth credentials wizards > OAuth 2.0 wizard and set the following:
    1. Authentication grant: Authorization code grant
    2. Client identifier: App Key from 1d in Configure Azure App section.
    3. Client secret: App Secret from 2b in Configure Azure App section.
    4. Authentication method: Include the client credentials in the body of the request
    5. Token endpoint URL: URL from 1.e.i in Configure Azure App section.
    6. Authorization server URL: URL from 1.e.ii in Configure Azure App section.
    7. Redirect URI: https://localhost
    8. Scopes:
      1. https://graph.microsoft.com/Files.Read.All
      2. offline_access
    9. Click Generate the authorization URL.
    10. Click Open URL.
  4. Give consent when prompted.
  5. The browser returns a message that ‘localhost’ refused to connect, but the browser address bar contains the required authorization response URL. Copy the URL from the browser address bar and paste it into the Paste the authorization response URL field in the Denodo OAuth credentials wizard.
  6. Click Obtain the OAuth 2.0 credentials
  7. Copy the credentials to the clipboard and store in a secure location. If the credentials do not contain a refresh token, you need to regenerate the credentials, being sure to add the offline_access scope as shown in step 3 above.

Create a data source

Create a Denodo data source appropriate for the file to which you are connecting. The example below walks through connecting to a CSV file in the One Drive user’s root directory by specifying a file path. The example assumes the file test_file.csv exists in a folder named test_folder in the root of the user’s One Drive.

  1. Open the Denodo VDP client.
  2. Connect to the Denodo environment.
  3. Navigate to New > Data source > Delimited file
  4. Set the Data Route to HTTP Client and configure the following: 
    1. Configuration tab
      1. HTTP method: GET
      2. URL: https://graph.microsoft.com/v1.0/me/drive/root:/test_folder/test_file.csv:/content
    2. Authentication tab
      1. Authentication: OAuth 2.0
      2. Client identifier: Paste from credentials obtained above
      3. Client secret: Paste from credentials obtained above
      4. Access token: Paste from credentials obtained above
      5. Refresh token: Paste from credentials obtained above
        • If the credentials do not contain a refresh token, you need to regenerate the credentials, being sure to add the offline_access scope as shown in step 3 of the Create OAuth Access and Refresh Tokens section.
      6. Token endpoint URL: Paste URL from 1.e.ii in the Configure Azure App section
      7. Access token expires in: 3599 (as specified in the OAuth credentials obtained in the Create OAuth Access and Refresh Tokens section.
  5. Click Test Connection to verify the data source connection. 
    1. The following situations may cause error messages: 
      • Invalid credentials were supplied.
      • An invalid token endpoint was supplied.
      • The URL is improperly formatted, contains an invalid path, or is missing the /content suffix.
      • The Azure App is not granted the appropriate permission scope(s). If you add scopes, note that you need to generate new OAuth credentials.
      • Not specifying the column delimiter when using a delimited file source.

Unauthenticated Method

If the file is shared publicly, meaning anyone in the world with the link can access it, the Denodo data source can be configured without authentication. However, anyone in the world can access the file using the link generated via this method. Be sure you are adhering to University standard practices related to data and information security. Additionally, this method uses an undocumented URL parameter and may cease to function.

Obtain File URL

  1. In One Drive, share the file publicly using the “Anyone with the link” option with View access.
  2. Copy the Shared Link, paste it into a text editor, and append the following suffix: &download=1.

Create a data source

Create a Denodo data source appropriate for the type of publicly shared file to which you are connecting. The example below walks through connecting to a publicly shared CSV file.

  1. Open the Denodo VDP client.
  2. Connect to the Denodo environment.
  3. Navigate to New > Data source > Delimited file
  4. Set the Data Route to HTTP Client and configure the following: 
    1. Configuration tab:
      1. HTTP method: GET
      2. URL: Paste the modified URL you created in step 2 above
  5. Click Test Connection to verify the data source connection. 
Tags: 
Last Updated: 
Thursday, February 4, 2021