SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Certificates, Proxies and Jobs! Oh My!

Oh Noes! I have a job that requires me to run an SSIS package, cmdshell script, etc. And I need them to have specific file level permissions! What do I do! I know I need to run my job/job step as a specific AD/Windows user but I don’t see how to do that.

It’s not all that hard really. You just need to associate a proxy with the job step.
So I need a proxy?

Yes. You need to create a proxy. And you’ll need to associate it with the type of job step you’re using.
Uh hu

Of course to create a proxy you need a credential.
Ok, you’ve completely lost me. What’s a credential?
And a proxy. What’s a proxy?
Oh, and how do I associate a proxy with a job step???

Let’s start with a credential.

Per BOL:

A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password.

There are purposes for credentials other than a proxy, but for our purposes you are just going to enter an AD username and password. Just to be even more clear, this is an AD/Windows user. Not a sql server login.

In Object Explorer: ServerName -> Security -> Right click on Credentials and select New Credential -> Fill in the Name, Identity and Password fields.


-- Code to create the credential
WITH IDENTITY = 'Kenneth-Laptop\Dopey'
     , SECRET = 'StrongPassword'

Now that we have a credential we can create the proxy.
Hey, definitions first! What’s a proxy!


A SQL Server Agent proxy manages security for job steps that involve subsystems other than the Transact-SQL subsystem. Each proxy corresponds to a security credential. A proxy may have access to any number of subsystems.

If it helps, subsystems include such things as CmdExec and SSIS. The different types of job steps.


Now can we create the proxy?


-- Code to create a proxy
USE [msdb]
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MyProxy',@credential_name=N'MyCredential', 
-- subsystem 3 is CmdExec
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy', @subsystem_id=11
-- subsystem 11 is SSIS
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy', @subsystem_id=11

Now that we have a proxy we can assign it to the appropriate job step.


Now if the person creating the job isn’t sysadmin then they will have to be granted permission to the proxy.


-- Code to add permissions for a proxy
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'MyProxy', @login_name=N'Kenneth-Laptop\Doc'

Filed under: Microsoft SQL Server, Security, SQL Agent Jobs, SQLServerPedia Syndication, SSMS Tagged: microsoft sql server, security, SQL Agent Jobs, SSMS


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...