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:WindowsSysWOW64odbcad32.exe
Good tip Ryan. This also threw me for a loop when we upgraded our PeopleSoft and put it on MS 2008 servers. It still amazes me that it’s acceptable for software that expensive to be so far behind the curve of technology. Sorry, you made me rant
Good post. I haven’t dealt with this as I haven’t used a DSN in awhile, but can see it happening to me.
Can’t say I disagree with you that IIS 7 is less intuitive to use than IIS 6. I’m not in there often, but I still get lost and I didn’t in IIS 6.
I’m getting a real weird error on this…
I tried to do the same thing with C:WindowsSysWOW64odbcad32.exe, and the database was in my profile on a network share.
I got the error:
“Invalid directory or file path”
But when I moved the databases to my local hard drive there was no problem at all.
So there’s no way to setup a DSN when the database resides on a remote drive?
It’s most likely a permissions issue. Make sure you check both the share and NTFS permissions. Remember that the least restrictive always applies between the two. You also need to consider whether you are using a User or System DSN.
There is also the point on the “Providers” branch under “Linked Services” adjusting the MSDASQL properties, (right click on MSDASQL) and then check “Allow inprocess” to OK.