How do I use a 32-bit ODBC driver on 64-bit Server 2008 when the installer doesn't create a standard DSN?

Question

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?

Solution 1

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.

Solution 2

Open IIS manager, select Application Pools, select the application pool you are using, click on Advanced Settings in the right-hand menu. Under General, set "Enable 32-Bit Applications" to "True".

Solution 3

A lot of these answers are pretty old, so I thought I would update with a solution that I think is helpful.

Our issue was similar to OP's, we upgraded 32 bit XP machines to 64 bit windows 7 and our application software that uses a 32 bit ODBC driver stopped being able to write to our database.

Turns out, there are two ODBC Data Source Managers, one for 32 bit and one for 64 bit. So I had to run the 32 bit version which is found in C:\Windows\SysWOW64\odbcad32.exe. Inside the ODBC Data Source Manager, I was able to go to the System DSN tab and Add my driver to the list using the Add button. (You can check the Drivers tab to see a list of the drivers you can add, if your driver isn't in this list then you may need to install it).

The next issue was the software that we ran was compiled to use 'Any CPU'. This would see the operating system was 64 bit, so it would look at the 64 bit ODBC Data Sources. So I had to force the program to compile as an x86 program, which then tells it to look at the 32 bit ODBC Data Sources. To set your program to x86, in Visual Studio go to your project properties and under the build tab at the top there is a platform drop down list, and choose x86. If you don't have the source code and can't compile the program as x86, you might be able to right click the program .exe and go to the compatibility tab and choose a compatibility that works for you.

Once I had the drivers added and the program pointing to the right drivers, everything worked like it use to. Hopefully this helps anyone working with older software.

Comments

[Alex Black]

You've figured it out? nice. The only thing I can add is that (afaik) you can't access a 32bit ODBC driver from a 64bit program (or vica versa)

[tvanfosson]

It's a 32-bit app running on a 64-bit computer. If I had it to do all over again, I'd be more skeptical of the claim that their software runs on 64-bit when their platforms page doesn't even list Server 2008.

[Dave Markle]

Things get REALLY hairy when you are hosting this stuff under IIS 6 (I think this might not be an issue in IIS 7?) -- Under IIS 6, you can't host both 32 bit and 64 bit programs, so if you're serving up 64-bit ASP.NET, you are pretty much hosed if you need 32 bit ODBC stuff. Ugh!

[Admin]

What changes exactly you did in registry for odbc? as we are also having the same issue?

[MacGyver]

Apparently, Microsoft never renamed their "system32" folder, so system32 really has all of the 64 bit drivers. And SysWow64 folder has all of the 32 bit drivers. And launching the "odbcad32.exe" under the SysWOW64 folder opens up the 32 bit version of the ODBC Data Sources dialog. By default, a .NET project will have a "Target Platform" of x86. I just changed it to x64 and my connection worked to get my 64 bit NetezzaSQL driver working for .NET.

[MacGyver]

I'm guessing Windows has a way to set up the default configuration of whatever dialog control panel opens and the way it installs applications. Or provides a right click context menu to do so. The Netezza driver that I used is the same ODBC driver I have on a Windows XP box. So I'm guessing it works for both 32 bit and 64 bit, but I'm not sure. Can you tell me where specifically you placed your key for the 32 bit driver in the registry, and what strings you used? I will +1 you if you can place a screen shot in your answer.

[tvanfosson]

@MacGyver unfortunately I've changed jobs since I wrote this answer and I don't have access to that system any more.

[MacGyver]

no big deal. Placing a reference to my question and answer--further explained: stackoverflow.com/questions/11478252/…

[JohnB]

Doesn't a 32-bit app automatically look for the 32-bit version of a ODBC driver, and vice-versa, a 64-bit app automatically looks for the 64-bit version of a ODBC driver?

[tvanfosson]

Unfortunately the app was not running in IIS.