CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
gruber
gruber

Reputation: 29727

copy data from sql server 2005 instance to other sql server 2008 instance

Im using code like below:

EXEC sp_addlinkedserver 'MyServer';

GO

exec sp_addlinkedsrvlogin 'MyServer', 'true';

GO

but then when I try to select sth from that server and insert it into server from which im executing query there is:

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

Does anyone know where the problem is ?

Upvotes: 1

Views: 201

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

It fails because it's trying to impersonate the local login when it goes to the remote server, and the local login (NT AUTHORITY\ANONYMOUS LOGON) isn't valid on the remote server.

Instead of impersonating the local login, I would create a login on the remote server using SQL Server Authentication, that has the appropriate permissions for what you're trying to do, and then explicitly specify that username and password for the linked server.

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'MyServer',
    @useself = false,
    @rmtuser = 'YourRemoteUsername',
    @rmtpassword = 'YourRemotePassword'

Upvotes: 1

Related Questions