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
Best Answer
You probably don't want to grant direct access to
sp_start_job
orsp_stop_job
, or even grant access to a range of SQL Agent functionality viaSQLAgentOperatorRole
, to eitherNT AUTHORITY\NETWORK SERVICE
orMyDomain\ServerA$
. Doing any of those combinations would, at the very least, allow any process running asNT 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.Now all you need to do is: