This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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?
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.
Setting up the proxy user did the trick. Thank you.
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.
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.
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.
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.