0

I have a couple of Excel macro based programs I've developed for clients where I use Access as a backend database, but want to move to an Azure SQL server for numerous reasons. I've setup the Azure SQL server and connected Excel, however it dawned on me that since Azure requires each IP address be added to the firewall this won't work if a user wants to access it from outside their office.

After hours of research it looks like the best option (other than running an Azure SQL VM) is to create a WCF or REST web service between the two objects, however I'm not too familiar with this and need a little help. So a couple of questions here:

1) Am I thinking about this the right way?

2) Can this be hosted on Azure as well?

3) How do I go about doing this? Even pointing me in the right direction would be helpful

4) With some HTML/CSS and Python experience, and a lot of VBA and R experience, how hard is this going to be to pull off?

Community
  • 1
  • 1
pheeper
  • 1,457
  • 4
  • 20
  • 37

1 Answers1

1

Yes. It is not a very best practice to give your customer or client direct access to the database. The broker like a REST service is the way to do that, yes.

The simplest way would be to use Azure Mobile Apps as it is the way to have that kind of a broker that will be automatically created and set up to access your database in SQL Azure. You may see the description of the workflow when using Mobile App in my SO answer here. Basically:

1) You create Mobile App on Azure dashboard

2) You create the data connection to your SQL Azure

3) You create Easy Tables for Mobile App

4) Now, you have the simple API like GET/POST/etc, and every operation is connected to the table in the SQL Azure. For example, GET can be used for retrieve the data, POST for insert, etc.

The best way to understand what Mobile Apps are is to use the official documentation hub.

Regarding "how hard" - for your scenario, it should be very easy as most of the operations are performed on the portal, and for the client you need to provide the application that will invoke REST actions and show the result. All of these steps are described on the link above, and, as it is the REST API, you may use that from anywhere with corresponding capabilities, Python included.

Community
  • 1
  • 1
Alex Belotserkovskiy
  • 4,012
  • 1
  • 13
  • 10
  • Thanks for your response, I'll look at the links you posted. – pheeper May 16 '16 at 00:12
  • I was able to quickly create the Mobile App as outlined above, but am spinning my wheels on how connect it using Excel VBA. Any suggestions or links that might help clarify this? – pheeper May 16 '16 at 21:14
  • Maybe helpful http://stackoverflow.com/questions/19553476/how-to-make-rest-call-with-vba-in-excel ? Did not do that with VBA. – Alex Belotserkovskiy May 17 '16 at 08:43