THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Use a SQL Agent Proxy for Special Tasks

When SQL Server Agent runs its tasks it normally does so using the context of the service account you assigned to the Agent service. This may or may not be the best context for your application, so you can set up a Proxy. This allows you to run the task under the context of the account you set up in the proxy, and it's pretty easy to do.

First, set up a Credential. This defines the ADS login credentials the proxy will use. You can do this under the Security tree in Object Explorer, or you can set it up in Transact-SQL like this:

CREATE CREDENTIAL [cred_MyCredential] WITH IDENTITY = N'MyDomain\MyLogin', SECRET = N'S0meComp1exP@ssw0rd'
GO

Once the credential exists then you define the proxy. Expand the tree under SQL Server Agent/Proxies, select the job task type you plan to use the proxy for, then right-click and select New Proxy, or use this Transact-SQL:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'proxy_MyProxy',@credential_name=N'cred_MyCredential',
@enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'proxy_MyProxy', @subsystem_id=3
GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'proxy_MyProxy', @msdb_role=N'SQLAgentUserRole'
GO

In the sp_grant_proxy_to_subsystem stored procedure, @subsystem_id=3 indicates this proxy will be used to run Operating System commands.

Once the proxy exists it can be selected in setting up a job task by specifying the new proxy in the Run As dropdown in the task dialog. If setting up the job through Transact-SQL you specify the proxy in the sp_add_jobstep stored procedure with the @proxy_name parameter:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 01',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'"d:\ApplicationDirectory\MyJobStep.bat"',
@flags=0,
@proxy_name=N'proxy_MyProxy'

This may seem like a lot, but really it's not, and it ensures that the automation you're implementing is being done using the right context.

Allen

Published Tuesday, May 06, 2008 8:34 AM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Rod Colledge said:

When using this technique for running SSIS packages, beware of the need for additional DCOM permissions on the proxy/credential account if using the "out of process" execution mode to spawn child SSIS packages. Apart from that, it works really well!

Cheers,

Rod.

May 6, 2008 7:50 AM
 

ALZDBA said:

The fun part of proxies is you can use "functional" accounts.

This way your sqlagent service account does not need auth anywhere else but your sqlserver instance or/and box.

Just keep in mind, you need to grant that proxy account the rights (wherever) it needs to perform the jobs you want it to serve.

Another advantage is that you can have a user act as job admin or sqlagent user, and grant that user rights to use a proxy (or more).

This way, that user does not need rights for al the production boxes that are touched by his jobs.

Johan

May 7, 2008 3:24 PM
 

Virgilio said:

Hi I have a SQl AGent Job that executes a T-SQL transaction,

The View History record success(There is no error) but the T-SQL transaction never occurs.

WHen I run the SQL agent Job manually as in right click start job at step "n". Everything happens succesfully.

Again no error are raised.

ANy ideas...

September 18, 2008 12:19 PM
 

Allen White said:

Over time it's easy to lose track of the things you set up to get work done. As a consultant, I have

May 25, 2011 9:42 AM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement