Linked Server Security under SQL Agent Job Context I have two SQL servers V1 and V2. Both 2012.
V1 has linked server connection to V2.
There are two Windows accounts: one running SQL server, the other
running the Agent service. The two accounts are common to V1 and V2.
I have a SQL Agent job that is owned by a non-sysadmin login, an SQL Server account. This account is also present on both servers with the same name and password.
On Linked Server Properties, Security tab, there are no explicit mappings. The default then is set to "Be made using the login's current security context"


Here's the problem.

I run SQL statement against V2 from V1 in SSMS as the non-sysadmin login and it works. However, when I create a job owned by the login with the same query, it fails with:


  Executed as user: NONSYSADMIN_USER.
  Linked servers cannot be used under impersonation without a mapping for the impersonated login.
  [SQLSTATE 42000] (Error 7437).  The step failed.


Then I run in SSMS with EXECUTE AS which results in pretty much the same:


  Msg 7437, Level 16, State 1...
  Linked servers cannot be used under impersonation without a mapping for the impersonated login.


I then schedule another job which is owned by sysadmin, running the same query and that works.

From my understanding, if the job is owned by sysadmin, there is no impersonation, the job runs (and steps as long as there is no run as or proxy set) as the Agent service account.  

So, my question is, shouldn't the sysadmin owned job also fail since it's using Windows account, and as far as I know there isn't any delegation configured for the account. Or does the fact that it works indicate that the delegation was configured?

My ultimate goal is to have a job that is owned by a non-sysadmin login, which is a windows account, that runs queries against the linked server. Will this work?

According to the Microsoft's KB article, it would not without employing workarounds. Can anyone confirm it?

Here are some of the references I've used (among others):


SQL Server Agent jobs and user contexts, Tibor Karaszi's post on sqlblog.com
Linked Server Properties (Security Page) on TechNet
Put NT AUTHORITY\SYSTEM user in sysadmin server role
Add this user into linked server login mappings with remote user name and pwd