Job Fails on Linked Server - Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)

  • Article

Problem: Having a SQL Agent Job, which executes a Stored prcedure to connect to a remote SQL server via linked server and extract/manupulate the data.

The SP runs successfully in SSMS but get failed, if run through an agent Job.

NOTE: A Windows a/c is used here to connect to remote server and the a/c is having dbo permission in both source and remote server databases. Both the servers are on same domain.

Resolution:

Step -1:
a. Make sure that the a/c used in linked server/Job having necessary permissions on both source and remote server databases.
b. Remove the run as "Login a/c" for the Job step
c. Remove the Login a/c, if it defined under "Local Server Login to Remote Server Login Mappings" to impersonate.
d. In Linked Server properties, Choose "Be made using the Login's current security context",if the a/c having permissions on remote server and is used to run the SQL Agent Service else choose "Be made using this security context" and define the Remote Login and password.