ODBC Continually prompts for password

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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Anyone have any ideas? All help is appreciated!>>
  When you link to the ODBC tables, there is an option to save the password used.  Make sure you check that box checked.
  Also, this user does have a login on the SQL server correct?  And you know what the password is for sure?
MarkRodAuthor Commented:
Since the application is already developed, I just refreshed the table links once I converted it. Do I need to delete the links and re-link them in?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
With SQL Server, if your not using a trusted connection, which is that SQL Server will trust someone if they have been authenticated by a windows login, then you need to supply in some way, shape, or form a user name and password for the connection. If you don't, the way ODBC works is that it will prompt for anything you don't supply.
You can embed a username / pswd right in the connection string with: Uid=<myUsername>;Pwd=<myPassword>;
either in a DSN (which you may be using) or in the table definitions connect property directly:

tabledef("myTable").Connect = "<connection string>"
By doing that, all users would access SQL server with the same username/password.
Baring all that, the current user name will be used, along with the cached password if you check the box.
So, your best bet is to use trusted connections. It's the least amount of work as far as the app is concerned. The connection string must include:
for that to happen. SQL will then automatically look for a SQL login with the same name as the current user and use that account to access everything.
When you link to a table, it will look like this if you don't check the "save password" checkbox:

ODBC;DSN=XYR;Description= Traverse - IDS;APP=Microsoft® Access;WSID=ICPAPP01;DATABASE=XYR;Network=DBMSSOCN;
and if you check the box, it will look like this:
ODBC;DSN=XYR;Description= Traverse - IDS;UID=Jdettman;APP=Microsoft® Access;WSID=ICPAPP01;DATABASE=XYR;Network=DBMSSOCN;

notice that my name is now part of the connect string. The password is cached and stored as part of my windows profile. However anyone using a copy of this app on this station is now going to have a problem, as they will always be trying to login with my username.
So you have three basic choices:

1. use trusted connections
2. Collect the username and password at app startup, then modify all the connect strings on the linked tables to supply that to SQL.
3. relink the tables at app startup (user still needs to get prompted for a password).
#1 is the cleanest.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MarkRodAuthor Commented:
This application has been in production since 2006. We have been using a System DSN with SQL authentication. All users use the same login/password.

Now that I converted a test copy of my app to 2007, it seems to be prompting for the password every time a table is accessed. However, if I log in with my credentials, even though the ODBC is set up the same, it will work fine.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<All users use the same login/password.>>
And that is in the DSN? And what type of DSN is it?
Just a comment on my experience
I do know that using a DSN in 2007 that was  created in 2007 ACCDB  does not act the same as one created in 2003 and used in 2003 MDB or a converted 2007 ( look at connection screen tip by hovering over linked table- its different)
(I ended up just using connection strings)
But if you can make you app go back to 2003 - create a DSN in the mdb that works - then convert to 2007 but don't change DSN - then it seems to work
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.