ODBC Connection not working when database is closed and re-opened.

[Guest]

An ODBC connection was created using SQL Server authentication. A linked
table was created using this connection and the data is successfully
accessed. When the database is closed and re-opened and the table is clicked
and the following message is displayed:

.......Login failed for user us\smithr

When OK is clicked on the above message screen, the SQL Server Login screen
is displayed with the Use Trusted Connection box checked and the user id,
smithr displayed. When the box is unchecked and the login information
existing in the DSN file is entered, it works. Can someone tell me why it
defaults to the trusted connection and not the information created when the
ODBC connection was created. Could it be that the password is not being
saved and if so, how is the password saved when creating the connection?

 

[Norman Yuan]

I am not sure how the ODBC connection is created (ConnectionString? DSN?),
it is obvious that when the database application is re-opening, it is
somehow trying to connect to the SQL Server with Windows security (trusted
connection), hence the showing user name as "WindowDomain\UserName". So,
re-examine your ConnectionString or DSN. If there is something like "Trusted
Connection", or "Integrated Security..."..., then you need to modify the
ConnectionString/DSN to make sure it uses SQL Server security and correct
UserName/Password pair is entered.

 

[Sylvain Lafontaine]

When you create a new linked table, you have the option "Save password" in
the last dialog window, the one which displays all tables on the SQL-Server
side and let you choose one or more of these.

If you are not using a Trusted Connection, then you must check this option
to have the password saved with the link. BTW, even when it's chosen, you
won't be able to see the password in the ODBC properties if the linked
table.

The easiest solution that you have now is to delete all these linked tables
and recreates the links but now with this option ON.

 

[Guest]

Thanks. That was it.

When you create a new linked table, you have the option "Save password" in
the last dialog window, the one which displays all tables on the SQL-Server
side and let you choose one or more of these.

If you are not using a Trusted Connection, then you must check this option
to have the password saved with the link. BTW, even when it's chosen, you
won't be able to see the password in the ODBC properties if the linked
table.

The easiest solution that you have now is to delete all these linked tables
and recreates the links but now with this option ON.