ODBC Connection Issue

I have an urgent issue I have been dealing with for over 48 hours now (should have opened this question long ago!).

I am unable to connect to an existing SQL Server 2008 database from a workstation using ODBC.  Here's what's strange: I can set up the connection (System DSN, Windows Authentication), and actually select the default database (tells me I have successfully connected to the server in order to get the database list in the first place).  At the end of the wizard I click the 'Test Data Source... button, and all tests complete successfully.  So it seems that server name, username, password, firewalls, and all permissions are set successfully.

However, when I attempt to use the newly-created connection, I am getting the dreaded Connection Failed/ConnectionOpen(Connect())/Server Does Not Exist Or Access Denied errors (2 & 17).

Looking at the SQL server logs, I can see where I connected successfully during the test, but there is no entry for the connection attempt during actual use.  What is the difference between the Test Data Source function and 'real' use?

Any ideas as to why I cannot even touch the server, let alone attempt a connection?
Who is Participating?
Looks like authentication problem to me. What credential are you giving? Is it windows authentication or sql server authentication?
Which ODBC driver are you using?

If SQL Server try to change the driver into SQL Server Native Client 10.0.

Hope this helps,
RareSeedsAuthor Commented:
Hi guys, thanks for the insight.  I actually found the problem.  It was with Windows 7's 64-bit ODBC driver not being compatible with Access 2007.  I turned to the 32-bit connection utility (odbcad32) and everything worked perfectly.

I should have mentioned that the application was Access 2007 as that may have spurred an immediate response in this direction.
RareSeedsAuthor Commented:
I'm going to split the points among all three since the two experts also have correct answers to this type of problem.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.