Can local SQL Server data be made available in SharePoint Online

Question

We have a SharePoint Online account and local data in a SQL Server instance. The data from the SQL Server is available as an OData feed in SharePoint and can be worked with in Excel Power Query.

We had hoped to use an angularjs UI, hosted in SharePoint Online, to provide some extra functionality on top of that data, but we are running into problems when trying to access the data "directly". I'm not sure if the cause is the authentication, the OData feed functionality or the lack of SharePoint Online capabilities.

Hence the following questions:

• Is the data in a Power BI OData feed accessible to a SharePoint hosted, client side UI?
• Does the SharePoint REST API provide access to a Power BI Datasource in some way?
• Or do we need to (and can we) use BCS to create an External Data list, synchronised with a local SQL Server instance.
• Are there specific subscription requirements to make this possible (e.g. if BCS is needed, that requires a plan 2 subscription, I think?)

Or, to summarize:

• What is a good way to provide a simple UI that reads and updates some on-premise data and is fully integrated with SharePoint Online (preferrably using REST + client-side Javacript)?

BTW, according to this page, including on-premise SQL in SharePoint Online is impossible: http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/make-an-external-list-from-a-sql-azure-table-with-business-connectivity-services-and-secure-store-HA102933008.aspx

"NOTE: You cannot use on-premise data sources, such as .NET Type,
with SharePoint Online.
In addition, you cannot use a SQL Server data source that is on-premise
with SharePoint Online."

Answer 1

Having worked with & built similar stacks for extending/integrating SharePoint with external LOB systems, I can say our approach has been to attack it from the other end.

That is, rather than trying to use the BCS to connect to an on-prem DB, we'd create a RESTful WCF service that ran on-prem alongside the DB (could be OData as well if based off a defined entity model) and then use the Azure Service Bus Relay to provide the integration between the O365 sites and the back-end Service.

Once you do that, you can create a Provider-hosted app for use within O365 that provides a fully-integrated experience (i.e. full CRUD) against your on-prem DB.

Here's a good article, with diagram, describing the architecture: http://blog.pointbeyond.com/2012/11/07/hybrid-on-premise-office-365-solutions-with-azure-in-sharepoint-2013/

Answer 2

Now it is posible, for use in Power BI at least.

Use Data Management Gateway.

You install it on premisses on a location with Access to the internet and to your database, set the security info on your SharePoint, and it automatically redirects your queries to your on premisses site, compresses the result, and uploads and updates the info on your power bi site.

Answer 3

Yes,

from my experience the best approach is synchronizing local SQL data with native SharePoint Online lists, bi-directional if required.

In this way you have all well-known native SharePoint list features available without any restrictions, e.g. views, search, change notifications or workflows on item Level etc. The sync approach results in a very stable architecture with high performance and security - as users are working with a synced copy or "cache" of the data in the cloud only. It also works above the list view threshold. You will find a no-code step-by-step intro based on a 2rd party tool @ YouTube: http://www.youtube.com/watch?v=z2wpSGm_raQ