It all began when our client made a strategic decision to move their SharePoint farm into the cloud in order to reduce internal hosting and administrative costs. The business needed SharePoint to interact with corporate SQL data on their internal network, without the addition of new on-premises servers or components.
Historically, SharePoint and corporate SQL Servers resided on the same internal network and accessing data outside of SharePoint was easily solved by using Business Connectivity Services (BCS). However, integrating internal SQL data with Office 365 / SharePoint Online traditionally requires a SharePoint hybrid farm, where cloud-based BCS runs as internal BCS by proxy. Unfortunately, this requires the organization to maintain an on-premises SharePoint farm and adds new servers for ADFS 2.0, DirSync, and related proxy servers required to execute a BCS hybrid farm. While technically possible, SharePoint hybrid farms are not for the faint of heart – deployment of the components needed to establish the required infrastructure is a not a trivial exercise and with the decision to move to the cloud, the thought of adding new servers in the on-premises data center was a non-starter.
The simplest way to solve the problem would be to expose the internal SQL Server to the internet with the appropriate security; however, even the mention of this approach was certain to invoke the ire and wrath of system admins. Alternatively, we could create an internet accessible web service on the internal network that connected to the corporate SQL Server, but this would again add more on-premises servers that need to be maintained. Using the Azure Service Bus relay would work, but it was a more complex solution and it too required hosting a new web service on the internal network.
New Solution: Azure Hybrid Connections
Fortunately, Microsoft provides another way to solve this problem by repackaging the Data Management Gateway component from the solution stack that allows Power BI to provide cloud-based visualizations of internal SQL data sources. Azure Hybrid Connections will allow you to build a bridge between cloud-based applications, including SharePoint Online, with read and write access to SQL Server data hosted on the internal corporate network. Using an Azure hosted web service, this solution requires no inbound firewall changes and is accomplished without needing to add any additional servers on the internal network. While Azure Hybrid Connections solve the connectivity aspect of the problem, creating the new web service on Azure with the OData protocol provides a variety of options for integrating with SharePoint Online and other cloud-based applications.
How to Build the Bridge
The starting point is making sure that the internal SQL Server meets the Azure Hybrid Connection prerequisites. The SQL Instance must be accessible via a static TCP/IP port – named instances managed by SQL Browser or dynamic TCP/IP ports will not be recognized by the Hybrid Connection Manager (HCM). Additionally, the database must use SQL authentication (mixed mode), as the solution does not have the capacity to validate users against a domain. The HCM needs to be installed on a server that can access the internal SQL Server – in most cases it can be installed directly on the SQL Server hosting the database that needs to be accessed. Finally, the server that runs the HCM needs to have outbound access to the internet on ports 80, 443, 5671, and 9352. Since outbound traffic is rarely regulated by the firewall, the solution should work without any security adjustments or additional exposure.
Create a new Biz Talk Service from the Azure portal (portal.azure.com)
The next step in the process is to access the Azure Management Portal (manage.windowsazure.com) from the server selected to host the Hybrid Connection Manager. You will need to set up a new Biz Talk service, which will in turn allow you to create a new hybrid connection. Simply fill in the same local credentials you established for SQL Server (host, port, id, and password) and then click the button to install the Hybrid Connection Manager locally. Shortly after the installation is complete, the status of the hybrid connection will show as Connected and be ready for use. At this time you can only associate hybrid connections with Azure Web Sites or Mobile services. Create a new one of these as needed and use its control panel to associate it with the new hybrid connection.
Create a hybrid connection using local connection values, then manage On-Premises Setup to install and configure the On-Premises Hybrid Connection Manager
With the secure connection established between the Azure cloud and the internal network, the next step is to deploy a web service available to cloud applications including SharePoint Online. An OData WCF Data Service will provide maximum flexibility as well as options for security that can integrate with the SharePoint Online environment. Develop this service in an environment local to the internal SQL Server with your choice of Visual Studio environments (2013 or 2012 with the Azure SDK), generate the required EDM (entity data model), and establish the appropriate access settings in the Data Service class. When you deploy this service to the Azure Web Site, the credentials from the local SQL connection string will match those registered with the hybrid connection managed in the BizTalk service. As a result, the web service published on Azure will be able to seamlessly interact with the internally hosted SQL Server.
With an Azure hosted OData web service, you will have a number of options for integrating it into your SharePoint Online environment. If you are building SharePoint hosted apps, you can use the cross-domain library to make service calls in jQuery or your desired technology to integrate the internal data into your front end code. Obviously, you can also consume the same OData web service in .Net if you are using the provider-hosted mode for your app. SharePoint 2013 BCS now supports OData connections; thus, you could also use the service to create external content types, leveraged within SharePoint Online without a hybrid farm.
While there is immense promise to be found in moving the enterprise to the cloud, the transition for organizations with complex needs has not been an easy one. Early solutions required you to maintain an on-premises farm for certain functions and the plumbing needed to expose these to cloud-based SharePoint proved to be cumbersome. Previously, the alternative was to try to move everything into the cloud and abandon the on-premises environment, but the task of moving all the SQL data and line-of-business applications proved just as difficult and in some cases constrained by cost, regulatory, and security concerns.
The new alternative is to leverage Azure Hybrid Connections. Use SharePoint Online to provide globally accessible collaboration functionality without a VPN and the costs of maintaining and administrating on-premises farms. Keep existing SQL databases hosted internally for the sake of continuity, security, and regulatory compliance. But, let Azure Hybrid Connections provide a simple, safe bridge between the two so you can get the best of both worlds.