How to connect MongoDB Cloud with SharePoint

This documentation describes how to set up your Layer2 Cloud Connector to synchronize data stored in MongoDB with a SharePoint online list in Office 365. Please find a similiar solution to integrate a local MongoDB installation here.

Contents

1. Configuring MongoDB

2. Configuring the Layer2 Cloud Connector

3. Hints and known issues

1. Configuring MongoDB Cloud

Go to your MongoDB cloud account https://cloud.mongodb.com and select your Stitch App. If you don’t have one, you must create it first.

Activate the anonymous authentication from your application.

Go back to Clusters/Security and create a new MongoDB user and add your machine local IP-address into the whitelisted IPs.

Create a collection for your Stitch application if you do not have one already.

Click on Clusters/Connect to get your connection string.

The IP-address from your local machine and a MongoDB user must be set.

Click on Connect Your Application.

Copy the connection string with the parameters. We are going to need them in the Layer2 Cloud Connector.

2. Configuring the Layer2 Cloud Connector

2.1. Creating a new connection

We are going so synchronize our MongoDB to a custom SharePoint list.

Create a new connection in the Layer2 Cloud Connector, name it and set the direction to Left to Right. 

2.2. Configuring the start system

Select the provider for MongoDB.

Select the source system and name it.

Set the connection string with the parameters from your MongoDB app.

The parameters from our cluster application are:

mongodb://Layer2MongoUser:[email protected]:27017,layer2cloudconnector-shard-00-01-jyrrg.mongodb.net:27017,layer2cloudconnector-shard-00-02-jyrrg.mongodb.net:27017/test?ssl=true&replicaSet=Layer2CloudConnector-shard-0&authSource=admin&retryWrites=true

The database name is the name from your MongoDB Collection.

Connection String:

Server=layer2cloudconnector-shard-00-00-jyrrg.mongodb.net;
Port=27017;
ReplicaSet=layer2cloudconnector-shard-00-01-jyrrg.mongodb.net:27017,layer2cloudconnector-shard-00-02-jyrrg.mongodb.net:27017;
AuthDatabase=admin;
Database=Layer2CC;
User=Layer2MongoUser;
UseSSL=true;

And your admin password.

The Select statement:

SELECT * FROM blog.comments;

Set your primary key too. 

Preview your data to make sure your connection to MongoDB is working.

2.3. Configuring the end system

We are going to send the data to a custom SharePoint list. Create the list first with the desired columns and copy the URL.

For more information about the SharePoint provider visit:

https://www.layer2solutions.com/support/cloud-connector-faqs/layer2-csom-sharepoint-ado-net-provider.

Connection string:

URL=https://your_custom_sharepoint_list_url/AllItems.aspx;Authentication=Microsoft_Modern;

If the fields from SharePoint are named identical to the fields from the source system, the auto-mapping function will work fine. We mapped our fields manually in this case.

2.4. Running your connection

You can now run your connection.

Data overview on the MongoDB entity.

And the transferred data to our custom SharePoint list.

3. Hints and known issues

3.1. Data model

To see all the tables and views you can access with the MongoDB provider, use these queries in the Select statement:

SELECT * FROM sys_tables

SELECT * FROM sys_views

If there are more tables or views available, you can load them with the option "load more" in the popup window.

3.2. Connection direction

As far as tested, the connection direction can be set to uni-directional as well as bi-directional. 

3.3. Batch operations

If you try to make multiple operations at the same time, you can get an error that batch operations not allowed. Set the parameter Other=’BatchSize=1’ in your connection string to avoid this error. 

↑Top