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:
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:
There you have it, give it a try! Go Azure, Go O365, Go Hybrid!