4

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

6

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$];
5

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$] 
  • Thank you. A combination of this and the answer by Solomon it is now working. Thanks – Round May 7 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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