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


G

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?
 
Ad

Advertisements

N

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.
 
S

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.
 
Ad

Advertisements

G

Guest

Thanks. That was it.

Sylvain Lafontaine said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top