[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1839
  • Last Modified:

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!
  • 3
  • 2
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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)PresidentCommented:
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.

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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)PresidentCommented:
<<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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now