none
ODBC--connection to 'SQL Serverservername' failed for one user but not another

    Question

  • In Win7, we're linking tables in an MS Access 2010 db to tables in a SQL Server 2008 R2 db. The driver user by the File dsn is SQL Server version 6.01.7601.17514 & we're using SQL Server Authentication.

    For some reason, one user gets the msg "ODBC--connection to 'SQL Serverservername' failed" before they're even asked for a password, but for other users, the prompt comes up and when they uncheck the Windows Auth box, they enter their password and connect successfully to the SQL Server db.  Both users have db_datareader access to the SQL Server db.

    I had the user that gets the error msg log onto my PC and they get the same error (yet it works for me.)

    This user was, however, able to successfully relink the tables, but then when we closed the access db and opened it again, the user got the "ODBC--connection..." error agin.

    I'm stumped as to why this is happening for one user.

    • Moved by Olaf HelperMVP Thursday, January 23, 2014 3:28 PM Moved from "SQL Database Engine" to a more related forum
    Thursday, January 23, 2014 3:19 PM

All replies

  • Hi

    do you have the same OS level and SQL driver level for all Clients? do you have all AD users correctly configure in SQL? 

    Thursday, January 23, 2014 3:24 PM
  • Try to run "C\windows\sysWOW64\odbcad32.exe," to set up the ODBC

    --Prashanth

    Thursday, January 23, 2014 3:27 PM
  • Users have the same version of MS access and SQL server and both have read access to the SQL Server database, yet one is having the problem (user1). 

    And when user1 logs on to a different pc, even though other users can access the linked tables in the access db when they're logged into that computer, user1 still gets the error message.  When other users log in and to open a linked table, they are asked to enter their id/password and then connect successfully.

    Thursday, January 23, 2014 5:16 PM
  • Hello Knellen,

    Please help to collect more log information regarding this issue, such as windows event log, SQL Sever log information. They are helpful for us to troubleshoot it.

    Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, January 29, 2014 7:17 AM