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.

CreateCredential

-- Code to create the credential
CREATE CREDENTIAL MyCredential
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!

Per BOL

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.

CredentialProxy1

Now can we create the proxy?
Sure

CreateProxy

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

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

CredentialProxy2

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

ProxyPermissions

-- 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'
GO

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

SQLStudies

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.

Comments

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

Loading comments...