Execute sp_start_job using certificate

Question

Hi everyone,

I have a stored proc I created in my database that is meant to run some processes for me and then start an SQL Agent Job using the sp_start_job.

I was able to run this successfully because I am a sysadmin. However, I need a user with no admin right to be able to execute this stored proc. I came across an article that mentioned that a certificate can be used to manage the permission I don't want to use impersonation or cross database authentication to do this because of other security reasons.

I have created the certificate and granted access to the sp_start_job in msdb and added the following roles (SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole, TargetServersRole). However, I am still gettting the following error: The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

Anyone with similar experience that could assist.

Thanks@!

Answer 1

I write about this here: http://www.sommarskog.se/grantperm-appendix.html#startjobs

In this chapter I discuss several solutions whereof one is based on certificates only, and the others consist of a combination of certificates and impersonation. And I don't really recommend the all-certificate solution, as it requires you to (counter)sign sp_start_job and two more procedures.

Answer 2

Hi @tobz ,

Thank you so much for posting here.

You could counter-sign a few more stored procedures within msdb. Once done then your user is able to execute the proc, which kicks off the job.

ADD COUNTER SIGNATURE TO sp_start_job BY CERTIFICATE JOBNAME  
    WITH PASSWORD = 'password'  
ADD COUNTER SIGNATURE TO sp_verify_job_identifiers BY CERTIFICATE JOBNAME  
    WITH PASSWORD = 'password'  
ADD COUNTER SIGNATURE TO sp_sqlagent_notify BY CERTIFICATE JOBNAME  
    WITH PASSWORD = 'password'  
go  

You also need to register the certificate in master db to be able to create a login. Use this login to map to your user at db level (yourdb and msdb).

Besides, you could also use a combination of [SQLAgentOperatorRole] and your own procedure. Meaning: Make the user member of that role and then deny execute on sp_startjob (and other procs). This way you can grant the execute via your own proc and circumvent the hard-coded check by giving him the [SQLAgentOperatorRole].

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet