Avatar of dalevv
dalevv
Flag 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!
Dale
Microsoft Access

Avatar of undefined
Last Comment
dalevv

8/22/2022 - Mon
jorgedeoliveiraborges

Dale,

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.
no worries :-) no nothing

Try the follwing syntax
"Driver={SQLServer};Server=yourserver;Database=yourdb;Uid=your_usr_name;Pwd=your_pw;"

no worries :-) no nothing

but for trusted connection:
"Driver={SQLServer};Server=yourserver;Database=yourdb;Trusted_Connection=yes;"


Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dalevv

ASKER
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.
ASKER CERTIFIED SOLUTION
dalevv

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dalevv

ASKER
I found a solution myself.