I have a Windows 7 64 bit client that is running a 32 bit of software and so needs a 32 bit ODBC connection to the database. I of course ignored the Administrative Tools > ODBC manager because that will open the 64 bit and instead went to c:\windows\syswow64\odbcad32.exe to input the system DSN connection. I went through the wizard and when I went to select the database got a message to say the server could not be accessed. I checked on the DB server that the SQL service was running, that the firewall had all necessary ports opened (it does as other clients can connect), that the SQL browser service was running (using a named instance,) that the instance had TCP and Named pipe connections enabled, and tested the username and password I'm using directly against the Database with SQL Server Manager.
The client machine is using a 3rd party Firewall and it says it is not blocking the ports ( I also tried switching off the firewall) but still get the error so I tried changing the client configuration to Named Pipes and could then connect when running the test at the end of the ODBC wizard. BUT when I ran the software, I still get an unable to open the database error!!
I went back to the ODBC setting and discovered that it had not kept the change to named pipes but had reverted back to TCP - Does anyone know why this is happening? (I'm logged in with an administrator account.)
I need to be able specify named pipes for the connection or if I'm using TCP figure out why it will not connect even with the firewall off.