Exposing SQL Data in SharePoint Online

For many years, users of SharePoint on-premises have enjoyed the ability to tap into their local on-premise SQL Server to expose tables and reports to their SharePoint site.  This was easy to use, as it only required creating a connection from the SharePoint Business Data Connectivity Service to the local SQL server. However, as organizations who have used this feature begin to migrate to Office 365 they are left with the dilemma of whether they need to seek alternatives or abandon this functionality all together.  Why you ask?  This is because without exposing your SQL server to the outside world, you cannot just hook directly into your SQL server any longer.

Why is this an issue with SharePoint Online?

To answer this question, one must first learn how the Business Connectivity Service (BCS) feature works on-premises.  Figure 1-1 below shows the typical connection between an on-premises SharePoint server and an on-premises SQL Server.

Figure 1-1

The flow of events works like this:

  • The User interacts with an External List
  • The external list communicates with SharePoint and determines what External Content Type is set up to support this list
  • The External Content Type has a Data Source tied to it that instructs the BCS on how to connect to, and the operations that are supported for, this External Content Type

Figure 1-2 shows the main reason why BCS connections to your local SQL servers from SharePoint Online are stopped in their tracks–namely, a company firewall.

Figure 1-2

Unless you plan on opening your on-premise SQL server to the outside world (not recommended), you will need to deal with your company’s firewall stopping you from connecting to your destination SQL server.

So what are my options?

Now that we have covered the main reason why this feature will break for most users during migration, we need to address what can be done about it.   Below I have listed some of the options, and the advantages and disadvantages to each.

Option 1: SQL Azure

Option 2: Custom WCF Service

Option 3: Azure Hybrid Connection

Option 4: 3rd Party Applications

How can Peters & Associates help?

We have been assisting organizations in solving business problems like these since 1981.  We can help you:

  • Identify your current Business Connectivity Service configurations
  • Help you understand the options available and which one closest aligns with your needs and budget
  • Help implement the decided upon solution
  • Provide training and assist in user adoption
  • Provide on-going technical support for your Microsoft applications

Need help to evaluate your work processes and determine what solutions can work for you?  Email us at info@peters.com.

By | 2017-11-20T12:03:49+00:00 December 1st, 2017|Collaboration|Comments Off on Exposing SQL Data in SharePoint Online

About the Author:

Duane Foote is a Senior Developer at Peters and Associates focused on helping clients with all their SharePoint and SQL Server related needs. He has spent over 17 years in the IT and Software fields having experience in a lot of different job roles. His background includes software development and maintenance, troubleshooting and support, SQL Server support and development, and all things SharePoint. Duane holds a Bachelor’s Degree in Computer Science from Western Illinois University. Duane’s main focus at Peters and Associates is on SharePoint roles such as Client Troubleshooting and Support, Upgrades and Migrations, Farm Reviews, Web Part and Solution Development, and Farm Installation and Configuration.