So you created a DSN on a Windows 64bit machine and your application can’t connect to it, huh?  Even more confusing is that the DSN tests fine and connects to your SQL server without issue.  I ran across this issue back when Windows 2008 came out.  I had completely forgotten about it until recently when someone facing the same issue asked me if I knew what the problem could be.

Now in my case, I was moving everything from a Windows 2003 32bit machine to a Windows 2008 64bit machine.  The application consuming the DSN was a 32bit classic ASP site.  The first thing I did was copy the site over to the new server and configure it in IIS.  Let me also interject here and say that this took some time because MS changed the look of IIS from version 6 to 7 and in my opinion it is less than intuitive.

My next step was to setup the DSN for the application to access my SQL database hosted on another physical server.  No problem I thought, I will just go under start and administrative tools and use the “Data Sources (ODBC)” utility to set it up.  I setup the DSN in the utility and it tested fine, but my application still could not connect.  Did you remember that I mentioned the application was 32bit?  A 32bit application uses the 32 architecture and associated DLL libraries and a 64bit application uses its associated libraries.  The problem is the “Data Sources (ODBC)” utility, under start and administrative tools, is the 64bit version and Microsoft does not show that there is a 32bit version in the GUI.  The GUI strikes again!  That’s okay.  We’re DBAs and we know to not trust the GUI.

The solution is to create your DSN for 32bit applications in the 32bit ODBC utility and your DSN for 64bit applications in the 64bit ODBC utility.  So where is the 32bit version hiding?  It’s right here:

C:\Windows\SysWOW64\odbcad32.exe