Microsoft SharePoint Connectors#

Squirro provides two 1-click connectors for Microsoft SharePoint. Both connectors allow Project Creators to connect to a Microsoft Azure account and index Microsoft SharePoint data into Squirro, but they use different authentication methods:

  • Microsoft SharePoint Connector

    Uses OAuth authentication (standard method).

  • Microsoft SharePoint Azure VM Connector

    Uses Managed Identity authentication (requires Azure VM deployment).

In the Squirro web interface, these appear as two separate connectors in the Enterprise section. Choose the appropriate connector based on your authentication requirements and deployment environment.

Set up the Connector#

Choose the appropriate setup method based on your connector type. Both authentication methods share the same Advanced Options for customizing data indexing behavior.

OAuth Authentication for Microsoft SharePoint#

Usage assumes that you have already configured the OAuth setup for the 1-click connectors on the server. It is by default available on all of the cloud instances deployed and managed using start.squirro.com. For any other Squirro installation, please ask your Squirro server admin to follow the steps presented in the OAuth Configuration section.

  • Click on Configure A New Data Source then navigate to the Enterprise section of the Data Sources dashboard.

    SharePoint connector Enterprise tab screenshot
  • Click on Microsoft SharePoint, then Configure to get a prompt asking you to authenticate your Microsoft Azure account. Click on the Authenticate button.

    SharePoint authentication prompt screenshot
  • Once you click on the Authenticate button, you are redirected to the Sign-in screen hosted by Microsoft.

    Microsoft sign-in screen screenshot

Squirro never asks for your Microsoft password. This request comes only from Microsoft itself.

  • Once you sign in you are 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 the scopes, you are brought back to the Configure source screen. That screen shows which Microsoft account Squirro uses.

    Squirro source configuration screen screenshot
  • 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.

    Tip

    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. It allows you the full flexibility of how the Microsoft SharePoint data should be mapped to Squirro items.

  • After clicking on Save & Exit the system starts indexing your Microsoft SharePoint data into Squirro.

    SharePoint data source running screenshot

Managed Identity Authentication for Microsoft SharePoint Azure VM#

The Microsoft SharePoint Azure VM connector provides seamless integration for Squirro instances deployed on Azure virtual machines using Managed Identity authentication. This approach eliminates the need for OAuth configuration while leveraging Azure’s native identity management capabilities. Follow the setup instructions below to configure your Azure VM environment.

  • Click on Configure A New Data Source then navigate to the Enterprise section of the Data Sources dashboard.

    Configure new data source Enterprise block screenshot
  • Click on Microsoft SharePoint Azure VM, then Configure, no authentication is required, and the system automatically redirects you to the connector configuration page.

    Managed Identity connector configuration page screenshot

    Tip

    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. It allows you the full flexibility of how the Microsoft SharePoint data should be mapped to Squirro items.

  • After clicking on Load Data, the system starts indexing your Microsoft SharePoint data into Squirro.

Advanced Options#

SharePoint Advanced Options part 1 screenshot
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.
SharePoint Advanced Options part 2 screenshot
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 OAuth2 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.

    Azure Portal register application button screenshot
  3. On the next screen, choose the following options as shown in the screenshot below and then click Register.

    Azure application registration form screenshot
    • Name: Name of the app.

    • Supported Account Types: “ccounts in any organizational directory (Any Entra ID directory - Multitenant) and personal Microsoft accounts (for example, Skype, Xbox).

    • Redirect URI: This is the URL Microsoft redirects the user to after successful authorization. Set this to the following format on your server:

      https://<your-server-url>/dataloader/sharepoint_plugin/pl/azure/authorized
      

      Replace <your-server-url> with your actual Squirro server URL. For example:

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

Add Scopes#

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

    API permissions add permission button screenshot
  1. Click Microsoft Graph.

    Microsoft Graph API selection screenshot
  2. Navigate to the Delegated permissions section.

    Delegated permissions section screenshot
  3. On the next screen, add the following scopes:

    • email

    • offline_access

    • Files.Read.All

    • Group.Read.All

    • Directory.Read.All

    • GroupMember.Read.All

    • Sites.Read.All

    • User.ReadBasic.All

    • User.Read.

    Note

    The GroupMember.Read.All permission enables access to group membership information and files stored in Microsoft 365 Group Drives. This application permission provides tenant-wide access to group data and requires administrator consent from a Global Administrator or Privileged Role Administrator before it can be granted.

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

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

    Add permissions confirmation screenshot

Granting Permissions#

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

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

    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.

    Grant admin consent
  3. Navigate to the Token configuration tab, click Add optional claim and add a new claim as follows:

    • Token type: ID

    • Claim: email

    Token configuration add optional claim screenshot

Create a Client Secret Key#

The final step is to create a Client secret key for your app.

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

  2. Navigate to the Certificates & secrets tab.

  3. Click New client secret.

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

    • Description: Enter a descriptive name for your client secret (for example, Squirro SharePoint Connector Key).

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

    Create client secret key screenshot

Apply for Production#

Unverified applications display a security warning during the user authorization process when users connect their Squirro instance to their Microsoft account. To avoid that warning, apply for Production status for your Microsoft SharePoint application.

The production verification process requires configuring the application branding with the following elements:

  • Application logo icon

  • Homepage URL

  • Terms of service URL

  • Privacy policy URL

For detailed instructions on publisher domain configuration and verification requirements, see the Microsoft documentation.

Squirro Configuration#

