Del via

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

  • Artikel

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.

Comments

  • Anonymous
    July 29, 2011
    +1 for the accurate resolution on this one.  Many thanks!

  • Anonymous
    July 26, 2012
    This was a HUGE HELP!!  Thank you!!  Worked for me...perfect directions!!

  • Anonymous
    March 27, 2013
    what is 'a/c'?   in the phrase 'NOTE: A Windows a/c'? Account credentials? authentication something?

  • Anonymous
    September 25, 2013
    There is a known bug with saved windows credentials accessing a remote server.  You have to authenticate with SQL credentails

  1.  Right click the SQL instance of the remote server in your Management Studio and go to Properties
  2. On the security tab, click "SQL Server and Windows Authentication mode
  3.  Restart the SQL service so the settings take effect
  4.  Create a new SQL logon on the remote server using SQL authenication and grant it rights to the database you need it to access.
  5.  Go back to the server you want the SQL Server Agent Job to run.
  6.  Expand server objects->Linked Servers and either create a new link or edit it.  
  7.  On the security page, choose "Be made using this security context"
  8.  Enter the remote logon name and password for the remote server you created in step 4
  9.  Edit your SQL Agent Job Step and make sure both "Run As" options are blank.  
  10.  Reference your remote table in your query using brackets around the connection which references a specific instance.  I used this notation in my "from" statement and then didn't have to reference it during select statement or where clauses.   [ServerNameInstanceName].database.dbo.table

  • Anonymous
    October 24, 2013
    Good job, Thanks for sharing sample.

  • Anonymous
    April 29, 2014
    Perfect solution !!!!!! Save lot of precious time

  • Anonymous
    September 03, 2014
    Thanks a lot. In SQL Server 2000 their is no such problem but in SQL 2k5 your solution is perfectly working.

  • Anonymous
    April 09, 2015
    Thank you for that concise answer, this worked on SQL Server 2012.