ODBC password used to save. After updating table, now I'm prompted for pw every single time.

Question

question

bjensenAfp-6665 asked Mar 17, '21 Viorel-1 edited Mar 19, '21

ODBC password used to save. After updating table, now I'm prompted for pw every single time.

After migrating an Access database over to SQL, I linked the tables to the Access front-end using ODBC named "DatabaseName.dsn". In SSMS, I set up a login, using all default settings, for each user that would need access. Authentication is over SQL Server, not Windows.

Then on each user's machine, I logged in with the credentials that I set up. After that first time, no one was prompted to enter the password again.

Recently, I had to remove and re-add a table from the SQL database. This change has now caused everyone to be prompted to enter their password every single time they access one page but not another.

In Access, if I hover over each linked table, I see this information:

Table linked to page that doesn't prompt for pw - ODBC;DSN=DatabaseName;UID=Me
Table linked to page that does prompt for pw - ODBC;Driver=ODBC Driver 17 for SQL Server; Server=ServerName

So what do I need to change to have DSN for each linked table?

If you need more info, I can provide it. Thanks.

sql-server-general

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 · Mar 18, 2021 at 06:46 AM

Maybe use the application called “Credential Manager” (can be found in Control Panel) and try removing the obsolete saved passwords that look to be related to your case.

0 ·

CarrinWu-MSFT · Mar 19, 2021 at 08:04 AM

Hi @bjensenAfp-6665, we have not get a reply from you. Did the answers could help you? If there has an answer helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 ·

Answer 1 · 2021-03-17T15:23:23.813Z

TomPhillips-1744 answered Mar 17, '21

This is a MS Access question, not related to SQL Server.

Please see:

https://social.msdn.microsoft.com/Forums/office/en-US/a4259c7a-9068-4993-977a-08c8a5ca39bf/creating-linked-odbc-table-not-remembering-password?forum=accessdev

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-03-17T15:29:24.197Z

SADALI-1584 answered Mar 17, '21

Hi,
should you be forced to use ODBC?

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2021-03-18T05:25:41.513Z

CarrinWu-MSFT answered Mar 18, '21 CarrinWu-MSFT edited Mar 19, '21

Hi @bjensenAfp-6665,

Please refer to Save Password check box is unavailable when you link ODBC tables get the solution, see below:

Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

access.png (64.5 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question