After creating 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.

    Application client ID overview screenshot
  • Next, switch to the Certificates & secrets tab and copy the value of your Client Secret key.

    Certificates and secrets client key screenshot
  • 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 it requires the tenant-wide admin consent.

  • 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 Squirro services to apply the configuration changes:

    sudo systemctl restart sqfrontendd sqdatasourced
    

VM Azure Configuration#

This section applies only to the Microsoft SharePoint Azure VM Connector using Managed Identity authentication.

Create an Azure VM#

To begin, create a virtual machine (VM) in Azure.

  • Sign in to the Azure Portal at https://portal.azure.com.

  • Select the Virtual Machines resource.

  • Go to the Virtual Machines tab and click Create.

    Azure Virtual Machines create button screenshot

Settings#

Basics Tab#
  • Subscription: Choose your Azure subscription.

  • Resource group: Select an existing resource group or create a new one.

  • Virtual machine name: Assign a name to your VM (e.g., squirro-vm).

  • Region: Select a region that is geographically close to your users.

  • Image: It is preferred to use a Rocky Linux or RHEL 8/9 operating system.

  • Size: Click “See all sizes” and select according to Squirro’s requirements https://docs.squirro.com/en/latest/getting/install/system-requirements.html.

  • Authentication type: You can choose between an SSH public key or a password.

  • Username: Specify a username, for example, azureuser.

    Azure VM basics tab settings screenshot
Disks Tab#

For improved performance, it is recommended to use Premium SSD (P20 preferred).

Azure VM disks tab settings screenshot
Networking Tab#
  • Virtual network: You can either use an existing virtual network or create a new one.

  • Subnet: User default or create a new one.

  • Public IP: Keep the Public IP enabled to allow SSH connections.

  • NIC network security group: Set to Basic and permit SSH traffic.

    Azure VM networking tab settings screenshot
Management Tab#
  • Enable System Assigned Managed Identity. It can also be configured later from the ‘Identity’ tab of the VM.

  • Click Review+Create to review your configurations to ensure they are correct.

  • Click Create to deploy the virtual machine.

  • Once the virtual machine is created, navigate to the VM overview page and click Connect to access connection options.

    To connect via SSH from your local terminal, use the following command:

    ssh azureuser@<your-public-ip-or-fqdn>
    

    Replace <your-public-ip-or-fqdn> with either your VM’s public IP address or its fully qualified domain name (FQDN), both of which you can find on the VM overview page.

Enable Managed Identity and Assign Permissions#

You can enable the managed identity either during the virtual machine creation or on an existing VM.

During VM Creation#

On the Management tab, you can enable the System Assigned Managed Identity.

On an Existing VM#

  • Navigate to your created Virtual Machine in the Azure Portal.

  • In the VM’s settings menu on the left, select Identity.

  • Select the System assigned tab.

  • Change the Status to On.

  • Click Save.

    Enable system-assigned managed identity screenshot

Assign Permissions via PowerShell#

Assigning API permissions directly through the portal is not possible for this type of identity, as the option is greyed out. Therefore, you must use PowerShell scripts to grant the necessary permissions.

Before running the scripts:

  • Ensure you connect to Mg-Graph in PowerShell.

  • In the following scripts, replace the placeholder test-mi with the name of your Virtual Machine.

Script 1 to assign the ``Sites.Read.All`` permission

$graphSp = Get-MgServicePrincipal -Filter "Appid eq '00000003-0000-0ff1-ce00-000000000000'"
$permissionsToAssign = @(
    "Sites.Read.All"
)
# Replace 'test-mi' with your VM name
$miSp = Get-MgServicePrincipal -Filter "DisplayName eq 'test-mi'"
foreach ($permName in $permissionsToAssign) {
    $appRole = $graphSp.AppRoles | Where-Object { $_.Value -eq $permName -and $_.AllowedMemberTypes -contains "Application" }
    if ($appRole) {
        New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $miSp.Id -PrincipalId $miSp.Id -ResourceId $graphSp.Id -AppRoleId $appRole.Id
        Write-Host "Assigned $permName"
    }
    else {
        Write-Warning "Permission $permName not found as an application permission."
    }
}

Script 2 to assign additional permissions

$graphSp = Get-MgServicePrincipal -Filter "Appid eq '00000003-0000-0000-0000-000000000000'"
$permissionsToAssign = @(
    "Files.Read.All",
    "GroupMember.Read.All",
    "Sites.Read.All",
    "User.ReadBasic.All",
    "Group.Read.All",
    "Directory.Read.All"
)
# Replace 'test-mi' with your VM name
$miSp = Get-MgServicePrincipal -Filter "DisplayName eq 'test-mi'"
foreach ($permName in $permissionsToAssign) {
    $appRole = $graphSp.AppRoles | Where-Object { $_.Value -eq $permName -and $_.AllowedMemberTypes -contains "Application"}
    if ($appRole) {
        New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $miSp.Id -PrincipalId $miSp.Id -ResourceId $graphSp.Id -AppRoleId $appRole.Id
        Write-Host "Assigned $permName"
    }
    else {
        Write-Warning "Permission $permName not found as an application permission."
    }
}

After running these scripts, your VM Managed Identity has the necessary permissions to access SharePoint.

VM managed identity permissions assigned screenshot

Install Squirro on Azure VM#

The next step is to install Squirro on the Azure VM you have created. Installation instructions can be found in the Install and Manage Squirro with Ansible page.

ACL Configuration via Microsoft Entra ID#

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

It 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 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 Squirro ID.

How to Set Up#

To configure permissions, follow the following steps:

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 Microsoft Entra ID (ME-ID).

  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 %}) OR NOT source_type:"Microsoft SharePoint"
    

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