Layer2 Cloud Connector  V5 for Office 365 and SharePoint

​The Layer2 Cloud Connector can be used to connect almost any external data source to Microsoft Exchange, on-premises or Exchange Online & Office 365. This page gives the full specifications of the Layer2 Data Provider for Exchange which is included in the Cloud Connector product starting with version 5.

Layer2 Data Provider for Exchange - Specifications

​The Layer2 Data Provider for Exchange can connect and sync Microsoft Exchange contacts, tasks, calendar items, notes, and emails from private or shared mailboxes, and public folders, uni-directional and bi-directional with almost any other data source or system, including native SharePoint SharePoint lists and libraries - codeless.

Microsoft Exchange Integration and Synchronization with SQL, ERP/CRM, SharePoint etc. 
 

You simply have to:

  • Start the Layer2 Cloud Connector Connection Manager to configure data source, data destination, field mapping, and sync options.
  • Select the Layer2 Data Provider for Exchange.
  • Enter a valid connection string with URL, user, password etc.
  • Enter an Exchange data query like select * from contacts.
  • Run first sync manually and configure automatic background sync if all works as expected.

You will find further specifications below.

Layer2 Data Provider for Microsoft Exchange: Connection String

​The connection is established directly to the Exchange Server, on-premises or in the cloud, not via Outlook. Exchange 2007 SP1, 2010, 2013, and Online is supported.

Sample Connection Strings:

Url=https://outlook.office365.com; [email protected]; NamingScheme=SharePoint; Password=myPassword

Url=https://myExchange; [email protected]; NamingScheme=SharePoint; Password=myPassword
 
Url=https://mail.mydomain.com; [email protected]; NamingScheme=SharePoint; Password=myPassword
 
[email protected]; [email protected]; NamingScheme=SharePoint; Password=myPassword
 
Url=https://192.100.100.1; [email protected];; NamingScheme=SharePoint; Password=myPassword
 
[Specifically for Site Mailbox Access]
[email protected]; Password=myPassword; email=userOfSiteMailBox; NamingScheme=SharePoint;

Specification

Url (required):

Link to the Exchange Web Services Interface. Please ask your administrator or use the autodiscover feature by giving an email adress only: [email protected] Please note that SSL / HTTPS required appropiate certificates on the client system.

Username (required):

Email adress ([email protected]), user name (myUser) possibly including domain (myDomain\myUser). The service inpersonates to this user to get access to the requested folders or items. Please make sure that this user has read access minimum to retrieve data.

Password (required):

Password of the given user. Please note that the configuration file can be encrypted for security reasons and access to the directory can be limited by Windows file system access rights.

Authentication (optional):

In case user name and password is used instead of an email address, Authentication=Windows is required.

PreferredCulture (optional):

You can prefer a defined culture, e.g. en-US. If not given "Invariant" is used to make a best fit approach.
Possible values are typical Microsoft culture notations like de-de, de-ch etc.
 
NamingScheme (optional):
 

Optional parameter to perform optimizations for certain target systems. For example in case of NamingScheme=SharePoint the column names automatically fit to SharePoint contact, tasks or event lists.
Auto-mapping can be used to map the data source fields to the data destination columns by name. By default no optimization is done (means original field names are used). You can also provide some optimization in the query statement using aliases (see below).

Email (optional):

Defines which mailbox should be accessed. If not specified, it will use the mailbox of the user given under the Username parameter. If the Email parameter is specified, the user given in the Username parameter must have permissions to impersonate the mailbox account.  You may need to set Impersonation=true;

Impersonation (optional):

When using the "Email" parameter to access a different mailbox, you can either use impersonation (Impersonation=true;) or delegation (Impersonation=false;). This is set to false by default. 

Please verify the connection before continue with the data query.​

Recursive (optional):

Defines if the provider should traverse through all subfolders to gather the items (Recursive=True;) or only return items which are direct children of the specified folder (Recursive=False;). This is set to false by default.

Layer2 Data Provider for Microsoft Exchange: Data Query (SQL/AQS)

​The data query retrieves the desired data set from SharePoint. A SQL (Structured Query Language) like syntax is supported.

Sample data query (returns contacts):

select Title, FirstName, FullName, Email, Company,  JobTitle, WorkPhone, HomePhone, CellPhone, WorkFax, WorkAddress, WorkCity, WorkState, WorkZip, WorkCountry, Comments,  exchangeid from Contacts

Specification:

SELECT myFields FROM myScope WHERE MyCondition

myFields can be a list of fields like "Firstname, Fullname". * is supported for all fields. For performance reasons it is advised to use a list of fields. Please include required fields only. You can find out available field names using the data preview or the field mapping dialogue. You can use aliases to rename fields to better fit the target system, e.g. select Lastname as Title.

