Recently I was asked by a customer if there is a way to allow SharePoint Online users to connect to an on-premises SQL database without using BCS and avoid the overhead configuring that. As a fan of SharePoint Apps and Azure the recommendation was to implement and leverage a provider hosted app on Azure, as for data access three options could be considered:
- Port the required databases to Azure SQL and sync that with On-premises SQL
- Configure Site to Site VNET to connect the app directly to on-premises SQL instance
- Leverage the new Azure Hybrid Connection
The rest of this post covers the required configuration and steps to implement the solution using Azure Hybrid Connection.
First I used Visual Studio 2013 to create a sample SharePoint provider hosted app called AlimazMVC using OOB project template, next I added a new MVC Model connecting to my on-premises SQL using Entity Framework along with required controller class and view elements:
Pretty straightforward! As expected VS did all the plumbing behind the scene and our sample SharePoint app was successfully launched from my localhost giving access to SQL database (Fabrics):
Next step was to publish the ASP.NET MVC app onto Azure website (I provisioned earlier) and also to add the SharePoint app into my Office 365 app catalog, as you can see I didn't change the connection string to any Azure SQL:
After deploying the solutions, I added the app to my team site and was able to navigate to provider hosted app:
As expected, selecting the product view gave me an error as web site could not establish a connection from Azure to my on-premises SQL instance:
The next couple of steps shows how to easily configure a Hybrid Connection via Azure portal to give our provider hosted app access to backend on-premises data:
- From Azure Portal, I selected the website (AlimazMVC). As you can see there was an option for Hybrid Connections:
- Following the wizard, I created a new connection pointing to local SQL server IP and port. We also need to create or use an existing Azure BizTalk service to create a connection:
- Next, I installed the connector using the zero touch client:
- After couple of seconds my hybrid connection was alive and connected!
- After a quick browser refresh my provider hosted app was all connected to back end and workings expected:
There you have it, give it a try! Go Azure, Go O365, Go Hybrid!