Solved

MS Access & SQL Server & ODBC: The User gets challenged for passwords *dozens* of times when application is used with each action in the app

Posted on 2012-04-11
4
676 Views
Last Modified: 2012-04-12
Background:
1)      I have an Access app with many drop-down menus whose contents are gotten from linked database tables.
2)      The User goes through the many fields making choices from those many drop-downs.
3)      When I use the app on my machine (I’m the developer of the app) the “SQL Server Login” (ODBC) asks me to fill in the password just once to connect, via ODBC, to the SQL Server database.SQL Server Login (ODBC) authentication challenge dialog
Problem:
1)      When I have my colleague use the Access app he is constantly being challenged to provide a password every time he makes a choice from a given field. Thus, as he tries to fill in the apps’ fields, he again and again gets challenged by the “SQL Server Login” to re-authenticate himself to the SQL Server connection every time a new field was being filled.

Questions:
1)      Why is my colleague’s machine different than mine?
2)      It seems that the app or ODBC “forgets” the fact that my colleague has already authenticated himself to the SQL Server database. Could there be some setting that my colleagues machine needs set?
0
Comment
Question by:stuckp1
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 37833824
Authentication info is passed to SQL Server in a connection string.  For a linked table, Access constructs the connection string by merging relevant information from the tabledef properties and from the ODBC DSN.   Prompts are issued for any necessary information that is missing from those two sources.  Once connected, the resulting DSN in cached on the workstation.

So, we now know that with your configuration(s), at least the password is not present in any of the places it can be stored persistently.  That's perfectly fine if the password prompts are desired.  We also suspect that your ODBC cache is retaining the DSN longer than the other workstation.  

There can be many causes, from different ODBC drivers to the available machine resources to who-knows-what-else.  I don't know of a specific setting to control it and I don't even know if it's completely controllable.

You may consider switching to trusted connections, which would not require password entry for anyone.  Beyond that, verify that you are using the same ODBC drivers and that the ODBC DSN's are exact copies.  To that end, you might consider using FILE DSN's that can be distributed with the Access application or DSN-less connections that are controlled by your application.
0
 

Author Comment

by:stuckp1
ID: 37834737
Thanks for the suggestions of avenues to investigate.
I'll keep you posted.
Peter
0
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 250 total points
ID: 37837319
To avoid this issue, we open a global read-only DAO recordset when the app first opens.  It doesn't matter which table; we use a one-record configuration table that's in all our apps.  We leave the recordset open until the app closes.  This seems to "force" Access to use the existing connection, so no more prompts.

Hope this helps,
Armen
0
 

Author Closing Comment

by:stuckp1
ID: 37837890
Many thanks for your solutions.
The File DSN worked to solve the immediate problem of continuous SQL Server Login challenges.
The approach of using DAO to open up any table is also very handy for where the connection itself is flakey.
Many thanks to both!
Peter
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question