1

I am creating a database (in PostgreSQL, but this question is hopefully independent of the underlying DBMS) that will be hosted remotely. Most access will be provided through a web-based front end using Apache, but I would like to allow some users retrieve results from custom queries into Excel, SPSS, SigmaPlot, etc. Direct ODBC connections to databases appear to be supported by the likes of Excel through data connections. I would prefer all access to go through Apache over HTTPS, not least because I need to support special handling of credentials. Is there an established way to do this?

beldaz
  • 243
  • 2
  • 10

1 Answers1

3

No, not out of the box. Your clients are expecting to speak ODBC to the remote (database) server. Apache doesn't speak ODBC natively, so it can't reply properly.

If you have time to spare, you might be able to build an Apache module that does speak ODBC, but if you could, you wouldn't be asking the question here. Or alternatively you can build a front-end to your database that is based on HTTP like what AOL did.

Common approaches are:

  • allow your remote users to connect to your network using a VPN and then grant access to the database for VPN connected clients.
  • Grant SSH access and use SSH port forwarding

Or install one of the many browser based database management tools: https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

HBruijn
  • 77,029
  • 24
  • 135
  • 201
  • Thanks. I was hoping there was some sort of ODBC bridge for Apache, but no luck. I think your SSH option may be the best bet. I might also just be better off restricting support to things like Excel web queries. – beldaz Jul 17 '14 at 00:11