2 Replies Latest reply on Mar 6, 2013 12:12 PM by Tridus

    ODBC Connection over Internet

    Scott H.
      I have a requirement for ODBC access for a remote work station (Database 11G hosted, ODBC connection for integration to UPS World Ship)

      Obviously, I don't want to open Port 1521 out to the internet, and creating a VPN is not an option.

      Does anyone know if there is a way to use a web service or similar to create an ODBC DSN?

        • 1. Re: ODBC Connection over Internet
          No, you can't use a web service for an ODBC connection. Even if you could, though, what would that really buy you? If you want ODBC access to a database, that means that you want the ability to connect to the database and issue arbitrary SQL statements. Even if you put a web service layer in the middle, all it would be doing is proxying the requests and responses to the database. The majority of the risks of opening the database up to the internet would be present since random attackers could now pound away looking for weaknesses and, if they get through, they'd be able to execute arbitrary SQL statements.

          You can use Oracle Connection Manager as a connection proxy. That would still involve opening port 1521 (or whatever port you want Connection Manager to listen on) to the internet and it would be a pretty significant security concern. It's safer than exposing the listener up to the internet (particularly if the listener and the database are on the same server) and allows you to have only one port open (normally, although you connect to the listener on 1521, the listener then redirects you to a random port from a rather wide range to communicate with the database server).

          Are you sure that you can't build a web service that exposes whatever functionality you want and use that to integrate with the application rather than allowing the application to directly hit the database?

          • 2. Re: ODBC Connection over Internet
            No, you can't do this. Justin did a great job of explaining why it'd be a bad idea even if you could.

            What is it you're actually trying to do? Does this remote workstation need insert/update capability, or just select? If all they're doing is selecting, you could fake it by writing a program that connects to some web service you create, gets the data, and puts it into a local Access database. You could then create an ODBC connection to that.

            If you need updates... well, you need to rethink what you're doing, because the only real answer to using ODBC like this is "VPN".