Access UI to SQL Server2000 backend, ODBC System DSN link, aaarrghh!

Access UI to SQL Server2000 backend, ODBC System DSN link, aaarrghh!

Post by Peter Ro » Thu, 09 Oct 2003 23:18:19


Hi,

I have an Access 2002 (XP) MDB UI app that uses linked tables/views to a SQL
Server 2000 database.
The machines that run the Access UI will have a system DSN that links to the
SQL DB using (supposedly)
a SQL Login.

Things to mention:
1) The AccXP MDB will runs on machines that are on a different domain to the
SQL Server
2) I have another Access 2002 (XP) ADP app that uses the same SQL server
2000 database
but it uses NT authentication (no DSN).

Now the ADP app works fine and has no probs' accessing the SQL backend.
However the MDB app seems to only work with an NT login and not with a SQL
login.
Now the login dialog has a "use trusted connection" tickbox but that is
unchecked
when I attempt to login with a SQL username. Attempting to login with a SQL
login
gives a "username is invalid error".

The DSN has the "use Sql authentication" option selected so I'm not sure
what could be wrong.

If I use the MDB app on a machine on the same domain as the SQL Server then
the SQL user is
accepted but then I get an "ODBC call -failed" error upon first access to
the data.

I'm looking into network issues as well, but if anybody has any ideas about
what specific things
I could check I would be most grateful.

Regards,
Peter