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
673 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now