Link to home
Start Free TrialLog in
Avatar of dalevv
dalevvFlag for United States of America

asked on

Access 2003 switches to Windows Authentication instead of SQL Authentication

I am trying to setup DSN-less links to a MS-SQL server using SQL authentication.  I am able to create the table links and they work during the session that I create them, but if I close and re-open the database they fail saying that "Login failed for user machine\username" - indicating that it tried to connect using Windows authentication.
I could have swore I've had this work before, but I've been unable to get it to work in this case.
Here is the code I'm using to create the links.

   Set tdfLinked = CurrentDb.CreateTableDef(strTable)
   tdfLinked.Connect = "ODBC;DRIVER={sql server};DATABASE=" & strdbName & ";SERVER=" & serverName & ";UID=" & strUid & ";PWD=" & strPwd & ";"
   tdfLinked.SourceTableName = strSourceTable
   CurrentDb.TableDefs.Append tdfLinked

Any help would be MUCH appreciated!
Avatar of jorgedeoliveiraborges
Flag of Brazil image


Create a file named dale.udl (universal data link), double click, configure it, test it, and use the string connection offered by the .udl file, please.

I guess your middleware changed.
Try the follwing syntax

but for trusted connection:

Avatar of dalevv


Jorge - When doing a UDL, using the "build" option only seems to let me create a DSN and then it builds a connection string for that.  I want a DSN-less connection.  I did, however, enter my own connection string into the UDL and it works fine and it saves the password.  Should I be using that UDL file somehow?
By the way - when i say this worked before - that was at a different organization.

Georgekl - I believe that's exactly what I was doing.  Just for the fun of it, I did try it without using variables, and I had the same result.  The connection works while I'm in the same session as when I created the connection.  If I close and re-open the database, it goes back to trying Windows Authentication.
Avatar of dalevv
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dalevv


I found a solution myself.