How to integrate Denodo with SharePoint Online

You can translate the document:

In this document we will review the different options to connect to data in Microsoft SharePoint online. First, we will see how to configure an application in Azure for authentication. Then, we will see 3 possible ways to access SharePoint Online from Denodo Virtual DataPort:

  • OData2: to access SharePoint OData entities.

  • API REST data source: to access SharePoint using its REST API.

  • Excel, JSON, XML and Delimited File Data Sources: to access SharePoint’s REST API to retrieve data in files.

  • Connecting via Microsoft Graph API: another way to access SharePoint documents using the universal Graph API.

The following table summarizes the possible ways to access SharePoint:

NEED

CONNECTION METHOD

AUTHENTICATION

Access metadata of Files, Folders, Lists or Items; and basic OData/Rest entities on any Sharepoint site.

SharePoint for OData and SharePoint Rest API

OAuth 2.0

Access file information

Excel, JSON, XML and Delimited File Data Sources

OAuth 2.0

Access file information

Graph API

OAuth 2.0

In all cases it is necessary to register and configure an app in Azure as described below.

Configuring the application in Azure

First of all, it is necessary to register an App, provide the correct API permissions and generate a client secret in the Azure Portal in order to allow the app (in this case Denodo) to access SharePoint.

In order to register an app in Azure, follow these steps:

  1. Login to the Azure portal

  1. Navigate to https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps

  1. Press the button New Registration

  1. Give a name for the app

  1. Fill in the Support Account Types field. The type of the application support depends on your requirement. For the purpose of this documentation we are using Accounts in this organizational directory only (Default Directory only - Single tenant).

  1. Enter Redirect URL value. The default value is http://localhost:9090/oauth/2.0/redirectURL.jsp. Note that http only can be used with localhost, if you have HTTPS enabled in your Denodo Virtual DataPort, then use your hostname instead of localhost: https://<hostname>:9443/oauth/2.0/redirectURL.jsp.

  1. Press the button “Register”.

Once the app is registered we will need to grant permissions for clients to access SharePoint Online using the app:

  • In the newly registered application, click on Integration assistant (on the left side menu) and select Daemon for What application types are you building and select Evaluate my registration.
  • Once the evaluation is done, the Recommended configurations are shown.

There are 2 needed configurations that have to be performed to allow the Denodo Platform to access the data in SharePoint: API permissions and client secret generation.

In order to configure API permissions follow the following steps:

  • Click on the    icon on Configure API permissions and select Go to page.
  • Select Add Permissions. In the Request API permissions pop-up select SharePoint.
  • Select Delegated Permissions and select AllSites.Read as the permissions.

Note: Microsoft Entra ID provides both Delegated and Application permission models, currently the Denod Sharepoint Online data source only supports Delegated Permissions. However, you can use Application permissions when using Excel, Delimited File, JSON or XML data sources, for instance to access Sharepoint data through the Microsoft Graph API.

If you prefer to use Application permissions, select those permissions instead of Delegated permissions after selecting SharePoint. Furthermore, select  Grant admin consent once the permissions have been assigned.

After the API permissions are set we have to generate a new client secret key. In order to generate the key:

  • In the Integration assistance section, select Configure a valid credential.
  • In the Certificates & secrets page select the option New client secret.
  • After generating the Client secret, copy the Value.

 

After following these steps, you should have gathered these values:

  • Application (client) ID which will be the Client Id in the data sources configuration.
  • Value of the Client Secret from step “Client secret key generation”
  • The Directory (tenant) ID which will be the tenant_id needed later.

Get the OAuth tokens

If you need to save the OAuth tokens to use, for example, in the JSON-based parts of the Denodo templates for SharePoint, you can generate them using the OAuth credentials wizard following the steps described below. This wizard can be found also in each Data Source configuration after selecting OAuth as authentication method.

Using the OAuth credentials wizard with Delegated permissions

A new window will pop up, where you need to provide the requested information:

  • Authentication grant: select Authorization code grant.
  • Client identifier: the client id from the newly created Azure application.
  • Client secret: the value of the secret generated in the previous step for the newly created application.
  • Token endpoint URL:  https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token
  • Authorization server URL : https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize
  • Redirect URI: the same that you have configured in the Azure app.
  • Scopes: add  https://<tenant_name>.sharepoint.com/AllSites.Read and offline_access.

After providing all the values click on the Generate Authorization URL option and, when the Authorization URL is generated, click on the open URL link.

Once the URL is opened on your desired  browser, Accept to grant the needed permissions:

