Use something else than JDBC over firewall

Hi,

I have simple server-client application that uses JDBC to connect to database and things works ok. Application does few simple things with JDBC connection (get data, insert new line and few others).

Now, I would like to keep the same application but use it outside of firewall - so, I would put something else on some host:port (and open that port to outside world) - instead of JDBC opening database access directly.

I guess that this problem is faced many many times and sure there are a lot approches.
One way can be doing servlet on one side, accessing it on client side.
I guess, I haven't touched Spring yet, maybe another would be to do POJO Java Class and using Spring configure it as http service. I have heard also "rumors" that Jetty has something that can help in this case (to minimaze coding on server and client side)

I would prefer something that:
- is not complicate (easy learning path)
- reuse something that is already done.

What approach would you recommend ?

Thank you and regards,
Igor

From stackoverflow

• The normal approach would be to implement a web service, which can be pretty easy these days with Axis etc.

  You really don't want to open direct JDBC to clients outside a firewall by tunnelling over HTTP... the server should strictly control what kind of interaction takes place with the database.

  Jon Skeet : @Igor: You shouldn't create an API which leaves a locked row in the database. Try to make it "chunky" so that each request is somewhat independent. You never know when a client will just "go away" - at which point you don't want to leave a locked row.

• I would recommend using something like SSH tunnels to carry your JDBC connections through the firewall. Set up a tunnel on the DMZ machine on whatever publicly open port your can, and connect the other end of the tunnel to the appropriate port on the DB server.

  Then just change your JDBC connection settings to connect to the tunnel machine's public port and it will transparently end up communicating with the database as usual, while passing through the firewall via the accepted port.

  If this is an IT policy problem, in that they won't let you directly access the database, then you would need to work out what you are allowed to do and work with that as far as possible. Changing JDBC to another access method is unlikely to be acceptable to the IT policy in this case.

  Edit: after reading Jon's answer, he may be right. I was assuming that the issue was the connection between your server/webapp, and the database server. If you were talking about the client creating direct JDBC connections to the database, then yes - firewall or no, this is very bad practice. The client should talk to your server to ask for what it wants, and your server should do the DB queries as required to get the information.

• I think that would just be an unnecessary complication. Your DBMS (usually) brings access control and transport layer security. If you introduce your own layer, are you sure that you can make it safer than a direct connection to the DB?

  I see your rationale, but if there isn't a framework to do this, avoid building your own! For example, PostgreSQL comes with a bunch of nifty options to tie things down. For example, require SSL certificate-based authentication on the transport level (clients must present a cert that the server checks), or IP-based access.

  Of course you still have to trust your DBMS implementation to get basic details like access control right (= "uncrackable"), but you still need to rely on this anyway after the black hats have broken into your web-proxy ;)

  @dtsazza: Maybe edit your answer to include the keyword "VPN"? ssh tunnels probably scale badly outside of a private setup.

  Volker