This year Microsoft released Azure Hybrid Connections, and significantly simplified the set up process of hybrid cloud solutions. Before, surfacing surface data from an on premise SQL server was an arduous task. It may have required developing data migration solutions, mirroring table structures on Azure, and opening firewall ports. Contrast this to the new process: setting up the Azure Hybrid Connection in the Azure Portal, and that only takes a few minutes, then just install Azure Hybrid Connection Manager on premise. And you’re good to go.
All communication from Azure Hybrid Connection Manager is securely transferred via outbound ports, and no inbound ports need to be opened in the firewall. Multiple web apps can share a single Azure Hybrid Connection. The connection is framework agnostic and can be accessed by any conventional web app framework (.Net, PHP, Node.js, etc.).
Azure Hybrid Connections can be used for any cloud solution that makes use of on premise data. My experiences were with setting up an External Content Type for SharePoint (detailed HERE). With your on premise data available on Azure, a number of applications can make use of the data. Some examples might include a custom Office 365 / SharePoint Online add-in, business intelligence solutions, or any other line of business application, so long as it can authenticate to Azure (or you can alternatively write your own authentication).
Hybrid On Premise Data in SharePoint Online with Azure Hybrid Connection
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.
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 (detailed HERE), 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.