Cannot execute sp_start_job using certificate

Question

I am trying to provide my database, [TestDB], the ability to execute sql server jobs via certificate signing (while having [TestDB] trustworthy bit set to OFF).  My current implementation attempt (below), allows [TestDB] to successfully read from MSDB..SYSJOBS_VIEW via certificate, but FAILS to execute MSDB..SP_START_JOB using the same methodology - i get an error "error # 14262...the specified @job_name ('HELLO') does not exist."  Behind the scenes, sp_start_job executes sp_verify_job_identifiers which attempts to select from msdb..sysjobs_view where (name = @job_name); however no rows get returned, hence error # 14262 gets raised.

Your mission, should you choose to accept, is to get MSDB..SP_START_JOB to work with a certificate! MUCH Thanks in advance.

Using SQL Server 2005, you can immediately reproduce this problem by running the code below. Just copy/paste/run it directly.  For your convenience, I've added code to remove the created objects which are commented out at the very end.

PRINT '1. Create Database [TestDB]'
--=======================================================================================
CREATE DATABASE [TestDB];
GO
USE [TestDB];

CREATE LOGIN [testdb_owner] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
CREATE USER [testdb_owner] FROM LOGIN [testdb_owner]
--make user db_owner
EXEC sp_addrolemember N'db_owner', N'testdb_owner'
GO

CREATE PROCEDURE dbo.SELECT_ALL_JOBS
AS	
	select * from msdb.dbo.sysjobs_view
GO

CREATE PROCEDURE dbo.START_HELLO_JOB
AS
	EXEC MSDB.DBO.sp_start_job @JOB_NAME = 'Hello'
GO

--End of create [TestDB]
--=======================================================================================

PRINT '2. Create/Install Certificate on [TestDB]'
--=======================================================================================
USE [TestDB];

CREATE CERTIFICATE [TestJobCertificate]
ENCRYPTION BY PASSWORD = '123' --password of the private key
WITH SUBJECT = 'Certificate to view and execute jobs',
START_DATE = '20110101', EXPIRY_DATE = '20990101';	
	
BACKUP CERTIFICATE [TestJobCertificate]
TO FILE = 'D:\TestJobCertificate.CER'
WITH PRIVATE KEY (FILE = 'D:\TestJobCertificate.PVK',
     ENCRYPTION BY PASSWORD = '123',
				 DECRYPTION BY PASSWORD = '123');
GO

--Create the certificate user
CREATE USER TestJobCertificateUser FROM CERTIFICATE [TestJobCertificate]
GO

--sign stored proc with certificate
ADD SIGNATURE TO OBJECT::[SELECT_ALL_JOBS]
BY CERTIFICATE [TestJobCertificate] 
WITH PASSWORD = '123'; --password of the private key

GRANT EXECUTE ON SELECT_ALL_JOBS TO TestJobCertificateUser
	
--sign stored proc with certificate
ADD SIGNATURE TO OBJECT::[START_HELLO_JOB]
BY CERTIFICATE [TestJobCertificate] 
WITH PASSWORD = '123'; --password of the private key

GRANT EXECUTE ON START_HELLO_JOB TO TestJobCertificateUser
--=======================================================================================
GO


PRINT '3. Create/Install Certificate on [MSDB]'
--=======================================================================================
USE [MSDB];
GO

--Create 'HELLO' JOB
--=============================================================================
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Hello', 
		@enabled=1, 		 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Hello', 
		@step_id=1,
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'select ''HELLO''', 
		@database_name=N'msdb', 
		@flags=0

EXEC msdb.dbo.sp_add_jobserver @job_name=N'Hello' 
--=============================================================================

--CREATE CERTIFICATE FROM FILE
CREATE CERTIFICATE [TestJobCertificate]
FROM FILE = 'D:\TestJobCertificate.CER'
WITH PRIVATE KEY (FILE = 'D:\TestJobCertificate.PVK',
				 ENCRYPTION BY PASSWORD = '123',
				 DECRYPTION BY PASSWORD = '123');

--Create the certificate user
CREATE USER TestJobCertificateUser FROM CERTIFICATE [TestJobCertificate]
GO

--GRANT NECESSARY PERMISSIONS REQUIRED TO VIEW/EXECUTE JOBS
--THIS DATABASE ROLE IS REQUIRED IN ORDER FOR THE CERTIFICATE USER TO START JOBS HE DOES NOT OWN
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'TestJobCertificateUser'
GO

--ADDITIONAL PERMISSIONS FOR TESTING
GRANT EXECUTE ON MSDB.DBO.sp_verify_job_identifiers TO [TestJobCertificateUser]
GRANT SELECT ON MSDB..SYSJOBS_VIEW TO [TestJobCertificateUser]
GRANT SELECT ON MSDB..SYSJOBS TO [TestJobCertificateUser]
GRANT EXECUTE ON MSDB.DBO.sp_start_job TO [TestJobCertificateUser]
GO


PRINT 'SETUP IS NOW COMPLETE AT THIS POINT...'

--=====================================================
PRINT '4. Testing Impersonation...'
--=====================================================
USE [TestDB];
GO
execute as login = 'testdb_owner'
select suser_name() as [context]

--this stmt will execute fine and return all records from msdb.dbo.sysjobs_view via the certificate
exec SELECT_ALL_JOBS

PRINT 'the following stmt will throw an error:'
PRINT 'Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67'
PRINT 'The specified @job_name (''HELLO'') does not exist.'
PRINT 'however, if you run under the context of ''sa'', it will execute fine (but that defeats the purpose of certificate signing)'
PRINT 'MSDB.DBO.sp_verify_job_identifiers, is where the error gets raised. sp_verify_job_identifiers attempts to select from msdb.dbo.sysjobs_view where (name = @job_name); however no rows are returned, hence error # 14262 gets raised. On the other hand, when you selected from msdb.dbo.sysjobs_view through the stored proc, SELECT_ALL_JOBS, that is signed by the certificate, the rows DO get returned.'
PRINT '------------------------'
exec START_HELLO_JOB
PRINT '------------------------'

