Content

In this document we explain  how to create an Excel data source in Denodo that retrieves an Excel file stored in SharePoint 365 using the Sharepoint API. The same steps can be performed for Delimited Files as well.

It is also possible to access SharePoint files using OData following the steps described in How to connect Denodo to Sharepoint Online using OData with OAuth authentication. Unless you need to access SharePoint using OData the procedure explained in this document is the recommended approach.

Creating an App in the Azure Portal

For the SharePoint application to be accessible, API permissions have to be configured. A client application token has to be generated for the access token. In order to generate the token the following has to be done in the Azure Portal:

• Login to the Azure portal
• Click on App registrations > New registration.
• Select the desired supported account types. 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).
• Provide a Redirect URI. The redirect URI does not have to be a valid one. But it is recommended to be a HTTPS URL.

Configuring access to Sharepoint

Once the app is registered we will need to grant permissions for clients to access SharePoint 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.

API permissions

• 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

Client secret key generation

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.

The API access is now set up to connect Denodo to SharePoint.

In the App Registration page in the Azure portal, select the app that has been registered for the SharePoint integration with Denodo:

• Copy the Application (client) ID this will be the Client identifier in the data source configuration.
• Copy the Value of the Client Secret generated for the new application.

Creating an Excel data source

In this section we will explain how to create an Excel data source in Denodo to read Excel files from SharePoint.

• From the Virtual DataPort Administration Tool or Design Studio, create an Excel Data Source.
• In the data source configuration, select the File Location as HTTP Client and select Configure.
• In the Edit HTTP Configuration dialog box set the HTTP Method to GET and provide the URL with the following format:

• In the Authentication section, select OAuth 2.0 for Authentication.
• Paste the Application (client) ID from the newly created application to the Client Identifier and the Value of the Client Secret to the Client Secret.

• Then select the Launch the Oauth 2.0 credentials wizard. The Oauth 2.0 credentials wizard will pop up.
• In this dialog, provide EndPoint URL from the Azure portal to the  Token endpoint URL  as https://login.microsoftonline.com/common/oauth2/token
• Provide the Authorization URL

• Select the Redirect URI, provide the redirect URI that you used to register an application in the first step.
• For the Scopes option provide the value AllSites.Read.
• After providing all the URL’s click on the option Generate Authorization URL.
• When the Authorization URL is generated, click on the open URL.
• Once the URL is opened on your desired  browser, provide the permission, and once the permission is given a URL with code would be generated. The format of the URL will be:

• Paste the same on the Paste the authorized response URL and select Obtain the OAuth 2.0 credentials.
• Select Ok.

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

Now you will be able to create a new Base view from this Excel data source.

References

Quickstart: Register an application with the Microsoft identity platform

Excel Sources