Examining SharePoint Data Integration

Examining SharePoint Data Integration

By Terry Engelstad
MCP, MCSE, CCNA, MCDBA, MCTS, MCITP
AIS Network Operations Manager

This week, we were contacted by a vendor for a new software product that claims to connect almost any on-premise data source (e.g., databases, ERP/CRM) to Microsoft SharePoint 2010 and Microsoft Office 365.

I’m not sure of the value of this to a SharePoint hosting provider per se. I’ll need to digest some different scenarios in order to understand how AISN would use this, but it gave me the idea for this blog on SharePoint data integration. Here’s a little background about data integration with Microsoft Office 365 (SharePoint), and probably, why this type of product evolved.

SharePoint MOSS 2007 introduced a new service called Business Connectivity Service (BCS). It allowed users to access external data in a variety of forms (databases, spreadsheets, files, etc.) from inside SharePoint. SharePoint 2010 renamed it “Business Data Connectivity” (BDC) and embellished the feature-set, including the ability to Search the external data. BCS and BDC do not copy data into SharePoint. They set up virtual connections to the data and allow other components within SharePoint to see and touch the data. At no point is data copied into SharePoint. Connections to external data are not persistent. This means they will exist for as long as necessary, then dissolve, then re-connect as necessary. This is not too efficient for large volumes of data.

When a connection is set up to an external source, there is a requirement to provide credentials in order to access the external data. SharePoint allows only three ways to provide credentials:

  1. the Windows account of the logged on user running the SharePoint BCS/BDC process,
  2. the operating system account of the service running the BCS/BDC process, or
  3. a customized set of credentials.

MOSS only allows types 1 or 2. On the other hand, SharePoint Foundation 2010 only allows types 1 or 2, and SharePoint Server 2010 allows all three types but needs to retain credentials for type 3 in a service called Secure Store. This service is available only in SharePoint Server – not MOSS and not Foundation.

Office 365 is built on the foundation of SharePoint Foundation (and so is our Shared SharePoint environment). Microsoft has stated that Office 365 does not allow accessing external data through BDC. I haven’t seen a document stating the reasons for this, but I can draw conclusions from the statements above.

First, since data is not actually copied into Office 365 SharePoint, there is no consumption of space, only consumption of network bandwidth and CPU. Since the pricing model for Office 365 is built around space consumption, Microsoft would not make any money if clients used, for example, only external data. Picture a SharePoint environment where there is no data stored locally, only accessed through BCS/BDC. Not too profitable.

Second, Office 365 does not allow access to external data because of credentials. In Office 365, Windows accounts and Operating System service accounts will have no meaning outside of Office 365. Therefore, a user could not authenticate with those credentials to any data source outside of Office 365. And again, since Office365 does not use the Secure Store feature found in Server, special/unique credentials cannot be stored.

So, long story short, there is still a need to get data into Office 365. The method proposed by this new software is to copy data from external data sources into SharePoint Lists. This is an OK way of getting data into SharePoint. I’m just not sure how many people would want to take advantage of it. We’ve had only one client in our Shared environment ask about BDC capabilities, and my research/response to that client has led to the above discovery. We do have one MOSS client using BCS to access several spreadsheets which they periodically refresh on their dedicated server. None of our dedicated SharePoint 2010 clients are currently using BDC.

I’d be interested in any comments that readers of this blog may have. What are your thoughts?

Leave a Comment