Start job from another SQL server instance

Question

I have a job on an SQL 2008 server (Server A) (I know this is not ideal... That issue is being addressed). The agent runs under NT AUTHORITY\NETWORK SERVICE.

I want to add a step that runs a job that sits on another SQL 2014 server (Server B).

I would use:

EXEC [Server B].msdb..sp_start_job N'JobName'

Ran from Server A manually it executes the job on Server B fine as expected.

If I add the task to a job on Server A it fails with message:

Executed as user: NT AUTHORITY\NETWORK SERVICE. The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.

I have assigned NT AUTHORITY\NETWORK SERVICE on Server B to the 'TargetServerRole' in msdb and then granted Execute permission to concern user to SP_Start_Job and SP_Stop_Job.

Server A has Server B as a linked server and I tried setting "Local server login to remote server login mappings" Local login NT AUTHORITY\NETWORK SERVICE to impersonate.

The job still fails with the same error.

What do I need to do?

Thanks

Answer 1

You probably don't want to grant direct access to sp_start_job or sp_stop_job, or even grant access to a range of SQL Agent functionality via SQLAgentOperatorRole, to either NT AUTHORITY\NETWORK SERVICE or MyDomain\ServerA$. Doing any of those combinations would, at the very least, allow any process running as NT AUTHORITY\NETWORK SERVICE on ServerA the ability to start and/or stop any job on ServerB.

Using Module Signing, you can set up very granular permissions such that MyDomain\ServerA$ (or any login or role, or any combination of those) can only start and stop that one particular job.

USE [msdb];
GO
CREATE PROCEDURE dbo.StartStopJobX
(
  @Operation VARCHAR(10) = 'start'
)

AS
  IF (@Operation = 'start')
  BEGIN
    EXEC dbo.sp_start_job N'{job_name}';
  END
  ELSE
  BEGIN
    EXEC dbo.sp_stop_job N'{job_name}';
  END;
GO


CREATE CERTIFICATE [SqlAgentPermissions]
 ENCRYPTION BY PASSWORD = 'change_me'
 WITH SUBJECT = 'Allow low-priviledged accounts to start/stop certain jobs',
 EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
  TO dbo.StartStopJobX
  BY CERTIFICATE [SqlAgentPermissions]
  WITH PASSWORD = 'change_me';

CREATE USER [SqlAgentPermissions] FROM CERTIFICATE [SqlAgentPermissions];

ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [SqlAgentPermissions];

Now all you need to do is:

GRANT EXECUTE ON dbo.[StartStopJobX] TO [MyDomain\ServerA$];

Answer 2

NT AUTHORITY\NETWORK SERVICE, is a local account, but it can authenticate on the network using the server's computer account (assuming the server is domain-joined).

You need to create a login on Server B for the computer account of Server A.

Eg if the computer name of Server A is "ServerA", then its computer account is named "ServerA$", so something like:

use msdb

create login [MyDomain\ServerA$] from Windows
create user [MyDomain\ServerA$] for login [MyDomain\ServerA$]
alter role SQLAgentOperatorRole add member [MyDomain\ServerA$]