When the permissions are given, a URL with a code would be generated. The format of the URL will be: //http://localhost:9090/oauth/2.0/redirectURL.jsp/?code=<>&state=<>&session_state=<>#

Copy that URL and paste it in Paste the authorized response URL.

After that you only need to select Obtain the OAuth 2.0 credentials.

Finally, click on Copy the credentials to the clipboard to save the OAuth tokens.

Using the OAuth credentials wizard with Application permissions

The same window will pop up, but in this case the Authentication grant method selected must be Client credentials grant. The rest of the fields will be completed as follows:

  • Client identifier: the client id from the newly created Azure application.
  • Client secret: the value of the secret generated in the previous step for the newly created application.
  • Token endpoint URL:  https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token
  • Scopes: add  https://<tenant_name>.sharepoint.com/.default and offline_access.

Clicking on OAuth 2.0 credentials the token must be generated without authentication.

Microsoft SharePoint for OData data source

Click the menu File > New > Data source and select Microsoft SharePoint for OData.

Then you have to fill the following fields:

  • Database: Name of the database in which the SharePoint data sources and views will be created.
  • View name prefix: The name prefix of every view created in order to be able to relate VDP views to SharePoint Online sites. The max length of this parameter is 10 characters.
  • SharePoint Online service name: Your SharePoint Online service name (tenant name).
  • Realm:  It is the identifier of your directory in Azure (Tenant ID) generated when registering the Azure app.
  • Client ID: The client id generated when registering the Azure app.
  • Client Secret: The client secret generated when registering the Azure app.
  • Access token: The access token generated from the OAuth Credentials wizard.
  • Refresh token: The refresh token generated from the OAuth Credentials wizard.
  • Site name: The site name of the specific SharePoint Online site you want to retrieve the OData entities.

Click Execute import. If all the parameters are correct, you will see a new folder in the selected database, called OData entities <prefix>.
All the base views available will be created to retrieve the information you want.

Query Example

For example, if you want the metadata of the pages created in your site. You can execute a select on the view bv_<prefix>_sitepages.

Here is an example of the response:

Note: If you want more information about Sharepoint for OData, you can review the SharePoint For OData - User Manual.

Note: SharePoint OData is subject to service-specific limitations that may impact query execution. For example, requests that exceed the maximum URL length supported by the Microsoft connector may fail with an HTTP 404 response. For additional details, refer to Microsoft's OData connector documentation.

Microsoft SharePoint REST API data source

Similarly to the previous section, you can connect to SharePoint using its REST API, Go to File > New > Data source and select Microsoft SharePoint REST API.

Then you have to fill the following fields (that are the same as the OData data source):

  • Database: Name of the database in which the SharePoint data sources and views will be created.
  • View name prefix: The name prefix of every view created in order to be able to relate VDP views to SharePoint Online sites. The max length of this parameter is 10 characters.
  • SharePoint Online service name: Your SharePoint Online service name (tenant name).
  • Realm:  It is the identifier of your directory in Azure (Tenant ID) generated when registering the Azure app.
  • Client ID: The client id generated when registering the Azure app.
  • Client Secret: The client secret generated when registering the Azure app.
  • Access token: The access token generated from the OAuth Credentials wizard.
  • Refresh token: The refresh token generated from the OAuth Credentials wizard.
  • Site name: The site name of the specific SharePoint Online site you want to retrieve the OData entities.

Click Execute import. If all the parameters are correct, you will see a new folder in the database selected, called API Rest <prefix>. The views created in this case are different from the ones created using OData:

If you want more information about the purpose of each view, review the SharePoint REST API - User Manual.

Query Example

To retrieve all content from a specific folder, such as MyLibrary, execute a query on the <prefix>_folder_full_content view, filtering by the desired folder in the WHERE clause:

Connecting to SharePoint using Excel, Delimited File, JSON and XML data sources

In this section we will explain how to create an Excel data source in Denodo to read Excel files from SharePoint. The same steps can be performed for JSON, XML or Delimited files as well.

Obtain the URL or PATH

The URL to extract a file will be follow this format:

https://<tenant_name>.sharepoint.com/sites/<your_site>/_api/Web/GetFileByServerRelativeUrl('<location of an Excel file in the sharepoint>')/$value

Note: The URL assumes that you want to access SharePoint elements in a specific site called <your_site>. Replace this parameter with your actual site name.

Note: If you want to actually access the root of your SharePoint and not a specific site, instead you would use the following URL.

