Consuming On-Prem SQL Data from SharePoint 2013 Online (Office 365)

Question

At a high level, What's the recommended Development model if we want to consume local (On-Prem) SQL data from Office 365 SharePoint 2013 Cloud application?

Say we have a custom SharePoint List Form and we want a dropdownlist with data from a table in a database down in SQL On Premisis?

Presuming OAuth is the way to go. Can the authentication server be ACS on the cloud?

1. What do we need to develop and configure On Prem and in SQL? Presume we have a SQL view ready.

2. Can the consumer (and only code in SharePoint) be just JQuery?

Thanks.

Answer 1

If you have SharePoint in the Cloud (SharePoint Online/Office 365) and your data is in-house, here's one (supported) way you could do it:

Surface your data from SQL in a web service you host on-site. Access this via JavaScript/client script code running in a SharePoint App.

Create a Provider hosted SharePoint App, where your on-site hardware is the 'provider', and you can grab the access to the SQL data as if it were local. This is where OAuth comes in - as SharePoint Online directs to your Provider Hosted infrastructure (on your premises), and it can talk back to SharePoint by authenticating using OAuth.

Configuring IIS for Provider Hosted apps is quite a tedious process (from what I gathered), but Eric Schupps went through at the SharePoint Conference 2012, I think this is the right post, but if not, find his other stuff from SPC12.

http://www.binarywave.com/blogs/eshupps/Lists/Posts/Post.aspx?ID=267