Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007

    Unanswered: SQL server linked table uses trusted connection but i said no!

    i have an access database with a linked table to a SQL server. i used VB to set the connection string to something like this:
    ODBC;Driver={SQL Server};Server=xxxx;Database=xxxx;UID=xxxx;PWD=xxxx
    on some computers, this works just fine. on other computers, i get an error:
    Connection Failes:
    SQLState: '28000'
    SQL Server Error: 18456
    [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'DOMAIN\username'
    notice that it's logging in with the domain credentials (i.e., a trusted connection), and not the username i specified in the connection string. i tried putting Trusted_Connection=No; in the connection string, but that didn't improve the situation.

    i found this article in microsoft's KB, but the affected PC's all have the same, newer versions of those files, and are all running MDAC 2.8 SP 1.

    any other ideas?

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    This is a bit of a stab in the dark but should "UID" be "Uid" (does case matter?). Same goes for Pwd.

    It might be a bit trivial but I think the connection string needs to end in a semi colon ( too.
    Home | Blog

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    If it works fine on some computers but not others, my guess is it's something with the permissions on sql server for what it recognizes as the user on the computers it doesn't work or it's ODBC DSN related for that computer. Have you tried logging into the computer it doesn't work on with the user name and password of the computer it does work on to see if it's user related or computer related?

    Have you also checked the ODBC DSN for the computer it doesn't work on? If it's ODBC DSN related (for that computer), you can try some code in the MS Access Code Bank which automatically creates an ODBC DSN name for the computer being used or just create an ODBC DSN for that computer and see if it works. ALWAYS make sure the ODBC DSN name is EXACTLY the same name for EVERY computer which accesses that same database or you will have problems. If not, it will work fine one time and not the next time after someone else has gotten into it with a different ODBC DSN name (and confuse the hell out of you on why it works sometimes and not other times.)
    Last edited by pkstormy; 07-14-07 at 13:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts