SQL Server Agent Proxy

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.

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

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.

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

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.

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.

EXEC sp_enum_sqlagent_subsystems 

sp_enum_proxy_for_subsystem: To list subsystems assigned to the proxies.

EXEC dbo.sp_enum_proxy_for_subsystem 

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

USE msdb
EXEC dbo.sp_enum_login_for_proxy ;
GO 

sp_grant_proxy_to_subsystem: To assign subsystem to the proxy.

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.

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.

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

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

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

sp_add_proxy: To add a new proxy

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

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

2. Define Proxy

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

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

• 28^th August, 2008: Initial post