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

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;"

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dalevv

ASKER

I found a solution myself.