Microsoft SQL Server Agent proxy accounts Facts
- SQL Server Agent proxy accounts define a security context in which a job step can run
- To set permissions for a particular job step assign a proxy to the job step
- Only members of the sysadmin fixed server role have permission to create, modify, or delete proxy accounts
- Users who are not members of the sysadmin fixed server role must be added to one of the following SQL Server Agent fixed database roles in the msdb database to use proxies: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole
- Creation of a proxy does not change the permissions for the user that is specified in the credential for the proxy
- Members of the sysadmin fixed server role have access to all proxies in the instance.
For more information, see How to: Configure a User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio).
The package runs successfully in the BIDS environment, but then it fails when they deploy the package and schedule the job to run. SQL Server Agent job fails with the message “Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.”.
The job fails if the account under which SQL Server Agent Service is running and the job owner is not a sysadmin on the box or the job step is not set to run under a proxy account associated with the SSIS subsystem.
What to do:
For such a scenario, SQL Server Agent allows creating a proxy account which defines the security context for the job step. This means each job step of the job can be run under a different security context using different proxies. SQL Server Agent impersonates the credentials (Windows User accounts) associated with the proxy when the job step is executed if the job step is set to run under that proxy. You can create a proxy and grant access to as many of the available subsystems as needed. Also a single proxy can be used by more than one job step to run under it if all of them require the same level of permissions.
1. Create Credential
–Drop the credential if it is already existing
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N’sharepoint’)
DROP CREDENTIAL [sharepoint]
CREATE CREDENTIAL [sharepoint]
WITH IDENTITY = N’Domain\LocalAccount’,
SECRET = N’password’
2. Create Proxy
Next you need to create a proxy account, please note that there is no DDL command available as of now for creating a proxy account, but rather in the msdb database you use the sp_add_proxy system stored procedure. You can enable or disable the proxy account using sp_update_proxy system stored procedure and use sp_delete_proxy to delete a proxy account.
Or use SSMS to create a proxy see below
–Script #2 – Creating a proxy account
–Drop the proxy if it is already existing
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N’PSjobs’)
@proxy_name = N’PSjobs’
–Create a proxy and use the same credential as created above
@proxy_name = N’PSjobs’,
3. Associate Proxy with Subsystem
Once a proxy account is created, associate it with the SQL Server Agent subsystems using sp_grant_proxy_to_subsystem system stored procedure. A single proxy account can be granted access to one or all of the available subsystems.
@subsystem_id=11 –subsystem 11 is for SSIS as you can see in the above image
4. Grant Permissions to Use Proxy
Grant permissions to logins/roles who will be using the created proxy account using sp_grant_login_to_proxy system stored procedure. Use sp_enum_login_for_proxy system stored procedure to view all the logins/roles who have access to the proxies.
5. Specify Proxy to Use for Job Step
Specify the proxy account to use for the job step with an additional option available in the Run As field. When SQL Server Agent executes the job step it impersonates the credentials of the proxy instead of the SQL Server Agent service account.