Access SharePoint (Microsoft Teams) files from Denodo at IU

• Overview
• Create an application in SharePoint
• Set up the data source in Denodo

---

Overview

Follow the instructions below to use a data file stored in SharePoint or Microsoft Teams as a data source in Denodo at Indiana University.

Create an application in SharePoint

Before you can access the data file from Denodo, you need to create an application in SharePoint to provide authentication details for the OAuth2 connection. You can use the same application for multiple files shared by your group. If you already have an application set up, you can skip to creating a new secret; UITS recommends you create a unique secret for each file you share.

To create an application:

1. Go to Microsoft Azure, and log in with your IU username followed by @iu.edu (do this no matter what your email address is). If you see a prompt that says "Stay signed in?", select Yes. If you are asked to start a tour or set up Azure advisor recommendations, you do not need to do so now. For more on logging into Azure, see About logging into Microsoft 365 or Azure at IU.

   Authentication using this application will fail if the application is later deleted. You can add more owners to the application later so that it isn't tied to your account alone.

2. In Azure, under "Azure services", select Microsoft Entra ID (you may need to choose More services to view the full list):

   

3. In Entra ID, from the left navigation menu, select App registrations.
4. At the top of the "App registrations" pane, select New registration, and complete the form:
   • Name: The app name will be shown when you create authentication tokens in Denodo. It should contain your group's name.
   • Supported account types: Select Accounts in this organizational directory only.
   • Redirect URI: Select Web from the drop-down menu and enter https://ebidvt-dev.uits.iu.edu/oauth/2.0/redirectURL.jsp in the text field.
5. Select Register.
6. In the left navigation menu, choose Certificates & secrets.
7. Under "Client secrets", select New client secret, and complete the form:
   • Description: Enter a description that will help you identify the secret later. UITS recommends creating a new secret for each file you share; therefore, it may help to describe which file the secret is for.
   • Expires: When the secret expires, authentication will stop working until you generate a new secret and tokens. Pick the shortest time feasible for your application.
8. Select Add.

9. Copy the secret's "Value" and save it elsewhere. You can use the Copy to clipboard icon (two pieces of paper) next to the value to easily copy and paste:

   

   The value functions similarly to a password, and you will not be able to access it again. If you do not copy and save the value now, you will need to delete the secret and create a new one.

10. In the left navigation menu, choose API permissions.
11. At the top, under "Configured permissions", select Add a permission. In the list of APIs that displays, choose SharePoint. Under "SharePoint", select Delegated permissions and then choose AllSites. Under "AllSites", select AllSites.read.
12. At the bottom, choose Add permissions.
13. At the top of the left navigation menu, select Overview.
14. Copy the value to the right of "Application (client) ID" and save it. You can use the Copy to clipboard icon (two sheets of paper) that appears when you hover over the value.
15. Although it is not required, UITS recommends adding additional owners to your application. Without additional owners, the application would stop working if you were no longer affiliated with IU. To add owners:
    1. In the left navigation menu, choose Owners.
    2. In the resulting pane, select Add owners, and use the search bar to find users you would like to add.
    3. Once you have made your selections, choose Select.

You should now have a client ID and a client secret saved. You will use these values to set up your connection in Denodo.

Set up the data source in Denodo

1. Open Microsoft Teams and log in.
2. If your data file is not already saved in Microsoft Teams, do so now:
   1. In the left navigation bar, select Teams, and choose the team where you want to host the file.
   2. At the top of the screen, select Files, and navigate to the folder (or create a folder) where you'd like to store the file.
   3. Select Upload to upload your file.
3. Navigate to the file in Microsoft Teams, and ensure that the account Denodo will use has access to the file. This account can be an individual or group account; however, it must be a member of your team.
4. Next to the file name, select ... (Show actions), and choose Copy Link. In the dialog that opens, select Copy to copy the URL to your clipboard. You will use this URL to create the file URL you will enter into Denodo later:
   1. The URL you copied will look similar to:

      https://indiana.sharepoint.com/sites/msteams_99999/Shared%20Documents/General/CSVFiles/movies_metadata.csv

      Note the value after /sites/ (in this example, msteams_99999).

   2. Determine the file URL using the template:

      https://indiana.sharepoint.com/sites/msteams_99999/_api/Web/GetFolderByServerRelativePath(decodedurl='/sites/msteams_99999/Shared%20Documents/General/CSVFiles')/Files('movies_metadata.csv')/$value

      • Replace msteams_99999 with the value you noted above.
      • Replace the path parameter after decodeurl=' with the portion of the URL you copied above between /sites/ and the file name. In this example, this portion is /sites/msteams_99999/Shared%20Documents/General/CSVFiles (note no slash at the end).
      • Replace the file name after Files(' with the file name from the URL you copied above (in this example, movies_metadata.csv).
   3. Save the file URL with all replacements for the time being; you will need it later to configure the HTTP connection in Denodo.
5. Open Denodo and navigate to the appropriate folder for your data source. Right-click the folder, and then select New > Data Source > [file type].
6. Fill out the fields in the resulting dialog:
   • Name: Give the data source a descriptive name.
   • Data Route: Select HTTP Client from the drop-down menu.
   • The other fields depend on the data file type; fill in appropriate values.
7. To the right of the "Data route" drop-down menu, select Configure. The "Edit HTTP Connection" dialog will open.
8. On the Configuration tab, enter the file URL you created earlier.

9. On the Authentication tab, fill in the following fields:

   

   • Authentication: Select OAuth 2.0.
   • Client identifier: Enter the client ID for your SharePoint application.
   • Client secret: Enter the client secret for your SharePoint application.
10. Select launch the OAuth 2.0 credentials wizard to help you obtain these credentials.

11. In the wizard, fill in the following fields:

    

    • Token endpoint URL: Enter https://login.microsoftonline.com/common/oauth2/token.
    • Authorization server URL: Enter https://login.microsoftonline.com/common/oauth2/authorize?resource=https://indiana.sharepoint.com.
    • Redirect URI: Select the second radio button and enter https://ebidvt-dev.uits.iu.edu/oauth/2.0/redirectURL.jsp.
12. Select Generate the authorization URL, and then choose Open URL. A new browser window will open and you will be prompted to log into SharePoint, if you haven't already. Log in with the account Denodo will use to access the data file.

13. A page with the URL for the OAuth 2.0 Credentials Wizard will display:

    

    Copy the entire URL to your clipboard.

14. Paste the URL into the wizard's "Paste the authorization response URL:" field:

    

15. In the wizard, select Obtain the OAuth 2.0 credentials. If successful, the wizard will display the message "The OAuth 2.0 credentials have been obtained."
16. Select OK.
17. Select Test connection. If the test is not successful, check all the values you have entered. If successful, proceed.
18. Select OK, and then choose Save.
19. Select Create base view, and then configure the base view for your data as desired.