https://<tenant_name>.sharepoint.com/_api/Web/GetFileByServerRelativeUrl('<location of an Excel file in the sharepoint>')/$value

  • To know the location of a file in your Sharepoint, you can navigate in SharePoint to the element itself, click on the three dots and then on Details. You will find the absolute path in the bottom and can use the copy button to know the exact location.

Based on this location, the URL specified above must be composed. For example, if the Path for that Excel is:

https://<tenant_name>.sharepoint.com/sites/<your_site>/Shared%20Documents/Book.xlsx

the final composed URL that you need to use would look like this:

https://<tenant_name>.sharepoint.com/sites/<your_site>/_api/Web/GetFileByServerRelativeUrl(decodedurl='/sites/<your_site>/Shared%20Documents/Book.xlsx')/$value

NOTE: A good test to see if the final composed URL is correct or needs still adjustment is to test it in the browser. If the URL triggers a download with a file called $value then the link is correct and that is the one that you need to use from Denodo.

Using the Microsoft SharePoint REST API or Microsoft SharePoint for OData views that we created in the previous sections, it is also possible to obtain the URL of a file automatically in a view.
There is the possibility to use different views, like
bv_site_file (if you only want the information of one file) or bv_site_files_in_folder (if you want to construct the url for more than 1).

The best way is to create a Select View above the view that retrieves the metadata and add a new field to this view, using the serverrelativeurl column:

concat('https://<tenant_name>.sharepoint.com/sites/<site_name>/_api/Web/GetFileByServerRelativeUrl(''', serverrelativeurl, ''')/$value')

Executing this view you will have all the urls for the files listed.

Create the data source

  • From the Design Studio, create the Data Source of the type of the file that you want to access.
  • In the data source configuration, select the File Location as HTTP Client.
  • In the HTTP Configuration use the HTTP Method as GET and provide the URL obtained in the previous section.
  • In the Authentication section, select OAuth 2.0 for Authentication.
  • Select the Authentication Grant as Authorization code grant or Client credentials depending on your preferences.

  • Then complete all the fields with the information retrieved in section “Get the OAuth tokens”.
  • Otherwise, you can select the Launch the Oauth 2.0 credentials wizard. The Oauth 2.0 credentials wizard will pop up. Review the section “Get the OAuth tokens” above.

Once the OAuth 2.0 Credentials are filled, test the connection to check if the connection can be established.

Now you will be able to create a new base view from the datasource, an Excel data source in this example. If you query the base view created, you will have access to the information of the file.

Connecting via Microsoft Graph REST API

Another option to connect to Sharepoint instead of using the explained traditional Sharepoint API, is to use the universal Microsoft Graph endpoint. This is particularly useful for unified access across Office 365 services. In case you want to use this endpoint you have to make some changes on the configuration steps done.

Configure Azure App Permissions

Follow the standard app registration process in the Azure Portal as described in the “Configuring the application in Azure” section, with these quick tweaks to the permissions step:

  • API Selection: In the "Request API permissions" pop-up, select Microsoft Graph instead of SharePoint.

  • Permissions: Choose Delegated permissions or Application permission and add the scopes Sites.Read.All.
  • Admin Consent: Independently on the permissions selected, do not forget to click Grant admin consent.

Obtain OAuth Tokens for Graph

To obtain the OAuth tokens you can follow the section “Get OAuth tokens” in this document, selecting the guide of Delegated or Application permissions depending on your previous decision.

Using Graph API you have to change the value of the scope field, swapping out the SharePoint-specific scopes for Graph scopes, `https://graph.microsoft.com/.default` and `offline_access`. Letting all the other fields as explained.

Obtain the URL or PATH

For the creation and setup of the data source, refer to the procedures outlined in the earlier “Create the data source” section. The primary distinction lies in the URL; when utilizing the Graph API, several endpoint formats are available:

Utilizing the itemId represents a more robust approach; since the identifier remains constant, the connection will persist even if the file is subsequently moved or renamed within the SharePoint environment. To know more about the Graph Api refers to the official documentation.

References

Quickstart: Register an application with the Microsoft identity platform

SharePoint For OData - User Manual

SharePoint REST API - User Manual

Microsoft Graph API - Official Documentation

Disclaimer

The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.

For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.
Recommendation

Recommended resources Recommendations generated by AI

How to connect Denodo to Sharepoint Online using OData with OAuth authentication

Microsoft SharePoint as data source

How to access Excel and Delimited Files in SharePoint from Denodo

How To Integrate the Google Drive API in Denodo

How to connect to Google Cloud Storage from Denodo

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here