ODBC Proxy for remotely accessing legacy resources?

Question

Our project uses AcuCorp's AcuODBC driver to access a legacy Vision database. The problem is that we only have a 32-bit driver and the installer simply won't run on our 64-bit servers. I need a way to use SSIS to pull data from that system. As far as I can tell, there are 3 options:

1. Set up a whole new SQL Server instance with SSIS and the AcuODBC drivers on a 32-bit VM (costly)
2. Try to hack the 32-bit driver onto our 64-bit server manually (failure prone and unsupported)
3. Set up a 32-bit VM with some sort of "proxy" service that our 64-bit SSIS can use to pull the data.

The first option is the least desirable. If you have any suggestions for options 2 or 3, or anything else I haven't thought of, I'd love to hear them.

Answer 1

The problem is that we only have a 32-bit driver and the installer simply won't run on our 64-bit servers.

I'm curious about this. Is it a non-standard installer?

Also, is the install that complex that is cannot be done manually? I.e. using the 32-bit emulation environment versions of regedit and regsvr32 to perform the installation manually.

Answer 2

So I solved the problem without really answer my question, so I'll post my solution as well as some resources I found.

My solution was to set up a 32-bit Windows VM with the ODBC drivers installed and use SQL Server 2008 Express Advanced (free!) to create a linked server that I could access from my 64-bit SSIS environment. This works great, I can't believe it didn't occur to me sooner.

Anyway, also did some research on database relays, proxies, and bridges.

The best thing to search for in my case was "ODBC-ODBC bridge". There are a couple of commercial products out there, nothing free. Around a thousand bucks. There is a $50 ODBC relay product that is not worth it because it uses a proprietary TCP/IP protocol, rather than bridging to the ODBC protocol.

There are some free JDBC-JDBC bridges that can be combined with the JDBC-ODBC bridge to create workable relay if your client can use JDBC. There is also an open source ODBC-JDBC bridge project (not sure of the quality) that could perhaps be used to create an ODBC-JDBC-JDBC-ODBC bridge. Ha.

The final option, which I came very close to doing, was to write a simple .NET web service that takes credentials and a query and returns results as CSV. (In my case however, the ODBC driver did not correctly work with .NET as it was based on an older version of ODBC than the .NET framework uses. Otherwise I think this would have worked ok).

Obviously you need to understand the security implications of any of these.