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:
Can anyone help?
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
Can anyone help?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Changed the authentication method for the Linked Server config from NT to SQL authenticated..
ASKER
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
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?
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.