sunshine (3/25/2008)

---

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 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login 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".

sunshine (3/24/2008)

---

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 1

The 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 @ServerName

exec sp_addlinkedserver @server=@ServerName, @srvproduct='', @provider='SQLOLEDB', @provstr='Integrated Security=SSPI;'

-- Set options

exec 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 connection

declare @SQL nvarchar(200)

set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'

exec sp_executesql @sql

-- test retreival

select count (*) from ServerName.master.DBO.sysdatabases