Microsoft Sharepoint Connector

Squirro provides a 1-click connector for Microsoft SharePoint. This connector allows the Project Creators to connect to a Microsoft Azure account and index the Microsoft SharePoint data into Squirro.

Set up Connector

Usage assumes that the OAuth setup for the 1-click connectors has already been done on the server. This is by default available on all of our cloud servers at https://start.squirro.com. For any other Squirro installation, please ask your Squirro server admin to follow the steps in the OAuth Configuration section below.

• Head over to the Enterprise tab on the Data screen as shown in the screenshot below.

• Click on the Microsoft SharePoint icon. Squirro will show a prompt asking you to authenticate your Microsoft Azure account. Click on the Authenticate button there.

• Once you click on the Authenticate button, you will be re-directed to the Sign-in screen hosted by Microsoft.

Squirro will never ask you for your Microsoft password. This is requested by Microsoft itself.

• Once you sign-in you will be prompted to approve Microsoft SharePoint scopes. This screen is presented to you by Microsoft asking you to provide consent for your Microsoft SharePoint data to Squirro.

• After approving scopes you will be brought back to Squirro’s source configuration screen. This screen shows which Microsoft account Squirro has connected to.

• Now, you can click Save & Exit for Squirro to automatically configure the mapping of item fields and labels, as well as any other required source configuration.

Media files content (image, video, audio) is not fetched by default. To change that behaviour expand the Advanced Options section and check the option to fetch media files content.

• Alternatively you can define those settings manually by clicking Next and going through the whole source setup process. This allows you the full flexibility of how the Microsoft SharePoint data should be mapped to Squirro items.

• After clicking on Save & Exit, you will see your source running. Sit back, relax & enjoy while we index your Microsoft SharePoint data into Squirro.

Advanced Options

1. Index All

When active, all files are indexed even if their content cannot be retrieved. In that case, only metadata is indexed, but the files are still discoverable through the typeahead search and facet filtering.

2. Download media files content

When active, the content of the media files (image, video, audio - as determined by filename) is downloaded and indexed. If inactive, the media files are skipped.

3. Matching path substring

Allows specifying a substring to filter the file paths. Only the files with matching paths are imported into Squirro. The condition checks if one string is contained within another using in. The field value is case-sensitive and does not rely on regular expressions. The strings using * are not interpreted correctly.

4. Detect file deletions

When active, Squirro automatically detects file deletions in SharePoint and removes the corresponding files from its index.

5. List of drive ids

Specify a list of drive IDs, separated by semicolons ; to load items only from the selected drives. Note that exact matches are required, and the list must be formatted without spaces or additional characters

6. List of allowed site names

Specifies a list of allowed site names separated by ;. The matching is case-sensitive, and names are stripped of white characters at the beginning and end. The algorithm uses the name field rather than displayName. Allowed names have a lower priority than denied names.

7. List of denied site names

Specifies a list of denied site names separated by ;. The matching is case-sensitive, and names are stripped of white characters at the beginning and end. The algorithm uses the name field rather than displayName. Denied names have a higher priority than allowed names. If the list is expanded to include previously accepted items, those items are not deleted retroactively.

OAuth Configuration

App Config

You will need to register an OAuth2 app on the Microsoft Azure portal to allow Squirro to connect to the Microsoft accounts of end users.

Note

The exact process for registering an 0Auth2 app on the Microsoft platform may change. If you notice big discrepancies between the current Microsoft website and the documentation page here, contact Squirro Support for assistance.

Register an Application

Note

If you are working off the Squirro cloud, the application will already be registered and you can skip this section. If you are working off a Squirro on-premise or private cloud installation, you will need to register the application.

To register an OAuth2 app on the Microsoft Azure portal, follow the steps below:

1. Go to the Microsoft Azure Portal at https://portal.azure.com/#blade/Microsoft_AAD_RegisteredApps/ApplicationsListBlade.

2. Click Register an application.

3. On the next screen, choose the following options as shown in the screenshot below and then click Register:

   • Name: Name of the app

   • Supported Account Types: “Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox)”

   • Redirect URI: This is the URL Microsoft will redirect the user to after successful authorization. This needs to be set to https://<your-server-url>/dataloader/sharepoint_plugin/pl/azure/authorized on your server.

     For example: https://squirro.example-company.com/dataloader/sharepoint_plugin/pl/azure/authorized.

Add Scopes

4. Navigate to the API permissions tab and click Add a Permission.

5. Click Microsoft Graph.

6. Navigate to the Delegated permissions section.

