Remove login prompt with SQL linked tables in Access

Question

I have an issue similar to this one: SQL Server 2008: ODBC connection problems

But mine is unique because I already have the "Save Password" option checked when I link my tables, AND it works fine unless I try to open more than one query at a time.

Steps to recreate:

1) Link a SQL table to an Access 2003 front-end, my DSN looks like this:

[ODBC]
DRIVER=SQL Server
UID=ACD
WSID=ACD
APP=ACD
SERVER=xx.xx.xxx.xx,1053
Description=ACD Connection to SQL Server
Pwd=XXXXXXXX

At first I didn't have the PWD line, it doesn't seem to make a difference with or without that.

2) Open 1 query that uses the linked table, no login prompt

3) Open 2nd query while first one still open, get this error followed by login prompt:

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed. 
The login is from an untrusted domain and cannot be used with Windows authentication.

I can open as many tables as I want directly and I don't get a password prompt.

If I login once, no more prompts until I restart Access, but this isn't an option for my app.

I'm using SQL server auth only, not setup for Windows auth.

Solution

You could try using a DSN-Less connection to your SQL server, microsft have a support page for this: http://support.microsoft.com/kb/892490

This should stop the prompts