Link to home
Start Free TrialLog in
Avatar of itsonlyme4
itsonlyme4

asked on

ODBC call failed to linked Server

We have an Access App with Linked tables to a SQL Server 2005 Database.

We have a Linked Server set up from SQL Server 2005  to a SQL Server 2008 Database for a call from a view in the SQL Server 2005 DB.

Some (but not all) user are receiving this error When trying to access the ACCESS Database:

Run-time error '3146' ODBC Call Failed

Error we are seeing in the SQL Server log on the  SQL Server 2008 Database:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xx.xx.xx]

All users have the same permissions to Databases in BOTh SQL Servers via AD Group permissions.

Here are the properties of the linked Server:

/****** Object:  LinkedServer [MySQLServer]    Script Date: 11/23/2016 11:52:54 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MySQLServer', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MySQLServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQLServer', @optname=N'use remote collation', @optvalue=N'true'
GO

Open in new window


Can anyone help?
Avatar of Qlemo
Qlemo
Flag of Germany image

Looks like the login info for the connection gets lost. The MSSQL 2005 user should be used to  connect to MSSQL 2008 automatically. Maybe the authentication for 2005 happens ok with anonymous?
Is it required that the (Access) user is kept thru the ODBC and linked server calls? It would be easier if you set up a static login for the linked server, but of course that removes all (real) user info on 2008 side.
You should check the ODBC connection for those users, of course, in case they are set up differently from those where access works.
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of itsonlyme4
itsonlyme4

ASKER

Changed the authentication method for the Linked Server config from NT to SQL authenticated..
I've requested that this question be closed as follows:

Accepted answer: 0 points for itsonlyme4's comment #a41907449

for the following reason:

Could not find a 'smoking gun'  as to why only some NT users were getting the error so switch to SQL authentication
That is what I've been recommending in https:#a41899499, no?