Certificates, Proxies and Jobs! Oh My!
3April 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.
-- 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.
Now can we create the proxy?
Sure
-- 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.
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' GO
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?
Not bad 🙂 I’ll have to explore a bit more later tonight.
[…] owner of the job or the tasks the job is running, however, if you run into problems remember that proxies will allow you to specify exactly who is running each step of a […]