Certificates, Proxies and Jobs! Oh My!

2

April 27, 2016 by Kenneth Fisher

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

2 thoughts on “Certificates, Proxies and Jobs! Oh My!

  1. And here are some diagrams on my website you may find helpful…

    SQL Agent Job Architecture

    Job Agent Security

    Proxies and Credentials

    SQL Agent Alert Architecture
    http://www.e-squillace.com/tech/techdiagrams/SQLServerAlertFacts.htm

    Should I have used TinyURLs?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: