Technology in the cloud is tremendously more fast moving than when compared to on premise solutions. The case of SharePoint hybrid solutions is a prime example. The current Microsoft-provided documentation itself seems to be a bit dated with a suggestion to set up an on premise installation of SharePoint to serve the on premise data to the cloud. Another popular (and less costly) approach involves syncing the data to Azure-hosted SQL tables with an Azure Hybrid Connection.
Thankfully the process of making on premise data available on the cloud seems to be on the way to become much more streamlined. With Azure Hybrid Connection Manager, on premise data can be made available to cloud-hosted solutions within a matter of minutes. With this connection made available, it can be surfaced to SharePoint via an Azure-hosted web service.
This can be a great path for setting up a hybrid connection to on premise data for a number of reasons. For starters, there is immediate access to live data made available to the cloud. Other alternative approaches require setting up periodically synchronized sources. Additionally, other approaches may be more error-prone, adding the requirement to maintain additional SQL structures in the cloud and ensuring that data is properly synced. And this approach is significantly less costly than Microsoft’s currently recommended approach of setting up an on premise SharePoint environment to sync data. Also because the data is made available as a web service, it could also later be served to other line of business systems, or custom Office 365 / SharePoint Online add-ins.
While the Azure Hybrid Connection aspect is well developed, there is still some ongoing friction involved when surfacing this data onto SharePoint. For starters, Microsoft has not yet provided a convenient way to authenticate to Azure’s default OAUTH model; this means that you would need to write your web service with a basic authentication model included. While this old school approach may deter some people with authentication being made with Base64 encoding, all cloud communication is secured over HTTPS and is trustworthy. The credentials for basic authentication could then be stored for internal use via the Secure Store of SharePoint Online.
An additional caveat is that in order to connect to Azure Hybrid Connection, an ODATA web service should be created. This presents a challenge because SharePoint Designer only supports WSDL/SOAP connections for web services when setting up new External Content Types. And because Microsoft has terminated any new development of SharePoint Designer, the External Content Type must be generated in Visual Studio instead. Visual Studio generates the BDCM file which can be imported as a BCS source for SharePoint Online. However, some modifications to the BDCM file (which is all XML) may be required, including for enabling basic authentication.
To date there is no truly excellent and pain free way to bridge on premise data to SharePoint Online. While Azure Hybrid Connection is indeed a tremendous help for making on premise data available to the cloud, SharePoint Online still has some work left for seamlessly integrating this source of External Data. At the rate in which Microsoft has been building the cloud and hybrid frameworks, easier and robust solutions will become available within coming years. For now, if you are implementing a hybrid connection and have the technical expertise on hand, building a web service that uses Azure Hybrid Connections would be my preferred approach.