ODBC continually prompts for password

Question

I have an application built in Access 2003 that uses a system DSN ODBC to connect to a SQL Server. The ODBC uses SQL authentication. When the application is started, the user is prompted to authenticate into the database.

I have another computer set up within the same domain that has Access 2007 installed on it. I log in using the same credentials that I use to get on the machine that has Access 2003.

I converted my application to Access 2007 format and everything works fine. However, when other users try to use the application, they are prompted to enter the database password every time a table is accessed. Thinking it was a problem with my ODBC, I confirmed that the connections were set up the same way on both of my machines, and the user's machine.

Here is the interesting part, when the user logged into my machine, it started prompting for the password every time. When I logged into the user's machine, the application worked fine.

Anyone have any ideas? All help is appreciated!

Answer 1

Be sure to use a System DSN, not a User DSN. It's easy to create the wrong one since the tabs are side by side.

Make sure that the System DSN has SQL Server authentication picked and you have the login id and password set.

A User DSN is only visible to the user that creates it. A System DSN is available to all users and Windows services.