Start job from another SQL server instance
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$];
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$]