February 5, 2011 at 4:11 am
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 "...the specified @job_name ('HELLO') does not exist."
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); '
PRINT '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
*/
February 5, 2011 at 6:15 am
avoid using sp_start_job ....
have a look at:
http://www.sqlservercentral.com/scripts/Miscellaneous/31032/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 5, 2011 at 8:54 am
Hello ALZDBA,
Thanks for your response. That's an interesting approach - executing raiserror that will raise an alert that can then start a job - that way I don't have to grant the SQLAgentOperatorRole to my application users.
I will definitely take this method into consideration.
Thanks again.
February 5, 2011 at 1:34 pm
and the big advantage is you have control over the job e.g. at maintenance time disable it or disable the alert.
One word (sentence) of caution:
Keep in mind, when disabling the job, it's best to also disable the alert because if someone raises the alert, it will detect the job is disabled and will retry a couple of times every second. It will register that in sqlagent.out file and that will fill up fairly fast.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 5, 2011 at 11:30 pm
ALZDBA - thanks. i will keep that in mind.
By the way, would you happen to know what's preventing the code in my OP from getting SP_START_JOB to work via a certificate?
February 6, 2011 at 1:38 am
I think your missing link is a LOGIN created from your certificate.
Keep in mind you should also register the certificate in master db to be able to create a login.
Use this login to map to your TestJobCertificateUser at db level (testdb and msdb)
Can you test it ?
(I haven't used certificates except for some small test setups)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2011 at 3:59 am
thanks for getting back to me.
i'll give it a try and get back to you soon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply