1
  • 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):

Improve this question

1 Answer 1

Reset to default
1
  1. Put NT AUTHORITY\SYSTEM user in sysadmin server role
  2. Add this user into linked server login mappings with remote user name and pwd
Improve this answer
1
  • 2
    Upvote, still a bad solution security wise..
    – Spörri
    Commented Apr 21, 2015 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.