How do I use a 32-bit ODBC driver on 64-bit Server 2008 when the installer doesn't create a standard DSN?
I ran into an issue with some third party software that we use to track software license usage in our computer labs. We recently migrated the app to 64-bit Server 2008 after receiving assurances from the company that it was compatible and running some preliminary tests that showed that the app worked in the 64-bit environment. Unfortunately the person doing the testing didn't test the functionality of a couple of apps that I have that rely on accessing the data to do both live- and post-processing on the data to produce some reports.
Turns out that the application doesn't have a 64-bit ODBC driver to access its internal data and can't use a 64-bit SQL Server ODBC driver to export its data to SQL server. It does include and install a 32-bit ODBC driver, but it installs it as a User Data Source, not a System Data Source, meaning that my windows service that runs the live collection isn't able to find it. I'm also not able to create a System DSN since the Data Sources admin console can't find the installed driver.
My question is how do I configure a data source for this connection that I can access from a C#/.NET windows service running under a system account?
Since I've figured out a way to do it, I'll provide my solution as an answer (in keeping with the FAQ on how to answer your own question).
Similar to: What software exists for bridging a 64-bit ODBC app to a 32-bit ODBC driver on windows?
Answer
It turns out that you can create 32-bit ODBC connections using C:\Windows\SysWOW64\odbcad32.exe. My solution was to create the 32-bit ODBC connection as a System DSN. This still didn't allow me to connect to it since .NET couldn't look it up. After significant and fruitless searching to find how to get the OdbcConnection class to look for the DSN in the right place, I stumbled upon a web site that suggested modifying the registry to solve a different problem.
I ended up creating the ODBC connection directly under HKLM\Software\ODBC. I looked in the SysWOW6432 key to find the parameters that were set up using the 32-bit version of the ODBC administration tool and recreated this in the standard location. I didn't add an entry for the driver, however, as that was not installed by the standard installer for the app either.
After creating the entry (by hand), I fired up my windows service and everything was happy.