ODBC Connection Issue

Posted on 2011-10-14
Last Modified: 2012-05-12
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?
Question by:RareSeeds
    LVL 25

    Accepted Solution

    Looks like authentication problem to me. What credential are you giving? Is it windows authentication or sql server authentication?
    LVL 12

    Assisted Solution

    Which ODBC driver are you using?

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

    Hope this helps,

    Assisted Solution

    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.

    Author Closing Comment

    I'm going to split the points among all three since the two experts also have correct answers to this type of problem.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now