Call SQL Agent Job on different instance

Mark Sanchez 101

I have a SQL Job on a 2008 R2 instance and would like as it's final step to start another SQL job on a SQL 2019 instance. Is this possible?

Accepted answer

Erland Sommarskog 100.8K Reputation points MVP

2021-09-22T22:10:07.513+00:00

You can set up a CmdExec job that connects with SQLCMD to the other instance and runs sp_start_job. You would have create a proxy user that has permissions to start jobs on the other instance, and you would select this user as Run As for the job.
Please sign in to rate this answer.

1 person found this answer helpful.
Mark Sanchez 101 Reputation points

2021-10-13T14:41:15.297+00:00

Setting up the proxy user did the trick. Thank you.
Sign in to comment

1 additional answer

AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor

2021-09-23T06:03:45.86+00:00
Hi MarkSanchez-5028,
You also can try to configure a linked server to Server 2 in SQL Server 1, and use the linked server with executing msdb.dbo.sp_start_job to call the SQL Server job in Server 2 as the final step of Server 1 job.

EXEC [Server 2].[msdb].[dbo].[sp_start_job] @job_name = N'jobname';

Please refer to How to Create a SQL Server Agent Job that Calls Another Job and How to start SQL Agent job from Another server for more details.

Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.

1 person found this answer helpful.
Erland Sommarskog 100.8K Reputation points MVP

2021-09-23T21:24:24.403+00:00

I thought of this first, but this solution comes with security problems that are difficult to iron out. If you set up the server with self-mapping, this will fail if SQL Server Agents as an NT SERVICE account. If you sent up login mapping to an SQL account on the remote machine, anyone on the local server can use that login to start jobs on the SQL 2019 box.

Therefore, I think the CmdExec solution is better.

Mark Sanchez 101 Reputation points

2021-09-27T15:09:30.337+00:00

Hello,

Thanks for the response. I carefully went over the steps in the referenced video and post. Two items I definitely did wrong were 1) Using the @servername parameter in sp_start_job, instead of qualifying the call to [Server2].[msdb].[dbo].[sp_start_job], and 2) Not having the "RPC Out" setting in my linked server set to "True".

Now I am getting another error; Namely:

Executed as user: NT AUTHORITY\NETWORK SERVICE. Login failed for user 'Domain\CallingServerName$'. [SQLSTATE 28000] (Error 18456). The step failed.

I also have the following posted question open about my linked server and wonder if this is a related issue:
https://learn.microsoft.com/en-us/answers/questions/562781/linked-server-login-failed-for-user-39nt-authority.html

I will be reviewing the answers posted there with my admin to find out.

Erland Sommarskog 100.8K Reputation points MVP

2021-09-27T21:13:41.28+00:00

Yes, there is a relation to that post, but the resolution is not the same. In that post, you had a double-hop problem. This is not the case here. The case here is that you have a login which is local to the machine, and therefore is not valid on the other server. That could be resolved by adding Domain\Machine$ as an account on the other server, but I recommend against that.

Instead the way to go is to pursue my solution above the proxy. This is just one of the problems with using a linked server for this problem.

I've written about how to set up proxies here: https://www.sommarskog.se/perm-hijack.html#agentjobs.
Sign in to comment