none
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
    */
    

    • Edited by Idev Saturday, February 05, 2011 11:22 AM
    Friday, February 04, 2011 9:33 PM

All replies

  • 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
    Friday, February 04, 2011 11:25 PM
  • 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.
    Saturday, February 05, 2011 8:10 AM
  • 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/
    Sunday, February 06, 2011 8:12 AM
  • 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/
    Sunday, February 06, 2011 8:12 AM
  • 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?
    Saturday, June 04, 2011 2:15 AM
  • 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

     


    • Proposed as answer by kaborka Tuesday, June 07, 2011 2:19 AM
    Monday, June 06, 2011 1:28 PM
  • 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!


    Tuesday, June 07, 2011 2:19 AM
  • kaborka,

     

    can explain how can i call ssis job from SP.

    THATS VERY HELPFULL FOR ME

    Sunday, September 25, 2011 9:38 AM
  • 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.
    Saturday, October 01, 2011 9:31 PM
  • 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

     


    • Edited by Rayzer Friday, December 23, 2011 12:31 PM
    Friday, December 23, 2011 12:30 PM
  • 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, esquel@sommarskog.se
    • Proposed as answer by Matija LahMVP Monday, December 26, 2011 3:27 PM
    Friday, December 23, 2011 10:01 PM