Access to the remote server is denied because the current security context is not trusted, SQL server agent

Question

I execute the following statements on the management studio perfectly, with out any problem.

Execute sp_addlinkedserver 'remoteservername'


execute sp_addlinkedsrvlogin @rmtsrvname = N'remoteservername', @locallogin = N'sa', @useself = N'False', 
@rmtuser = N'remoteuserlogin', @rmtpassword = N'remoteuserpwd'


select * into local_table_bkup FROM [remoteservername].remotedb.dbo.table_name

But when I try to use them in the SQL server agent job schedule, the following query does not work in T-SQL and throws the error

Executed as user: dbo. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)

select * into local_table_bkup FROM [remoteservername].remotedb.dbo.table_name

Could some one please help me out?

I have loogged in as user sa .I am running the job on my local server.

Answer 1

try this:

USE [master]

GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LINK_SERVER', @locallogin = N'sa', @useself = N'False', @rmtuser = N'sa', @rmtpassword = N'123456'

GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'**LINK_SERVER**', @locallogin = **NULL** , @useself = N'**False**'

GO