Step-by-step deployment guide

This article describes how to use the SQL gateway in order to import Sharepoint list data into Elimity Insights.

1. Intro

The SharePoint Lists integration does not have its own gateway. Instead, it uses the SQL gateway with the DuckDB driver and the ERPL web extension to read SharePoint lists and import them into Elimity Insights. The DuckDB driver requires SQL gateway v4.0.0 or later, which is compatible with Elimity Insights server versions >= 3.46.0.

To set it up, follow the steps below.

2. Creating a dedicated app registration in Entra ID

As usual we recommend creating a dedicated app registration for this connector.

1. In Entra, open your tenant's app registrations by typing 'app registrations' in the global search bar and clicking the App registrations service.

2. Click New registration.

3. Choose a name for this registration (e.g. elimity-insights-sharepoint-lists).

4. Leave the defaults for 'Supported account types' and 'Redirect URI' and click Register.

Note down both the client identifier and the tenant identifier.

3. Generating credentials for the new app registration

The connector authenticates as the app registration using a client secret. Click Certificates & secrets in the menu on the left and add a new client secret. Immediately note down the value (Entra shows it only once).

4. Granting read permissions to the Graph API

Click API permissions and add this Microsoft Graph → Application permission, then grant admin consent:

• Sites.Read.All — read site data

5. Configuring the gateway

At this point you should follow the installation note and the Configuring the gateway section of the SQL gateway docs, keeping in mind the following:

• Sharepoint Lists needs some SQL queries to run before data can be retrieved. To do this, add the following property to the SQL gateway's JSON configuration file, filling in the tenant identifier, client identifier and client secret you obtained in steps 1 and 2.

• Also in the JSON configuration file, set the driver field to "duckDb" and the connectionStrings field to [":memory:"] . Example below:

{
  "connectionStrings": [":memory:"],
  "driver": "duckDb",
  "setupQueries": [
    "INSTALL erpl_web FROM community;",
    "LOAD erpl_web;",
    "CREATE TEMPORARY SECRET ms_graph (TYPE microsoft_graph, tenant_id    'SHAREPOINT_TENANT_ID', client_id 'SHAREPOINT_CLIENT_ID', client_secret 'SHAREPOINT_CLIENT_SECRET');"
  ],
  "jwtValidationBaseUrl": "https://example.elimity.com",
  "jwtValidationGatewayUrl": "https://gateway.example.com",
  "jwtValidationSourceId": "42"
}

You can now follow the rest of the SQL gateway deployment guide.

6. Querying Entity types and Relationship types from Sharepoint lists

Instead of reading from a table, you read from a list with:

graph_sharepoint_list_read(
  'site-name',
  'list-name',
  secret := 'ms_graph'
)

The last argument references the secret defined in setupQueries by name.

6.1 Entity Type query examples

This is an example which includes different queries for importing different entity types. Take note of the specific query format and attributes as described here.

  "entityTypes": [
    {
      "attributes": [
        {
          "description": "",
          "id": "description",
          "name": "Description",
          "type": "string"
        }
      ],
      "icon": "label",
      "id": "businessRole",
      "plural": "Business Roles",
      "query": '''
        SELECT Code, Title, Description
        FROM graph_sharepoint_list_read(
          'https://4s0pkg.sharepoint.com',
          'BusinessRoles',
          secret := 'ms_graph'
        )
      ''',
      "singular": "Business Role"
    },
    {
      "attributes": [
        {
          "description": "",
          "id": "system",
          "name": "System",
          "type": "string"
        },
        {
          "description": "",
          "id": "businessRoleCode",
          "name": "Business Role Code",
          "type": "string"
        }
      ],
      "icon": "label",
      "id": "technicalRole",
      "plural": "Technical Roles",
      "query": '''
        SELECT Code, Title, System, BusinessRoleCode
        FROM graph_sharepoint_list_read(
          'https://4s0pkg.sharepoint.com',
          'TechnicalRoles',
          secret := 'ms_graph'
        )
      ''',
      "singular": "Technical Role"
    },
    {
      "attributes": [
        {
          "description": "",
          "id": "email",
          "name": "Email",
          "type": "string"
        },
        {
          "description": "",
          "id": "department",
          "name": "Department",
          "type": "string"
        },
        {
          "description": "",
          "id": "businessRoleCode",
          "name": "Business Role Code",
          "type": "string"
        },
        {
          "description": "",
          "id": "technicalRoleCode",
          "name": "Technical Role Code",
          "type": "string"
        }
      ],
      "icon": "person",
      "id": "user",
      "plural": "Users",
      "query": '''
        SELECT UserId, Title, Email, Department, BusinessRoleCode, TechnicalRoleCode
        FROM graph_sharepoint_list_read(
          'https://4s0pkg.sharepoint.com',
          'Users',
          secret := 'ms_graph'
        )
      ''',
      "singular": "User"
    }
  ]

6.2 Relationship Type query examples

This is an example which includes different queries for importing different relationship types. Take note of the specific query format and attributes as described here.

"relationshipTypes": [
        {
            "attributes": [],
            "from": "user",
            "query": '''
            SELECT
            UserId,
            BusinessRoleCode
            FROM graph_sharepoint_list_read(
            'https://4s0pkg.sharepoint.com',
            'Users',
            secret := 'ms_graph'
            )
            WHERE BusinessRoleCode IS NOT NULL
            ''',
            "to": "businessRole"
        },
        {
            "attributes": [],
            "from": "user",
            "query": '''
            SELECT
            UserId,
            TechnicalRoleCode
            FROM graph_sharepoint_list_read(
            'https://4s0pkg.sharepoint.com',
            'Users',
            secret := 'ms_graph'
            )
            WHERE TechnicalRoleCode IS NOT NULL
            ''',
            "to": "technicalRole"
        }
  ]
}