Calling sp_start_job from a stored procedure

Question

Our developers need to be able to start a SQL Server Agent job from their .Net code. I know I can call msdb..sp_start_job to do just that, but I don't want to give general user accounts direct access to run jobs.

What I'd like to do is to create a stored procedure in the application's database using the WITH EXECUTE AS clause to impersonate a proxy account. The procedure as we have it is:

CREATE PROCEDURE dbo.StartAgentJob 
    WITH EXECUTE AS 'agentProxy'
AS
BEGIN
    EXEC msdb.dbo.sp_start_job N'RunThisJob';
END

When we run this, though, we get the following message:

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

Any ideas? Is this even the best way to do this in SQL2005?

Answer 1

I'm glad you solved this, but ownership chaining is not the recommended solution. Since you seem validly concerned about security and proper granularity of the rights involved, I'm adding this reply, although late, as a reference to what's happening and how to resolve this problems.

EXECUTE AS impersonation scope

The EXECUTE AS clauses come in two flavors: EXECUTE AS LOGIN and EXECUTE AS USER. The EXECUTE AS LOGIN is authenticated by the server and is an impersonation context trusted by the entire SQL instance (server-scoped):

When impersonating a principal by using the EXECUTE AS LOGIN statement, or within a server-scoped module by using the EXECUTE AS clause, the scope of the impersonation is server-wide. This means that after the context switch, any resource within the server that the impersonated login has permissions on can be accessed.

EXECUTE AS USER is authenticated by the database and is an impersonation context trusted only by that database (database-scoped):

However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

A stored procedure that has an EXECUTE AS clause will create a database scoped impersonation context, and as such will be unable to reference objects outside the database, case in point being you will not be able to reference msdb.dbo.sp_start_job because is in msdb. There are many other examples available, like trying to access a server scope DMV, trying to use a linked server or trying to deliver a Service Broker message into another database.

The enable a database scoped impersonation to access a resource that would not be normally allowed the authenticator of the impersonation context has to be trusted. For a database scoped impersonation the authenticator is the database dbo. This can be achieved by two possible means:

• By turning on the TRUSTWORTHY property on the database that authenticated the impersonation context (ie. the database where the EXECUTE AS clause was issued in).
• By using code signatures.

These details are described in MSDN: Extending Database Impersonation by Using EXECUTE AS.

When you resolved the issue via cross database ownership chaining you have enabled the cross-db chaining at the entire server level, which is considered a security risk. The most controlled, fine grained way to achieve the desired result is to use code signing:

• In the application database create a self signed certificate
• sign the dbo.StartAgentJob with this certificate
• drop the private key of the certificate
• export the certificate to disk
• import the certificate into msdb
• create a derived user from the imported certificate in msdb
• grant AUTHENTICATE permission to the derived user in msdb

These steps ensure that the EXECUTE AS context of the dbo.StartAgentJob procedure is now trusted in msdb, because the context is signed by a principal that has AUTHENTICATE permission in msdb. This solves half of the puzzle. The other half is to actually grant the EXECUTE permission on msdb.dbo.sp_start_job to the now trusted impersonation context. There are several ways how this can be done:

1. map the impersonated user agentProxy user in msdb and grant him execute permission on msdb.dbo.sp_start_job
2. grant the execute permission to the msdb authenticator certificate derived user
3. add a new signature to the procedure, derive a user for it in msdb and grant the execute permission to this derived user

Option 1. is simple, but has a big disadvantage: the agentProxy user can now execute the msdb.dbo.sp_start_job at its own will, he is truly granted access to msdb and has the execute permission.

Option 3 is positevely correct, but I feel is unnecessary overkill.

So my preffered is Option 2: grant the EXECUTE permission on msdb.dbo.sp_start_job to the certificate derived user created in msdb.

Here is the corresponding SQL:

use [<appdb>];
go

create certificate agentProxy 
    ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
    with subject = 'agentProxy'
   , start_date='01/01/2009';
go

ADD SIGNATURE TO OBJECT::[StartAgentJob]
      BY CERTIFICATE [agentProxy]
        WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
go

alter certificate [agentProxy] 
  remove private key;
go

backup certificate [agentProxy] 
 to file='c:\temp\agentProxy.cer';
go

use msdb
go

create certificate [agentProxy] 
  from file='c:\temp\agentProxy.cer';
go

create user [agentProxyAuthenticator] 
 from certificate [agentProxy];
go

grant authenticate to [agentProxyAuthenticator];
grant execute on msdb.dbo.sp_start_job to [agentProxyAuthenticator];
go

use [<appdb>];
go

exec dbo.StartAgentJob;
go

My blog has some articles covering this topic, written in the context of Service Broker activated procedures (since they require an EXECUTE AS clause):

• Signing an Activated Procedure
• Call a procedure in another database from an activated procedure
• Why does feature … not work under activation?
• Certificate Not Yet Valid

BTW, if you're trying to test my script and you live on the eastern hemisphere, or on UK summer time, definitely read that last article I linked before testing.

Answer 2

Have you put the agentProxy login in the msdb database and given it rights to run sp_start_job? If not you'll need to enable database permission chaining for the msdb database and your user database.

You are probably better off putting the login into the msdb database and granting it the correct rights.

Answer 3

Since you're trying to start SQL Server Agent from .NET code, this might be a better question for StackOverflow?

http://www.stackoverflow.com

Answer 4

Checking a random SQL Instance on the network SQLAgentOperatorRole doesn't give you sp_start_job privledges directly, it inherits them from SQLAgentUserRole .

Double check it using :

select dp.NAME AS principal_name,
                 dp.type_desc AS principal_type_desc,
                 o.NAME AS object_name,
                 p.permission_name,
                 p.state_desc AS permission_state_desc 
    from    sys.database_permissions p
    left    OUTER JOIN sys.all_objects o on p.major_id = o.OBJECT_ID
    inner   JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id
    where o.name = 'sp_start_job'

Run this in MSDB and double check you havn't inherited any explicit denial access.

hth.

Answer 5

One way of achieving this without granting additional permissions: do not let the stored proc start the job directly, but just allow the stored proc to flip a bit in a table (in the application database); then, let the job run every minute or so, check if the bit is flipped and if so, perform the work and flip the bit back again. If the job sees the bit is not flipped, the job will just exit.

Works like a charm, if you don't mind the delay (and the job running very often).