InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 02:51:39 GMT20http://www.sqlservercentral.com/Forums/Topic473766-146-1.aspxInteresting responses.When faced with the same problem, I went to the remote server and changed the permissions on the account "NT AUTHORITY\ANONYMOUS LOGON". Giving this sysadmin privileges fixed the problem, but that is overkill! In the end, I simply gave the account read access to all the databases on the server.--gordonWed, 13 Oct 2010 08:07:15 GMTGordon Linoffhttp://www.sqlservercentral.com/Forums/Topic473766-146-1.aspx[quote][b]sunshine (3/24/2008)[/b][hr]I can select from a master table however get this error for all user databases. any idea what is going on? Msg 7314, Level 16, State 1, Line 1The OLE DB provider "SQLNCLI" for linked server "Server Name" does not contain the table ""DBname"."dbo"."sysfiles"". The table either does not exist or the current user does not have permissions on that table. This is the setting from my linked server. declare @ServerName varchar(50)Set @ServerName = 'Server Name'--exec sp_dropserver @ServerNameexec sp_addlinkedserver @server=@ServerName, @srvproduct='', @provider='SQLOLEDB', @provstr='Integrated Security=SSPI;'-- Set optionsexec sp_serveroption @ServerName, 'collation compatible', 'true'exec sp_serveroption @ServerName, 'data access', 'true'exec sp_serveroption @ServerName, 'rpc', 'false'exec sp_serveroption @ServerName, 'rpc out', 'true'exec sp_serveroption @ServerName, 'use remote collation', 'false'-- Test connectiondeclare @SQL nvarchar(200)set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'exec sp_executesql @sql-- test retreivalselect count (*) from ServerName.master.DBO.sysdatabases[/quote]USE master;GOEXEC sp_addlinkedserver 'servername', N'SQL Server'GOUSE [master]GOEXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'data access', @optvalue=N'true'GOUSE [master]GOEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'servername', @locallogin = NULL , @useself = N'False', @rmtuser = N'dba', @rmtpassword = N'dbapwd'GOnote : dba user has acces on table which u wantFri, 16 Jan 2009 22:31:38 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic473766-146-1.aspxI had the same problem when my login was not a user in the database I was selecting against.Fri, 16 Jan 2009 13:19:29 GMTdebbie.bullhttp://www.sqlservercentral.com/Forums/Topic473766-146-1.aspxThanks guys... This was usefull to me. I had that same issue and this artickle helped me resolve it.Wed, 26 Nov 2008 11:49:17 GMTali-550825http://www.sqlservercentral.com/Forums/Topic473766-146-1.aspxThank you so much! An article is a great idea! :DWed, 26 Mar 2008 07:41:53 GMTsunshine-587009http://www.sqlservercentral.com/Forums/Topic473766-146-1.aspx[quote][b]sunshine (3/25/2008)[/b][hr]It works for sa users, not for Windows Authenticated users. :( They get this error.OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Communication link failure".Msg 10054, Level 16, State 1, Line 0TCP Provider: An existing connection was forcibly closed by the remote host.Msg 18452, Level 14, State 1, Line 0Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Invalid connection string attribute".[/quote]Boy, I should write an article on this one.See my previous post in another thread: [url]http://www.sqlservercentral.com/Forums/FindPost473248.aspx[/url]Wed, 26 Mar 2008 00:42:18 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic473766-146-1.aspxIt works for sa users, not for Windows Authenticated users. :( They get this error.OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Communication link failure".Msg 10054, Level 16, State 1, Line 0TCP Provider: An existing connection was forcibly closed by the remote host.Msg 18452, Level 14, State 1, Line 0Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Invalid connection string attribute".Tue, 25 Mar 2008 09:39:51 GMTsunshine-587009http://www.sqlservercentral.com/Forums/Topic473766-146-1.aspxAgree with RBarry. Adding datasource will resolve issue.Mon, 24 Mar 2008 21:29:22 GMTMohan Kumarhttp://www.sqlservercentral.com/Forums/Topic473766-146-1.aspxTry adding "[font="Courier New"], @datasrc=@ServerName[/font]" to your [font="System"]sp_addlinkedserver[/font] statement.Mon, 24 Mar 2008 17:02:35 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic473766-146-1.aspxI can select from a master table however get this error for all user databases. any idea what is going on? Msg 7314, Level 16, State 1, Line 1The OLE DB provider "SQLNCLI" for linked server "Server Name" does not contain the table ""DBname"."dbo"."sysfiles"". The table either does not exist or the current user does not have permissions on that table. This is the setting from my linked server. declare @ServerName varchar(50)Set @ServerName = 'Server Name'--exec sp_dropserver @ServerNameexec sp_addlinkedserver @server=@ServerName, @srvproduct='', @provider='SQLOLEDB', @provstr='Integrated Security=SSPI;'-- Set optionsexec sp_serveroption @ServerName, 'collation compatible', 'true'exec sp_serveroption @ServerName, 'data access', 'true'exec sp_serveroption @ServerName, 'rpc', 'false'exec sp_serveroption @ServerName, 'rpc out', 'true'exec sp_serveroption @ServerName, 'use remote collation', 'false'-- Test connectiondeclare @SQL nvarchar(200)set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'exec sp_executesql @sql-- test retreivalselect count (*) from ServerName.master.DBO.sysdatabasesMon, 24 Mar 2008 15:58:35 GMTsunshine-587009