7. On the next screen add the following scopes:

   • email

   • offline_access

   • Files.Read.All

   • GroupMember.Read.All

   • Sites.Read.All

   • User.ReadBasic.All

   • User.Read.

Note

GroupMember.Read.All scope is used to download files from the Group Drives. However, GroupMember.Read.All is a tenant-wide scope and requires admin consent to use.

Reference: For information on how to grant tenant-wide admin consent, see Grant Admin Consent.

8. After adding scopes, confirm your choices by clicking Add Permissions.

Granting Permissions

9. Navigate to the Enterprise Applications section, find the app that was registered, and select Permissions in the left navigation menu.

10. Click Grant admin consent for <your-app>, as shown in the example screenshot below.

Note, that a user must have admin rights to grant permissions. Also, asking an admin to give users rights to grant permissions is possible. See Configure how users consent to applications.

11. Navigate to the Token configuration tab, click Add optional claim and add a new claim as follows:

• Token type: ID

• Claim: email

Create a Client Secret Key

The final step is to create a Client secret key for your app. To do so:

12. Navigate back to the App registration section and click on your App.

13. Navigate to the Certificates & secrets tab.

14. Click New client secret.

15. Create a new secret key with the following settings:

• Description: Name of your key (whatever you want)

• Expires: Period of time after your secret key will expire (max period of time is 24 months).

Apply for Production

Unverified apps will show a warning about unverified status during the user authorization process when the users connect their Squirro instance to their Microsoft account. To avoid that, you have to apply for Production status of the Microsoft SharePoint app.

To start that process you first have to configure the Branding. This will require a logo icon, homepage URL, links to your terms of service and privacy policy. For more information go to the link: https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-configure-publisher-domain.

Squirro Configuration

After having created the OAuth2 app on the Microsoft Azure portal, you need to configure the Client ID and the Client Secret on your Squirro instance.

• Go to the Overview tab of the app and copy Application (client) ID.

• Next switch to the Certificates & secrets tab and copy the value of your Client Secret key.

This configuration will soon be possible from the user interface.

• Edit /etc/squirro/common.ini on your Squirro cluster and add the following lines:

[dataloader]
sharepoint_client_id = YOUR_ID
sharepoint_client_secret = YOUR_SECRET

• To enable org-wide access scopes used by your app also add the following line to the [dataloader] header:

sharepoint_org_scopes_enabled=true

Note that this requires tenant-wide admin consent described in the App Config section.

• If the [dataloader] header is already present in this file, add the lines to the existing section. The section header can not appear more than once in the configuration file.

• Restart the frontend & datasource service to apply the settings:

sudo systemctl restart sqfrontendd
sudo systemctl restart sqdatasourced

ACL Configuration via Microsoft Azure Active Directory

Using query templates, you can configure the ACLs for your Microsoft SharePoint data.

This allows you to configure permissions within your projects so that users can only see the data that they have access to in Microsoft SharePoint.

Prerequisites

To configure permissions, three things are required:

• Users must be logged into Squirro with the same Microsoft account that is used to access the Microsoft SharePoint data. In other words, the SSO used by Squirro must be the same organizational Microsoft Azure account as the SSO used by Microsoft SharePoint.

• Users requesting the API must have the Contribute permission level and above permissions level in SharePoint.

• You must have administrative access for both the Squirro and SharePoint.

Note

This feature does not work with Squirro cloud instances unless those instances have been configured to use Microsoft for SSO rather than SquirroID.

How to Set Up

To configure permissions, follow the steps below:

Part 1 - Set Project Configurations

1. Log in to your Squirro project.

2. Navigate to Setup > Settings

3. Click Project Configuration from the left menu.

4. Set dataloading.get-microsoft-user-groups to True.

Part 2 - Use SAML-SSO Authentication

1. Configure Azure SSO by following the steps in Azure Active Directory Setup.

2. Provide the following configuration in the SAML-SSO authentication Fields to map in as user’s session field:

‘msAzureUserId = http://schemas.microsoft.com/identity/claims/objectidentifier’

Part 3 - Create and Configure a Query Template

1. Create a query template by following the steps in Query Templates.

2. Use the following query template to configure the ACLs for your Microsoft SharePoint data:

({%- if msAzureUserId -%} acl_users:{{msAzureUserId[0]}} OR {%- endif %} acl_users:{{email}} {%- for item in user_values['microsoft_user_groups'] %} OR acl_groups:{{item}} {%- endfor %}) AND source_type:"Microsoft SharePoint" OR NOT source_type:"Microsoft SharePoint"

Reference: For more information, see Microsoft Graph v1.0 - List Group Owners and Microsoft Graph v1.0 - driveItem: delta.