Click here to Skip to main content
16,016,306 members
Articles / Database Development / SQL Server

SQL Server Agent Proxy

Rate me:
Please Sign up or sign in to vote.
4.75/5 (4 votes)
28 Aug 2008CPOL2 min read 58.4K   17  
shujaatsiddiqi.blogspot.com

Introduction

All the jobs run with the account assigned to SQL Agent service. What if we have to perform some task which requires some extra credentials or the other way around, then what should we do. The answer is SQL Server Agent Proxy. Proxy is about having additional security.

You can specify the job step for which you need a different security context to be using the security context of the specified proxy.

Background

SQL Server Jobs run in the security context of the account assigned to SQL Server Agent. We want to run a particular step of a job in a different security context. The answer is SQL Agent Proxy.

Credential

Before creating a proxy account, a credential must be created.

Image 1

When you select new credential in the context menu shown above, then the following form appears:

2.jpg
C#
USE [master]

CREATE CREDENTIAL [TstCredential] WITH
IDENTITY = N'SHUJAAT-PC\shujaat',

SECRET = N'shujaat'

 FOR CRYPTOGRAPHIC_PROVIDER MySecurityProvider

GO 

Sub System

This is a SQL Server Object with already defined functionality. They are to provide limitation to the functionality provided to a proxy. A proxy can be defined with one or more sub-systems. The list of sub-systems is as follows:

  1. ActiveX Script
  2. Operating System
  3. Replication Distributor
  4. Replication Merge
  5. Replication Queue Reader
  6. Replication Snapshot
  7. Replication Transaction-Log Reader
  8. SQL Server Analysis Services Command
  9. SQL Server Analysis Services Query
  10. SQL Server Integration Services Package
  11. Unassigned Proxy

To access the specified sub-system, the proxy has to use the security context of a Windows User.

Now there may be a question whether we can add our own sub-systems. The answer is No. We can only use those subsystems provided by the SQL Server and cannot add any new subsystems.

3.jpg

On the 'Principals' tab, you can specify the login information.

4.jpg

How It Works?

When SQL Server executes the job which has a step that uses any particular proxy, SQL Server impersonates the user account of the proxy to run the particular job step.

Are there any Exceptions?

Yes! The job step which is defined to be T-SQL Step runs in the security context of owner of the job if specifically no user is specified.

5.jpg

As shown in the figure, as soon as you select job step type as Transact-SQL script (T-SQL), the Run as selection box becomes disabled.

Transact SQL Support

sp_enum_sqlagent_subsystems: To list the SQL Server Agent subsystems defined in the system.

SQL
EXEC sp_enum_sqlagent_subsystems 

sp_enum_proxy_for_subsystem: To list subsystems assigned to the proxies.

SQL
EXEC dbo.sp_enum_proxy_for_subsystem 

sp_enum_login_for_proxy: To add new job step. This may also add proxy information.

SQL
USE msdb
EXEC dbo.sp_enum_login_for_proxy ;
GO 

sp_grant_proxy_to_subsystem: To assign subsystem to the proxy.

SQL
USE msdb
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy',
@subsystem_id=3
GO

sp_grant_login_to_proxy: To assign login to proxy.

SQL
USE msdb
EXEC msdb.dbo.sp_grant_login_to_proxy
          @proxy_name=N'MyProxy',
          @msdb_role=N'UserRole' 

sp_delete_proxy: To delete the specified proxy.

SQL
USE msdb
EXEC dbo.sp_delete_proxy
    @proxy_name = N'MyProxy' ;
GO 

sp_update_proxy: To update the specified proxy with the new information.

SQL
USE msdb
EXEC dbo.sp_update_proxy
         @proxy_name = N'NewProxyName',
         @enabled = 0;
GO 

sp_add_proxy: To add a new proxy

SQL
USE msdb
EXEC msdb.dbo.sp_add_proxy
          @proxy_name=N'MyProxy',@credential_name=N'TstCredential',
          @enabled=1
GO 

Steps for Incorporating Proxy in your Design

1. Create Credential

SQL
CREATE CREDENTIAL [TstCredential] WITH IDENTITY = N'SHUJAAT-PC\shujaat',
SECRET = N'shujaat'
FOR CRYPTOGRAPHIC_PROVIDER MySecurityProvider
GO 

2. Define Proxy

SQL
EXEC msdb.dbo.sp_add_proxy
@proxy_name=N'MyProxy',
@credential_name=N'MyCredential',
@enabled=1
GO
--Run operating system command (sub-system = 3)
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@proxy_name=N'MyProxy',
@subsystem_id=3
GO
EXEC msdb.dbo.sp_grant_login_to_proxy
@proxy_name=N'MyProxy',
@msdb_role=N'MyUserRole'
GO

3. Add Job Step Including Proxy Information

SQL
USE msdb;
GO
EXEC sp_add_jobstep
@job_name = N'Weekly Sales Data Backup',
@step_name = N'Set database to read only',
@subsystem = N'TSQL',
@command = N'ALTER DATABASE SALES SET READ_ONLY',
@retry_attempts = 5,
@retry_interval = 5
@proxy_name = N'MyProxy';
GO

History

  • 28th August, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
Muhammad Shujaat Siddiqi
New Jersey, USA

Comments and Discussions

 
-- There are no messages in this forum --