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
683 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

752 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