The amount of data can be limited with a TOP clause, e.g. SELECT TOP 500 * FROM myScope. By default the number of items is limited to 1000. Please use e.g. TOP 2000 to increase. Best to use "Top 10" for testing. 

myScope can be a valid path to any Exchange folder, e.g. Contacts. Please note that default folder names are localized (e.g. Kontakte in German). To access public folders use something like this:

Select * From PublicFoldersRoot\ExchangeTest\Kontakt

You can also access mailboxes from other users (if you have access) via

SELECT * from [email protected]/contacts.

You can use the full path to the folder, e.g. SELECT * from "Root\Top of Information Store\my contacts 1", for more complex folder names.

MyCondition can be a valid expression in terms of AQS (Advanced Query Syntax). You will find more information here

https://msdn.microsoft.com/en-us/library/ee693615(v=exchg.150).aspx

and

here:

https://msdn.microsoft.com/en-us/library/office/dn579420(v=exchg.150).aspx

Samples:

SELECT * FROM contacts WHERE Body:progress

SELECT * FROM calendar WHERE category:customer

This first sample e.g. retrieves contacts only with the token "progress" in the notes / body field (not case sensitive). You can also search by categories as shown in the 2nd sample.

Please verify your query and display preview data before continue with mapping and synchronization.

Layer2 Data Provider for Microsoft Exchange: Primary Key(s)

No primary key must be given in Layer2 Cloud Connector. The Exchange Item-ID is set automatically. For the Layer2 Business Data List Connector please set the primary key to ID and add an additional text column to host the Exchange Item-ID. You can use bdlcID as column name to automatically map with ID. You can use any other name, e.g. ExchangeID to map manually to ID via custom mapping.

Cloud Connector and Business Data List Connector Sample Configurations

​An example configuration of a Microsoft Exchange data source in the Layer2 Cloud Connector can look like this:

 
 
Fig. Example configuration to connect to Microsoft Exchange data (e.g. contacts) with SharePoint using the Layer2 Cloud Connector. 
 
For the Layer2 Business Data List Connector it should look like this:
 
Exchange-SharePoint-Contact-Sync-Layer2-BDLC.png
 
Fig. Example configuration to connect a SharePoint contact list with Exchange via the Layer2 Business Data List Connector.
 
Method to Access a Site Mailbox:
The connection string must contain the following attributes:
  • User - A User with access to the site mailbox.
  • Password - Password of the User.
  • Email - Email address shown as in URL of the site mailbox, typically something like "SMO-<sitename>@tenant.onmicrosoft.com".
  • NamingScheme - Set as "Sharepoint", which allows it to automatically fit the column names from Exchange to match those found in SharePoint contacts, tasks, or event lists.

In addition, the Select Statement should also be set to contain a where statement that limits the emails returned to the unread ones, like the following:

SELECT * FROM Inbox WHERE IsRead:false

Site_Mailbox_Exchange_Example.png 

 

 

 

Fig. Example configuration to connect to Microsoft Exchange data from a Site Mailbox (e.g. Inbox) with SharePoint using the Layer2 Cloud Connector. 

Layer2 Data Provider for Exchange: Use Cases and Known Issues

Use Cases:

  • Provide Microsoft Exchange data synced with a native SharePoint lists, e.g. contacts. Sync other item types as tasks, calendar data, notes.
  • All list features supported, e.g. views, filter, grouping, search, calculated fields, lookups.
  • Metadata and social tagging supported, as well as attachments.
  • Change notifications and workflows on source data changes supported.
  • Mobile access via SharePoint.
  • Offline availability and sync with Outlook via SharePoint.
  • Sync with almost any other corporate data sources: Virtually any data sources supported likeODBC, OLEDB, Microsoft .NET based providers, Files (Excel, XML, CSV), RSS / XML feeds, SQL databases like MS SQL, SQL Azure, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint (lists and libraries), local file system, cloud-based file stores, Exchange, Active Directory, Dynamics NAV/CRM, Navision, SAP and many more. More complex data sources, web services, custom COM or .NET objects, can be connected using 3rd party add-ons.

