Avoiding SQL login prompt with Access pass through queries

[dcefros]

Using Access 2010 with and ODBC connection to SQL Server 10 my linked tables work fine, but each time we load an accdb file and execute a pass-through query we are prompted with the SQL Server Login dialog where the user must click OK to confirm the Login ID and Password. Once this initial prompt is handled it does not occur again while the accdb file is open.

This process is problematic because the end users could accidentally change one of these stored values, which we do not want them to know in the first place. Also, since the end users are in and out of this application they must acknowledge this prompt several times a day. Since these values are already stored and known to the system, is there not a way to bypass this prompt?

I have run some code found on this site to relink all the query defs using the connection string, but that did not help

[zmbd]

QryDefs?

+ Do you mean tables instead of queries? It is the underlying linked tables that will need to have the connection string set to the back end.

+ For linked tables I use a modified version of the first set of code here: M.S. How to create a DSN-less connection to SQL Server for linked tables in Access in code that checks to see if the table is a linked table and how it's linked to the backend.

+ For saved queries, you can open them in design mode, show the properties sheet, click in the table area of the gui. This should now show you the properties for the query itself where in you can set the source database and the source connection string.

With the query You could also do:

 - air SQL here -
SELECT PK, [field]
 FROM xmplTvbl
 IN [ODBC; DATABASE=database; UID=user; PWD=password; DSN= datasourcename]; 
 

Where the connection string has the username, password, or trusted connection etc...

>>I've read that you should start the query out as a pass-through query first, then create the query string, finally show the properties sheet for the query and set the [ODBC Connect Str] first...
If you are using a DSN connection the general format is: ODBC;DSN=ServerName;UID=username;PWD=userpw;

The issue here is that the user name and password are stored in plan-text.

+ Would you mind posting the code you used to "re-link" your tables?
Obviously you should replace the username(s), password(s) and servername(s) with generics. :)

-z

[Rabbit]

One thing you should be aware of is that even if you are able to prevent the prompt from coming up, that doesn't mean the user can't get to the username and password you use to set up the connection in the first place.

[jforbes]

One last thing that I think should at least be mentioned is that if you change the authentication to use Windows User Authentication/Trusted Connection this problem will go away. It would also provide the added benefits of giving you a user level security for your database while removing the unprotected saved passwords that are needed for SQL Authentication. Some of this can be automated, but this may be more maintenance than you are willing to deal with as each user would need to be added as a user of the Database.

[dcefros]

Thank you all for your feedback. I do not think the problem is table linking. I have relinked using code as well as the standard Access method and the result is the same.

To test this I simplified the process down to a single pass through query. The pass through query has the correct connection data (See screenshot).

After opening the ACCDB the first action I perform is to manually open this query. The first time it opens the user is prompted to confirm the connection. If I click OK then any future time I open that query or any other pass through query the system does NOT present the Server Login dialog. This affirms that the connection data stored with the query is correct. But it does not explain why this happens the first time I try to execute any pass through query. There is no prompt if you open a linked table immediately after opening the ACCDB.

I would like the SQL Authentication solution, however due to politics this is not possible.

[zmbd]

@dcefros:
I've found when this happens there's something mistyped within the connection string, connection strings can be very touchy. :(

For example an extra space between the password and the semicolon caused me no end of headaches :

ODBC;DSN=Server_data;
DESC=LIMS_Data;DATABASE=Server_data;
SERVER=10.10.10.10;
PASSWORD=limsuser ; <<<HERE<<<
PORT=0000;OPTION=3;STMT=;;TABLE=audit

Your image is fine; however, it cuts off portions of the string.
Please go back into the properties sheet, select the entire text, cut and paste into a reply...
Of course, replacing the actual password, username, servername with their generic equivalents :) However, be careful when doing so not to alter any of the current spacing etc...

@jforbes: AGREED!
I had considered suggesting this; however, some DBA either do not understand how to handle this (I know I don't - then again, I'm not a SQL-Server-DBA (YEA!)), just simply don't want to deal with doing so... I don't know why, or there are (as OP mentioned) inter/intra-office politics.

[dcefros]

The exact connection string looks like this:
ODBC;DSN=XXXX-YYYY;Database=MyDBASE;USID=XXXXX;PWD=ZZZZZZZ

There are no spaces in the string as it appears in any of my pass through queries. I have tried it with a closing semi-colon and without; no difference. As shown there is a hyphen in the ODBC connection name. This has never been an issue with linked tables.

[zmbd]

dcefros:
1)

As shown there is a hyphen in the ODBC connection name

Hyphens are allowed (although I personally would not use them) in the DSN name per RFC 1123 ((Complying with Name Restrictions for Hosts and Domains))


2) I am not able to recreate the same issues you are seeing.

What I have done:
+ From a production access database front-end connected to MySQL pulled all of my connection strings - saved to a plan text file using notebook

+ From a production access database front-end connected to a SQL-Server pulled all of my connection strings - saved to a plan text file using notebook
>> Neither of these backends use windows authentication; thus, no "trusted connection" available.

+ Altered these connection strings to use my username and userpassword.

+ Created two new databases; one for MySQL and the second for the SQL-Server.

+ No linked tables

+ Created passthru queries using the appropriate connection strings

+ closed these files and rebooted the PC to ensure that there were no residual connections.

++ Opened the SQL_Server_Direct.ACCDB and ran the queries without any login prompt.

++ Opened the MySQL_Server_Direct.ACCDB and ran the queries without any login prompt.

<<>> I repeated the above; however, inserting linked tables the second time around along with the passthru quiries, with the same results - no login prompt.

>>> At this point, it appears to be either something wrong with the username and password combination in the passthru query connection string or something set on the systems you are using. TBH - I am now at the limit that I can provide you any help. Perhaps one of the more knowledgeable Experts/Moderators has a few more suggestions.