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

Applies to: Denodo 8.0 , Denodo 7.0
Last modified on: 21 May 2020
Tags: SharePoint

Download document

You can translate the document:

This document describes how to access Sharepoint Online using the Denodo OData Custom Wrapper with OAuth 2.0 authentication.

Denodo allows to get the access tokens using the Denodo OAuth Wizard. However, Sharepoint Online requires some extra parameters not included in the wizard. Therefore, to connect to Sharepoint using Oauth it is necessary to manually obtain the tokens following these five steps.

Step 1: Register an app in SharePoint.

Step 2: Get the Realm and Audience Principal ID.

Step 3: Get the Authorization Code

Step 4: Get the Access Token and Refresh Token

Step 5: Configure the OData Wrapper

Note: in this article Postman is used to complete the whole OAuth flow. Any tool that allows to send requests and receive responses could be used.

Step 1: Register an app in SharePoint.

First of all, it is necessary to register Denodo in Sharepoint, confirming to Sharepoint that Denodo is a trustable application. If the app is already registered skip this step and go to the next section “Get the Realm and Audience Principal ID”.

In order to register Denodo in Sharepoint navigate to the following URL:

https://<mysite.sharepoint.com>/_layouts/15/appregnew.aspx

  1. Click Generate Client Id.
  2. Click Generate Client Secret.
  3. Give a name for the app. (e.g. Denodo 8.0)
  4. Fill in the app domain (e.g. www.denodo.com).
  5. Enter the Redirect URL. It should be https (e.g. https://localhost:9443/oauth/2.0/redirectURL.jsp)
  6. Click Create.

Write down the Client Id, Client Secret and Redirect URI. Those parameters will be required later.

Step 2: Get the Realm and Audience Principal ID

The Realm is a constant GUID for a site and the Audience Principal ID is a permanent security ID for SharePoint. To get the realm and principal ID make the following GET request:

URL

https://<mysite.sharepoint.com>/_vti_bin/client.svc

Header

Authorization: Bearer

Write down the Realm and Audience Principal ID. Those parameters will be required later.

Step 3: Get the Authorization Code

The Authorization Code is a temporary code that the client will exchange for an access token.

This code is necessary to obtain the Access and Refresh Tokens. To get the authorization code open the following URL in the browser:

https://<mysite.sharepoint.com>/_layouts/15/OAuthAuthorize.aspx?client_id=client_id&scope=app_permissions_list

&response_type=code&redirect_uri=redirect_uri

client_id

Client Id from registering the app in step 1.  

app_permissions_list

Describes the scope and the right to be granted for the app.

This parameter is a space-delimited set of permission scope and right requests (see table below).

redirect_uri

Redirect URL given in the step 1 when registering the app in SharePoint. Note that this url is encoded.

The table below describes the Scope URI, Scope Alias and the Right. The values listed in the Scope Alias column are shorthand versions of their counterparts in the Scope URI column. For more info on this please refer Understand permission scope aliases and the use of the OAuthAuthorize.aspx page.

URL Example:

https://<mysite.sharepoint.com>/_layouts/oauthauthorize.aspx?client_id=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx&scope=Web.Read&response_type=code&redirect_uri=https%3A%2F%2Flocalhost%3A9443%2Foauth%2F2.0%2FredirectURL.jsp

Navigate to this URL from the browser and login into the site. It opens a consent page that prompts the user to grant (or deny) the permissions that the app requested. In this case, the user would be granting the app read access to the current Site.

Once the permission is granted (by clicking trust), SharePoint Online asks ACS to create a short-lived (approximately 5 minutes) authorization code unique to this combination of user and app.

SharePoint Online will redirect the browser back to the redirect URI that was specified when the app was registered (step 1). The redirect URL is structured like the following:

https://redirect_url/?code=<authcode>

Extract the code from above url. It will be used in the next step. This is the authorization code and it lasts for approx. 5 minutes.

Note: App registration can be verified using the following URL:

https://<mysite.sharepoint.com>/_layouts/15/appprincipals.aspx

The output will be displayed as below for the app name that is registered.

Step 4: Get the Access Token and Refresh Token

Use Postman (or any other HTTP Client)  to retrieve the Access and Refresh Tokens. To get them make the following POST request:

URL

https://accounts.accesscontrol.windows.net/<site_realm>/tokens/OAuth/2

HEADER

Content-Type=application/x-www-form-urlencoded

BODY

grant_type=authorization_code

&client_id=<client_id>@<site_realm>

&client_secret=<client_secret>

&code=<auth_code>

&redirect_uri=<redirect_url>

&resource=<audience_principal_ID>/<mysite.sharepoint.com>@<site_realm>

<site_realm>

Realm obtained in step 2.

<client_id>

Client Id from registering the app in step 1.  

<site_realm>

Realm obtained in step 2.

<client_secret>

Client Secret from registering the app in step 1.

Note: If the client secret contains + symbol, it should be encoded by replacing + with %2B

<auth_code>

Code obtained in step 3.

<redirect_url>

Redirect URL given in the step 1.

<audience_principal_ID>

Audience Principal ID obtained in the step 2.

<mysite.sharepoint.com>

Host name

<site_realm>

Realm obtained in step 2.

The response contains the access_token and refresh_token that will be needed in the last step to configure the OData Wrapper.

Step 5: Configure the OData Wrapper

The last step is to configure the Denodo OData 2 Custom Wrapper. Fill the parameters following the instructions from the KB article Microsoft SharePoint as data source.

Select the option “Use OAuth2” and provide the following values:

Access Token

access_token from step 4.

Refresh Token

refresh_token from step 4.

Client Id

<client_id>\@<site_realm> 

<client_id> from registering the app in step 1.

<site_realm> obtained in step 2.

Client Secret

Client Secret from registering the app in step 1.

Note: You can use client secrets as it is. No need to encode here.

Token Endpoint URL

https://accounts.accesscontrol.windows.net/

<site_realm>/tokens/OAuth/2

<realm> obtained in step 2.

OAuth Extra Parameters

resource="<audience_principal_ID>/<mysite.sharepoint.com>\@<site_realm>"

<audience_principal_ID> audience Principal ID obtained in step 2.

<mysite.sharepoint.com> is the host name.

<site_realm> realm obtained in step 2.

Refr. Token Auth. Method

Include the client credentials in the body of the request

    

This is how it will look like in ODatawrapper

Save the changes and select the Create base View option. This will display the Edit Wrapper Parameter Values dialog. For e.g. ComposedLooks

You can define any collection name in the Entity Collection textbox .To know the list of available collections, browse the service endpoint URL given in OData Wrapper datasource configuration.

URL

https://<mysite.sharepoint.com>/_vti_bin/client.svc

Questions

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

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training