none
Job Fails on Linked Server RRS feed

  • Question

  • I've got a job running on a SQL2005 box (server07) that modifies data on a SQL2000 box (server05).  The databases on the 2005 were imported from an older box running SQL2000.  The step that is failing is:

    update server05.xtender.sysop.ae_rf4
    set field3 = sname
    from server05.xtender.sysop.ae_rf4 x inner join allscholarships..studentsindb_table s
    on x.field1 = s.ssn
    where x.field3 is null or len(field3) = 0

    The history shows this for the failed step:

    Message
    Executed as user: Domain\Administrator. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)Unable to open Step output file.  The step failed.

    Domain\Administrator is a sysadmin on both boxes.  This job had been running some time ago, but the SQL2000 box (under a different dba) has somehow changed.  Now I need to get the thing running again.
    Tuesday, December 12, 2006 8:17 PM

Answers

  • a very weird issue. Was able to resolve it using a new NT Account with Admin rights. Just created it at both servers, then added to both SQL Servers with rights I needed. And also set this account to be used for SQL Agent service.

    Oh, also have used the imporsonate setting for linked servers mapping (at both sides)

    and just leave "run as user" blank for step settings

    Saturday, February 24, 2007 8:21 AM

All replies

  • Hi,

    The error said its untrusted connection ... have you tried re mapping accounts using sp_addlinkedsrvlogin!!!?

    refer this thread http://www.webservertalk.com/archive137-2006-4-1477449.html

    Hemantgiri S. Goswami

    Wednesday, December 13, 2006 5:56 AM
    Moderator
  • That discussion is the only thing that pops up when I google "Error 15274", I've read the very original and he hasn't gotten an answer yet.

    The best I can tell is that the SQL Agent is supplying the security credentials to the linked server and I'm not able to specify which security context for the job to use.

    In the Job Step Properties pages, under each step's General page in the middle of the window is a dropdown - Run As.  There is nothing in the dropdown list and no way to enter a value.  On the Advanced page of each step is a Run As User with a select user button, I've entered every conceivable value on this Advanced page with no success.
    Wednesday, December 13, 2006 6:21 PM
  • Check services - which account is used for startup of the SQL agent. System account is no good - only local resources are available. Use "This account" with administrative rights on the Windows & SQL server.
    Tuesday, December 19, 2006 6:25 AM
  • Make sure Domain\Administrator has got required privilges on SQL Server (incl. SQLAgent) in order to complete the job process.
    Tuesday, December 19, 2006 12:11 PM
    Moderator
  • Also check the linked server setup - security. Check "Be made using the login's current security context".
    Wednesday, December 20, 2006 5:35 PM
  • a very weird issue. Was able to resolve it using a new NT Account with Admin rights. Just created it at both servers, then added to both SQL Servers with rights I needed. And also set this account to be used for SQL Agent service.

    Oh, also have used the imporsonate setting for linked servers mapping (at both sides)

    and just leave "run as user" blank for step settings

    Saturday, February 24, 2007 8:21 AM
  • check the connectivity of your linked server using sp_testlinkedserver
    Monday, February 26, 2007 2:44 PM
  • a very weird issue. Was able to resolve it using a new NT Account with Admin rights. Just created it at both servers, then added to both SQL Servers with rights I needed. And also set this account to be used for SQL Agent service.

    Oh, also have used the imporsonate setting for linked servers mapping (at both sides)

    and just leave "run as user" blank for step settings


    Saved the day
    Withers
    Tuesday, January 5, 2010 5:19 PM
  • I had the same error message on one of my SQL jobs.  I found that the OWNER of the SQL Job (on the general page of the job's properties) made a difference. When I changed the owner from sa to a Windows account with administrator rights on the machine the job started working. It wasn't even a domain account; it was a local administrator.
    • Proposed as answer by Vasantha Praba Tuesday, February 14, 2012 6:49 AM
    Thursday, January 27, 2011 10:33 PM
  • Thanks Magoo- switching the owner totally solved my problem!
    Thursday, February 10, 2011 10:35 PM