Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Our developers 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?

share|improve this question
    
Solved. There were three pieces to the solution: ownership chaining must be enabled on the server; the user used in the EXECUTE AS statement has to be sa or a user with similar permissions in order to run the xp_sqlagent_* jobs; and the job must be owned by the same user as is listed in the EXECUTE AS statement. –  Ed Leighton-Dick Jul 14 '09 at 15:30
    
A little more experimentation showed one variation to this solution. If you want to use a non-SA proxy user to run the job, you can grant the proxy user EXECUTE permissions on the xp_sqlagent_* procedures in the master database. (The other two requirements - cross-database ownership and job ownership - still apply.) –  Ed Leighton-Dick Jul 14 '09 at 15:44
add comment

5 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Yes - I started by adding it to the SQLAgentOperator role and then tried direct EXECUTE permissions on sp_start_job itself. Neither helped. It seems to throw this error regardless of the permissions of the proxy - even a sysadmin-level account fails. –  Ed Leighton-Dick Jun 26 '09 at 14:54
    
Use SQL Profiler and see what account is actually making the call. Now that I think about it more, Execute As is use a database user, which probably isn't translating out to the other database correctly. Try turning on database chaining and see if that works. –  mrdenny Jun 26 '09 at 17:20
    
The ownership chaining was a big part of the solution, so I'm awarding the points here. It also turns out that there are two other pieces to this; I'll note those above. –  Ed Leighton-Dick Jul 14 '09 at 15:28
add comment

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):

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.

share|improve this answer
add comment

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

http://www.stackoverflow.com

share|improve this answer
1  
I think this is probably a database security issue, but I'll try StackOverflow if we can't find an answer here. –  Ed Leighton-Dick Jun 26 '09 at 14:51
add comment

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.

share|improve this answer
    
The user is explicitly a member of SQLAgentOperatorRole and SQLAgentUserRole. –  Ed Leighton-Dick Jul 14 '09 at 14:47
add comment

Great answer, however you don't talk about how the user agentProxy (the user which is executed as in the SP) is setup in the DB where the stored proc is saved (appdb).

Could you please explain this as I don't know which login I should point the SP to in the EXECUTE AS part.

If I create the login agentProxy with the code: -

create user [agentProxy]  

then I get the error: -

Msg 15007, Level 16, State 1, Line 1 'agentProxy is not a valid login or you do not have permission.

Thanks

share|improve this answer
1  
Solved this, you dont actually need to use EXECUTE AS if you are using certificates –  Wildster Nov 25 '10 at 14:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.