revert;


--CLEANUP CODE
/*
USE [MSDB];
GO
DROP USER [TestJobCertificateUser]
DROP CERTIFICATE [TestJobCertificate]

EXEC msdb.dbo.sp_delete_job @job_name='Hello'
GO

DROP Login [testdb_owner]
DROP DATABASE [TestDB]

--Delete certificate and private key from file
*/

Answer 1

The origin of the error is actually the sp_verify_job_identifiers system procedure, when trying to read from the dbo.sysjobs_view.

Based on the procedures' definitions, I guess you need to grant additional permissions to the certificate-based user. Try adding them one by one, to see which ones are actually needed.

grant select on msdb.dbo.sysjobs_view to TestJobCertificateUser
grant select on msdb.dbo.sysjobservers to TestJobCertificateUser
grant select on msdb.dbo.sysjobsteps to TestJobCertificateUser
grant select on msdb.dbo.systargetservers to TestJobCertificateUser
grant select on msdb.dbo.sysschedules to TestJobCertificateUser
grant select on msdb.dbo.sysalerts to TestJobCertificateUser

A question from me: why don't you simply grant the execute permission on the sp_start_job system procedure to the users?

 

---

ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Answer 2

Hello Matijah,

Thanks for your response.

I've tried granting ALL the permissions that you mentioned including db_owner, but unfortunately sp_start_job still does not work.   You are correct, MSDB.DBO.sp_verify_job_identifiers, is where the error gets raised. sp_verify_job_identifiers attempts to select from msdb.dbo.sysjobs_view where (name = @job_name); however no rows are returned, hence error # 14262 gets raised.  On the other hand, when I select from the msdb.dbo.sysjobs_view through the stored proc, SELECT_ALL_JOBS, that is signed by the certificate, the rows DO get returned.

Could you please do me a favor and run the code in my original post?  You can just copy/paste and run it directly.  After that, you can try adding additional permissions to see if you can get the "exec START_HELLO_JOB" to work in the context of "testdb_owner."

MUCH Thanks in advance.

Answer 3

Running copy-paste script I got

Job 'Hello' started successfully.

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Running copy-paste script I got

Job 'Hello' started successfully.

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

I have the same problem and get the same results as Idev.  Is there a solution to this that will allow a sp in an app db, executed by a non-admin user, to start a job?

Answer 6

I have not tried to do what you ask, starting a SQL Agent job from a signed stores procedure in an application database.  If you are willing to put a stored procedure into msdb, though, you should be able to get it to work.  Sample code:

USE msdb

GO



CREATE PROCEDURE dbo.StartAgentJob

@Job_Name NVARCHAR(128)

WITH EXECUTE AS OWNER

AS

SET NOCOUNT ON



DECLARE @Allowed INT

SET @Allowed = 0



/* Table variable to determine who can start a job */

DECLARE @JobMap TABLE 

(job_name NVARCHAR(128),

 group_name NVARCHAR(256))

/* Populate the table of allowed groups for a job */

INSERT INTO @JobMap Values (N'Test it out',N'Domain\Group')

INSERT INTO @JobMap Values (N'Another job',N'Domain\OtherGroup')



/* Since this runs as 'sa' need to check membership of original login */

EXECUTE AS LOGIN = ORIGINAL_LOGIN()

IF EXISTS (SELECT * FROM @JobMap

  WHERE job_name = @Job_Name

  AND IS_MEMBER(group_name) = 1 )

 SET @Allowed = 1

REVERT

/* Back to 'sa' so that we can start the job. */



IF @Allowed = 1 

 EXEC sp_start_job @job_name = @Job_Name

ELSE

 PRINT 'Invalid attempt to start ''' + QUOTENAME(@Job_Name)+''''

RETURN

go



GRANT EXECUTE ON dbo.StartAgentJob to SQLAgentUserRole

As you can see, this is running as the owner of msdb, which is 'sa', so the procedure has enough rights to start any job.  The code to populate a table variable with the names of the jobs and users that this procedure will proxy is in order to keep the door closed to adhoc requests.

The user who runs the stored procedure needs to have some access to msdb, so choose a role that you consider harmless enough to let him use.

FWIW,
RLF

 

Answer 7

Russell:  That worked perfectly!  I added the sp to MSDB and granted execute to the certificate user (TestJobCertificateUser in the OP).  The signed sp in the app db could then launch the job by calling its MSDB counterpart instead of sp_start_job.

Thank you very very much!

Answer 8

kaborka,

 

can explain how can i call ssis job from SP.

THATS VERY HELPFULL FOR ME

Answer 9

kaborka,

can explain how can i call ssis job from SP.

THATS VERY HELPFULL FOR ME

I did not read your question carefully.  To invoke a SSIS job, simply create a SQL Agent job that runs the SSIS job, then use the method described above to start the agent job from the target database.

Answer 10

I had the same issue and got to the point where the msdb.dbo.sysjobs_view was not returning any records.

I ran the following and it sorted all the problems. the SQLAgentReaderRole allow the msdb.dbo.sysjobs_view to actually return records.

EXEC sp_addrolemember N'SQLAgentReaderRole', N'Fred'

GO

EXEC sp_addrolemember N'SQLAgentOperatorRole', N'Fred'

GO

 

Answer 11

I missed the thread when it was on originally. But it possible to do what is asked for in the initial question. You need to countersign sp_start_job and two procedures it calls.

I have a complete example in this article on my web site:
http://www.sommarskog.se/grantperm.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]