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
669 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

763 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

7 Experts available now in Live!

Get 1:1 Help Now