SQL Server Agent Proxies

SQL Server Agent Proxies are very helpful in executing some jobs. I had came across a situation where in I was scratching my head so as to how to accomplish a task which I wanted to run as SQL server Agent Job. First let me give some brief description about proxies.

SQL Server Agent uses proxies to manage security contexts. A proxy can be used in more than one job step. A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.

Now lemme give you a small example where how exactly a proxy can be helpful.

Say you have around 10 database servers,  both SQL Engine and SQL Agent services on these servers run on different Service Account say Machine Specific. Ex Ser1, Ser2, Ser3 and so on. Now here is the task. You want some operations may be a T-SQL or some script combined into SSIS package to be run on all these services. One way to achieve this is schedule a Job on each of these server and what if you want a consolidated data like health of all servers or logins existing on these servers as single report. This is where your Proxy comes in handy. To achieve this task get a Service Account(A windows Domain user Account), give access to this service account on all servers and then create a credential for this service account and create proxy for this credential on any of the one server and execute this Job or package from one single server and get combined data on one server. By doing so what exactly happens is SQL server agent impersonates the associated User account with proxy against the server on which the script/job needs to be run and since you have provided access to this service account on all server you will be able to run the same. Now lemme guide you through a step wise procedure to create a proxy using T-SQL.

--Detailed Steps to create a SQL proxy using T-SQL
--Step 1 - Create a credential for proxy

USE MASTER
GO
--Drop and create the demo credential if it is already existing
IF EXISTS (SELECT * FROM sys.credentials WHERE name = N'ProxyDemoCredentials')
BEGIN
DROP CREDENTIAL [ProxyDemoCredentials]
END
GO
CREATE CREDENTIAL [ProxyDemoCredentials]
WITH IDENTITY = N'YourDomain\ProxyDemoServiceAccount',
SECRET = N'ServiceAccountPasswordHere'
GO 
--End of Step 1

--Step 2 - Create a demo proxy account
USE msdb
GO
--Drop the demo proxy if it is already existing
IF EXISTS (SELECT * FROM msdb.dbo.sysproxies WHERE name = N'ProxyDemo')
BEGIN
  EXEC dbo.sp_delete_proxy
    @proxy_name = N'ProxyDemo'
END
GO
--Creating a demo proxy and assigning the demo credential to the same
EXEC msdb.dbo.sp_add_proxy
  @proxy_name = N'ProxyDemo',
  @credential_name=N'ProxyDemoCredentials',
  @enabled=1
GO
--You Need to Enable a Proxy before using it which can be done using below step.
EXEC msdb.dbo.sp_update_proxy
  @proxy_name = N'ProxyDemo',
  @enabled = 1 
GO 
--End of Step 2

--Step 3 Granting created demo proxy to SQL Agent subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
  @proxy_name=N'ProxyDemo',
  @subsystem_id=11 --subsystem 11 is for SSIS. You can get info using EXEC sp_enum_sqlagent_subsystems 
GO
--End of Step 3

--Step 4 Granting access of security principals(service account) to demo proxy created
USE msdb
GO
--Grant proxy account access to security principals
EXEC msdb.dbo.sp_grant_login_to_proxy
  @proxy_name=N'ProxyDemo'
  ,@login_name=N'YourDomain\ProxyDemoServiceAccount'
  --,@fixed_server_role=N'<fixed_server_role name>' if any roles
  --,@msdb_role=N'<msdb_role name>' if any MSDB roles
GO
--End of Step 4.

Now just use this proxy under Run AS in job step for using SSIS package.

Technorati Tags: proxy,credintials,Login,Proxies,SSIS