asked on

ODBC call failed when trying to open a SQL Server view linked to Access

Access 2007 database, lots of linked SQL tables and views, all with connection strings that look like:

ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=XXXXXX;Trusted_Connection=Yes;APP=SSMA;DATABASE=XXX_System_Tables;

Every one of these linked tables and views run properly except one, which generates an error:

ODBC--call failed
[Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.(#18456)

The only difference I can find between this view and others is that this view includes a reference to a table in a linked server, so I can only assume that there is something about the permissions established for querying the linked server. When I open Access on the server and create a pass-thru query using the SQL from the view, it runs fine. But when I open Access from my desktop, connect to the server via a VPN with the same login credentials I get the error mentioned above.

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

ASKER

Have not had a chance to return to this question lately. Am working on another similar issue that looks like it has to do with the first SQL Server not being able to pass the Windows authentication information to the 2nd server. Will hopefully get this resolved, or ask more questions shortly.

Dale

Dale Fye

ASKER

Thanks for your help guys.