SQL Agent job by non-admin : Linked servers cannot be used under impersonation without a mapping for the impersonated login

Question

On SQL 2014 all users can run queries in SSMS using linked servers without issue. However since they are not sysadmins, their SQL Agent job fails with: “Linked servers cannot be used under impersonation without a mapping for the impersonated login”
Linked server is configured for “Be made using the login’s current security context”.
I tried adding in linked Server Security tab, Local server login to remote server login mappings, the AD account of the of the Local Login and checked “impersonate”, but still does not work.
I can reproduce in SSMS by running execute as login before the linked server query since linked server generates an "execute as login" as i understood behind the scenes.
I do not want to use SQL auth for the remote User, but just the local AD account impersonate for the remote user, basically same as running SSMS query.
What is the solution, and am I missing anything on the AD side?

Thanks,

Answer 1

1. Correct, you must absolutely never agree to let sysadmin run jobs which executes code which can be modified by non-sysadmin users.
2. Well, it is not impersonation. It is a login. The user you set for the proxy should not be an account for a real user. Technically it could be, but since you need to store the password for the proxy account in SQL Server, it is obviously not a very good idea.
3. Yes, these are your options. If you let them into Agent, you need to work with proxies. Or you keep them out entirely, and the users will need solve the problem on their own with Task Scheduler, or a user-written service..

Answer 2

You set it up as a CmdExec job using a proxy. In this case, there is a real login to Windows, so there are no restrictions with linked servers. Here are the steps, from an article I have not published yet:

1. The AD admin creates a Windows login and adds it to the AD group(s) needed for the application in question.
2. You create a credential in SQL Server for this login: CREATE CREDENTIAL ApplJobRunner WITH IDENTITY = 'DOMAIN\ApplLogin', SECRET='password' ApplJobRunner is just a name and you can choose whatever you like. The Windows user and the password is what you got from the domain admin.
3. In Object Explorer, you go to the node for SQL Server Agent and then to Proxies. Select Operating System (CmdExec) and then New from the context menu. Enter Proxy name (a name you choose) and the name of the credential from step 2.
4. On the first page, set sa or some other non-person who is sysadmin as the job owner.
5. When you create the job step, you change the job type to Operating system (CmdExec) and then from the Run as dropdown, select the proxy you created in step 3.
6. The text for the job step would be something like this: SQLCMD -I -b -S $(ESCAPE_DQUOTE(SRVR)) -d dbname -Q"EXEC somesp" (The ‑I option forces QUOTED_IDENTIFIER to be ON. The ‑b option ensures that if the procedure fails, the job will also be reported as failed. The ‑S option is followed by an Agent token which expands to Server\Instance. This saves you from having to edit the job step if the database and the job is moved to a different server.)

Answer 3

Hi @rodrag ,

>Linked servers cannot be used under impersonation without a mapping for the impersonated login

Transact-SQL job step runs as the owner of the job step if the owner of the job step is not a member of the sysadmin fixed server role. SQL Agent uses Execute as Login to execute the job step under the context of the owner of the job step. You cannot use EXECUTE AS statement across server boundaries. This behavior is by design. For more information, see the following SQL Server Books Online:
SQL Agent job that executes a distributed query may fail with 65535, 782 or 7437 error messages

Hope this helps.