0

We have some SQL view on-promise server, we would like to display this data on the SharePoint online.

Normally once we need to connect to this database from different network, VPN connection is required.

I'm looking for any guide to resolve this, read some articles about this issue.

some of these articles mentioned app development with visual studio. However, how about the VPN connection.

What I have currently

SharePoint online E3 2013

SQL server database on-promise

My Skills

I have some programming skills with Web development, as I have noted that Sharpoint 2013 is highly developed with .NET WEB FORM as well as it not as any normal web XSLT is heavily used in SharPoint 2013.

What is the possibility to connect on-promise Database to sharePoint online, while database server required VPN?

0

If you just need to display it, PowerBi works well for this. PowerBi is in the cloud, but there is "gateway" software you can download which enabled you to use on-prem data sources for your cloud-based reports. Once a report is built, you can optionally put it on a SharePoint page. Note, both the use of the gateway as well as putting it on a SharePoint page will require all users viewing the report to have a PowerBI license.

If you need to interact with the data at all, you could use PowerApps. Same idea: PowerApps can connect via the gateway.

Or, create an excel spreadsheet that consumes the data, and publish that. This would require someone to open the workbook, refresh, and re-save.

Or, write a script or use SSIS to push the data to Azure SQL, and then use PowerBI from there.

Or, write a code-based REST service that is published and available outside the firewall, and write a SPFx web part that uses the service.

There are certainly other code-based strategies, though PowerBI will be the simplest route, and doesn't require custom development. (Well, excel is the simplest route, though I don't like the idea of depending on a manual refresh).

  • I would exclude power Bi form the option list – Mustafa Alqanbar May 1 '18 at 13:38
  • E3 should inlude the use of the gateway, so you should be covered (powerapps.microsoft.com/en-us/pricing). Though, it's a different tool. it's designed to allow users to add/edit records, not display charts and such. – Mike2500 May 1 '18 at 13:46
  • How about custom app development with ASP.NET – Mustafa Alqanbar May 1 '18 at 13:50
  • Sure. Sky's the limit. As mentioned, lots of options. Pro is no licensing costs per users. Con is custom code to build and maintain. – Mike2500 May 1 '18 at 13:53
  • One option: create a web.api that is secured via azure ad. This web api will be available externally, aka no vpn. (it will not be available to anonymous users, again, it will be secured via azure ad). Then, create a web part (spfx) that uses the web api to get data. Since users will be logged into O365, which uses azure ad, users will be able to view data from the web api via their existing credentials, aka no need to manage users/passwords, etc, as that's all taken care of by azure ad. – Mike2500 May 1 '18 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.