Getting OAuth 2.0 Credentials¶

To get the OAuth credentials to configure the SharePoint for OData wizard, follow these steps:

1. Get the Realm of the site Realm is a constant GUID for a site. To retrieve it, a tool such as Postman can be used. To obtain the Realm, it is necessary to carry out the following steps:

   • Make a GET request like this:

     • https://<tenant_name>.sharepoint.com/_vti_bin/ListData.svc

     • Header. Authorization: Bearer

   • Get the Bearer realm component from the WWW-Authenticate response header and save it. This value is the same as the tenant_id.

   • Get the client id component from the WWW-Authenticate response header and save it. This value is what later we will call Audience Principal ID.

     

     Get the Realm of the site¶

     Note

     If you are using cookies you might not get back the WWW-Authenticate header with the Bearer realm. Delete the cookies in the browser or use the Postman Interceptor to avoid this issue.

2. Get the authorization code

   Construct an authorization URL as follows:

   https://<tenant_name>.sharepoint.com/_layouts/15/OAuthAuthorize.aspx?client_id=<client_id>&scope=AllSites.Read&response_type=code&redirect_uri=<redirect_uri>
   

   Change the parameters of the URL to fit your connection data. For the client_id use the Application (client) ID from the application registration in the Azure portal (this is different from the client id obtained in the previous step that will be used as Audience Principal ID).

   Note

   For this step the Azure Portal user will need to have the necessary permissions to consent to grant permissions to the app. In the previous sample URL we are requesting read permissions for all SharePoint sites, this would require Manage permissions on the resources requested.

   To make a request restricted to a specific site a URL like the following could be used requesting read permissions on an individual site:

   https://<tenant_name>.sharepoint.com/sites/<sitename>/_layouts/15/OAuthAuthorize.aspx?client_id=<client_id>&scope=Site.Read&response_type=code&redirect_uri=<redirect_uri>
   

   Navigate to the URL from your browser. Login to the site if you have not logged in already. This opens a consent page that prompts the user to grant (or deny) the app the permissions that the app requests.

   

   Grant permissions to the app¶

   Once you grant the permission (by clicking trust), the SharePoint Online site asks the Access Control Service (ACS) to create a short-lived (approximately 5 minutes) authorization code unique to this combination of user and app. ACS sends the authorization code to the SharePoint site.

   SharePoint Online site redirects the browser back to the redirect URI that was specified when the app was registered. It also includes the authorization code as a query string. The redirect URL is structured like the following: http://localhost:9090/oauth/2.0/redirectURL.jsp/?**code=<authcode>** or https://<hostname>:9443/oauth/2.0/redirectURL.jsp/?**code=<authcode>**

3. Get the access token and refresh token

   Construct the below POST request:

   • URL:

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

   • Header:

     • Content-Type = “application/x-www-form-urlencoded”

   • Post parameters (in the body of the request):

     • grant_type=authorization_code

     • client_id=<client_id>@<tenant_id>

     • client_secret=<client_secret>

     • code=<auth_code>

     • redirect_uri=<redirect_uri>

     • resource=<audience_principal_ID>/<tenant_name>.sharepoint.com@<tenant_id>

     Where:

     • <tenant_id> is the Bearer realm obtained in the step “Get the Realm of the site”.

     • <client_id> is <client id when registering the app>@<tenant_id from the step “Get the Realm of the site>.

     • <client_secret> is the client_secret obtained when registering the app.

     • <auth_code> is the auth code obtained in step “Get the authorization code”.

     • <resource> is <audience principal ID>/<sharepoint domain>@<tenant_id>.

     • <redirect_uri> is the redirect URL set when registering the app.

     • <audience_principal_ID> is a permanent security principal ID for SharePoint. <audience_principal_ID> is obtained in step “Get the Realm of the site” (the value “client_id” in the response header WWW-Authenticate).

Importing Artifacts¶

Now, you have all the data needed to use the SharePoint for OData wizard. Click the menu File > New > Data source and select Microsoft SharePoint for OData.

Microsoft SharePoint for OData wizard¶

Enter the following parameters:

• 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 . The access token generated in the VDP wizard for OAuth 2.0.

• Realm . The realm obtained in the previous step.

• Audience principal id. The value of the client id obtained in the previous step.

• 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 in the previous step.

• Refresh token . The refresh token generated in the previous step.

• Site name. The site name of the specific SharePoint Online site you want to retrieve the OData entities.

Now, click Execute import to create the data sources and the views to query SharePoint.

SharePoint data source and views created¶

Query Example¶

Now, you can execute queries on the SharePoint Online views that have been created.

Querying bv_site2_sitepages view¶

bv_site2_sitepages query results¶