How to setup Link Server properly

Hi,

I want to access another database seating on another server so I configured the Linked server with SQL Server as the server type. I set the security as NT_AUTHORITY\SYSTEM and checked the Impersonate. In Server Options, I set the RPC Out to true then I clicked ok.

I got this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB Provider "SQLNCLI" for linked server "server1" reported an error. Authentication failed.

Cannot initialize the datasource object of OLE DB Provider "SQLNCLI" for linked server "server1".

OLE DB Provider "SQLNCLI" for linked server "server1" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)

Note that my NT login is added as "Administrators" on the Server i want to connect to.

Have I missed anything?

cherriesh

I tend to use sp_addlinkedserver and sp_addlinkedsrvlogin for this.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

http://msdn2.microsoft.com/en-us/library/ms189811.aspx

The trick is to specify 'true' for @.useself for sp_addlinkedsrvlogin.

e.g.

Code Snippet

exec sp_addlinkedsrvlogin 'remotesrv', 'true'