Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
688 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 1000 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 1000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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