Known limitations and issues:

  • ​Calendar items: Full-Day events are not managed correctly. Also regular events with exceptions.
  • Contact items: Outlook aggregates contact information from several sources and can merge it for display. A contact synchronization manages information only as stored in Exchange Server (not in Outlook).
  • Inbox items: Attachments are not managed.
  • Task items: Attachments are not managed. Dependencies are not managed.
  • Binary blobs are not managed, e.g. images.
  • Depending on configuration and data amount performance of first sync can be slowly.
  • Please format multi-line text fields as text only, not HTML/RTF. Otherwise it possibly leads to recurring field updates on each sync. Will be fixed with next versions by providing an additional Body.Plaintext field.
  • Sub-fields like CompleteName.GivenName, CompleteName.SurName are read-only. In some cases there are related fields available for write like GivenName, or SurName. Unfortunately for CompleteName.Title and CompleteName.Suffix no related fields are available at this time.
  • Wildcards are not supported in scopes.
  • Max item count is limited to 1000 items in Exchange by default. Please use e.g. Select Top 5000 from myScope to increase.
  • Documents in public folders are not synced.
  • Be careful with select *. In some cases large attachments can slow down performance and end up in timeouts.
Share this site on Xing
Share this site on Google+
Share this site on LinkedIn

I see the Layer2 Cloud Connector as a most beneficial product that we could definitely sell to our clients.

Contact
Frank Daske

Frank Daske

Business Development Manager
+49 (0) 40 28 41 12 - 30
[email protected]

Please note that the 011 exit code must be dialed first for all international calls made from the USA, Canada and Australia.

Related News

New Microsoft Teams Connectors: Layer2 adds 100+ typically used backend systems

​The Layer2 Cloud Connector is now able to connect to Microsoft Teams to quickly integrate more than 100 IT backend systems including local SQL, ERP/CRM.

Layer2 Office 365 and SharePoint integration now compatible to FIPS

The newest Layer2 Cloud Connector release is compatible to the Federal Information Processing Standards (FIPS) of the United States federal government regarding strong encryption.
Related Downloads
PDF Image

Cloud Connector Instruction

Download this step-by-step instruction to connect and sync your corporate or cloud data sources bi-directional to any native SharePoint 2010/2013/2016 list or library, including Microsoft Office 365.
PDF Image

Feasibility Study: Connecting local SQL Server to SharePoint Online

The study compares different approaches for SharePoint Online data integration regarding performance, features, security and investment.
PDF Image

Flyer: Layer2 Partner Program

Partner with us as a software distributer, reseller or SharePoint, Office 365 or Azure service provider. We are offering a free Partner Program with up to 30% Discount, free NFR licenses etc. Please download for more.
PDF Image

Layer2 Case Study: Cloud Connector helps ChemWerth to integrate the Microsoft Office 365 Cloud

"The Cloud Connector’s interface is easy to understand and use. Anyone familiar with utilities in the Microsoft stack will be productive immediately. Our recommendation: The Layer2 Cloud Connector is a no brainer.”
NoDoc Image

Layer2 Cloud Connector PAD File

This XML-based PAD file offers a Layer2 Cloud Connector product description for resellers to download and publish.
PDF Image

Office 365: How to sync with local SharePoint?

You can replicate selected parts of your SharePoint intranet portal to the Office 365 / SharePoint Online cloud.
PDF Image

Office 365: How to sync with MS Dynamics CRM

You can sync any CRM data with native lists in SharePoint on-prem or online to make it available offline with Outlook.
PDF Image

Office 365: How to sync with SAP?

You can connect SharePoint and Office 365 with SAP via NetWeaver and OData.
PDF Image

Office 365: How to sync your local file share?

When you are moving to the cloud, there are a lot of reasons to keep your local file system. But you don't have to miss the benefits of the cloud.
PDF Image

Product Flyer: Cloud Connector for Microsoft SharePoint & Office 365

Flyer with features, benefits & pricing info for download as PDF.
PDF Image

Product Flyer: SharePoint Online Local File Server Sync

You can centrally migrate, synchronize, or backup your local file server department shares and home drives very flexible with SharePoint Online to close known gaps and overcome limitations of the Microsoft OneDrive for Business client.
PDF Image

Solutions Flyer: Data Integration with SharePoint, Office 365 and 100+

FileMaker can be integrated with 100+ other sources codeless using the Layer2 Cloud Connector.
Related Links

Layer2 Cloud Connector Community Group @ LinkedIn

​Are you already @ LinkedIn? Please join the Cloud Connector Community Group to share your experience.

Microsoft Partner Story Book for Office 365 about Cloud Connector

​Know more about a Layer2 Cloud Connector customer reference at the Microsoft Office web site.

Purchase Layer2 Cloud Connector via ComponentSource

​ComponentSource has offices in the USA, UK and Japan supporting over 125,000+ Customers, in over 180 Countries. They are also an approved Federal Government supplier through the GSA Schedule.

 

cs-award-top-100-publisher-2014-15-large.gif

Sign Up for Layer2 Product Newsletter

We keep you up-to-date about new Layer2 product releases, added features and fixed issues.