ODBC to SQL 2005, pass through queries ask for password

[nnn]

I have an application that's deployed at several location on windows
2003 servers. On each site it uses a distinct system dsn which contains
sa user and pswd, different for each site. Access ver 2000.

On on server, any time I call a passthrough query, I'm prompted for the
sa password. The linked tables present data without prompting for
password. I've removed and recreated the system dsn and the issue
persists.

All of the servers have the latest patches for windows 2003, and also
for sql server express. Why isnt' the password being retained by Access,
only for pass through queries?

 

[nnn]

I have an application that's deployed at several location on windows
2003 servers. On each site it uses a distinct system dsn which contains
sa user and pswd, different for each site. Access ver 2000.

On on server, any time I call a passthrough query, I'm prompted for the
sa password. The linked tables present data without prompting for
password. I've removed and recreated the system dsn and the issue
persists.

All of the servers have the latest patches for windows 2003, and also
for sql server express. Why isnt' the password being retained by Access,
only for pass through queries?

Oh well, that was cleared up as soon as I posted. I had used win auth on
the other servers. Still curious as to why the sa password was not
retained by the passthrough queries?

 

[david]

There is actually an Access connection option that controls this
behaviour (save ODBC password), and you may wish to look
for that, but I wouldn't expect it to work.

It used to be possible, but somewhere along the line it was disabled
without documentation for security reasons. Access probably can't
and doesn't store passwords for ODBC sources anymore. You either
have to enter the password each time, or use an ODBC driver
and DSN that can store a password, or use a system like Win
Authorisation that doesn't require a password each time.

Just putting the password into any DSN is not good enough, because
Access only uses the DSN for reference. Connection details are
stored with the connection, and Access only checks the DSN when
it feels the need. So you would need a driver that explicitly chooses
to check the DSN, regardless of the instructions from Access.

(david)