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
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?
Matija Lah, SQL Server MVP
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.
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.
- Proposed as answer by kaborka Tuesday, June 07, 2011 2:19 AM
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!
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.
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'
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'Fred'
- Edited by Rayzer Friday, December 23, 2011 12:31 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:
Erland Sommarskog, SQL Server MVP, email@example.com
- Proposed as answer by Matija LahMVP Monday, December 26, 2011 3:27 PM