CODEWITHSUNDEEP
sql-server
David Shochet
David Shochet

Reputation: 5395

Copying between servers

I need to copy data from a table on one server to the same table on another server. I tried this:

sp_addlinkedserver 'server1' 

INSERT INTO [server2].[R2Op].dbo.[RefR2Ops]
SELECT * FROM server1.[R2Op].dbo.[RefR2Ops]

But I am getting an error on the second statement:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Could you please tell how I can fix that? Thanks.

Upvotes: 0

Views: 84

Answers (2)

Mithrandir
Mithrandir

Reputation: 25397

If both servers are in the same ADS domain and your login in present on both servers, you'll probably have to add:

  EXEC sp_addlinkedsrvlogin 'server1', 'true'

Otherwise check in which security context the connections to the linked server will be made.

Upvotes: 1

Preet Sangha
Preet Sangha

Reputation: 65556

The answer is there. You have not given rights for the local service use to login remote server. You're command is running as default under the rights of NT AUTHORITY\ANONYMOUS LOGON.

you need to specfy the user credentials to access the linked server

see sp_addlinkedsrvlogin

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 
    [ , [ @useself = ] 'useself' ] 
    [ , [ @locallogin = ] 'locallogin' ] 
    [ , [ @rmtuser = ] 'rmtuser' ] 
    [ , [ @rmtpassword = ] 'rmtpassword' ]

Upvotes: 1

Related Questions