Recently we have been working on a Citrix migration and one of the hurdles we ran into was with a core application that relies on an ODBC connection to a SQL database. Under normal circumstances we would just create a System DSN and be good to go, but in this case it just wasn’t working. To help troubleshoot the issue we ran through the ODBC setup tool included within the application, unfortunately this only created a user-specific ODBC connection which would require us to log in as each user and manually setup this connector; not an ideal situation.
After several attempts at scripting an automatic creation of User DSN upon login using various tweaks in VB script, and no success (although we got close) we took another look at the System DSN. We were running a 64-bit OS with 32-bit software so the problem had to be there, right? Once we confirmed that System DSN was being used on the old server, we knew that scripting something was not the solution, but indeed a System DSN should work.
We created the DSN as normal through Start, Administrative Tools, ODBC connections, but again, the application just didn’t see it. To confirm, we launched the 32-bit version of ODBC by running it from C:WindowsSysWOW64odbcad32 where our connection was indeed listed. After some further troubleshooting we found out that despite our DSN being listed in both architectures, the ODBC connection must be created from the 32-bit ODBC in order for the application to see it. Once we deleted the existing System DSN and recreated it with odbcad32, we had a happy system.