Credential, Proxies, SQL Server Agent Jobs

Terminology:


Credentials:

A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. Most credentials contain a Windows user name and password.

The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication to access resources outside the server instance. When the external resource is Windows, the user is authenticated as the Windows user specified in the credential. A single credential can be mapped to multiple SQL Server logins. A SQL Server login can be mapped to only one credential.

System credentials are created automatically and are associated with specific endpoints. Names for system credentials start with two hash signs (##).

Proxies:

SQL Server Agent uses proxies to manage security contexts. A proxy can be used in more than one job step. Members of the sysadmin fixed server role can create proxies.

Each proxy corresponds to a security credential. Each proxy can be associated with a set of subsystems and a set of logins. The proxy can be used only for job steps that use a subsystem associated with the proxy. To create a job step that uses a specific proxy, the job owner must either use a login associated with that proxy or be a member of a role with unrestricted access to proxies. Members of the sysadmin fixed server role have unrestricted access to proxies. Members of SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole can only use proxies to which they have been granted specific access. Each user that is a member of any of these SQL Server Agent fixed database roles must be granted access to specific proxies so that the user can create job steps that use those proxies.



Two Types of Proxies



1. xp_cmdshell proxy


2. SQL Server Agent Proxy


xp_cmdshell proxy :

This is used for non sysadmin users to execute xp_cmdshell from SSMS (QA) or from SPs or in Jobs ( Only as T SQL Job Step )

xp_cmdshell is disabled during sql server installation



1. Enable xp_cmdshell through Facets or Surface Area Configuration

2. Create a domain Windows account ( domain\sqljobrun) in the domain

3. For each database server use this account to create credentials

Note: This account should not be local admin account in database server and should not be sysadmin. It need not access SQL Server at all.

4. Provide Log on batch to sqljobrun: Start -> Administrative Tools - > Local Security Policy - > Log on as a batch Job Click Add User or Group .

Add domain\sqljobrun

5. Create a non sys admin Login and Database user for SQL Server User (sqltest)

6. Provide database access sqltest to master database

7. Provide execute access to xp_cmdshell in master database

Use master

Go

Grant exec on xp_cmdshell to sqltest

8. Server - > Object Explorer -> Right Click -> Properties - > Security

9. Server Proxy Account -> Enable Server Proxy Account

Proxy account :domain\sqljobrun

Password : xxxxxx



This will create system credential ##xp_cmdshell_proxy_account## under Security -> Credentials



sqltest can execute xp_cmdshell in SSMS, Stored Procedures and T-SQL job steps .

Note: This is not same as cmdexec . cmdexec uses direct execution of cmd.exe

Sqltest test will have same directory and window privileges of domain\sqljobrun



Note: Make sure domain\sqljobrun password newer expires .



Any error in executing xp_cmdshell, provide read/execute access to windows\system32\cmd.exe to domain\sqljobrun



 

SQL Agent Proxies :

The SQL Agent Proxy accounts allow users without sysadmin authority to own and run SQL Agent jobs. The SQL Agent contains a number of subsystems, each one dedicated to a particular type of work. A proxy account is required in order for an account without sysadmin authority to use these subsystems

The recommended approach is to configure each Non-Admin account that needs to run SQL Agent jobs as a proxy account. This will allow all the authorities required by the job to be contained within the job owner account. The first step in creating a SQL Agent proxy is to create a credential. Therefore, every Job Owner account would have a credential defined for it in order to both isolate and encapsulate the authorities required by a given job.

The credentials created for a given job owner are then associated with each type of SQL Agent subsystem used by the job owner. It is this association that creates the required proxies. This will ensure that all steps in the jobs owned by that account run with a predictable and consistent security profile.



SubSystem:

A subsystem is a predefined object that represents a set of functionality available to a SQL Server Agent proxy. Each proxy has access to one or more subsystems. Subsystems provide security because they delimit access to the functionality that is available to a proxy. Each job step runs in the context of a proxy, except for Transact-SQL job steps. Transact-SQL job steps use the EXECUTE AS command to set the security context.



Subsystem name Description

Microsoft ActiveX Script Run an ActiveX scripting job step.

Important:

The ActiveX Scripting subsystem will be removed from SQL Server Agent in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.



Operating System (CmdExec) Run an executable program.

PowerShell Run a PowerShell scripting job step.

Replication Distributor Run a job step that activates the replication Distribution Agent.

Replication Merge Run a job step that activates the replication Merge Agent.

Replication Queue Reader Run a job step that activates the replication Queue Reader Agent.

Replication Snapshot Run a job step that activates the replication Snapshot Agent.

Replication Transaction Log Reader Run a job step that activates the replication Log Reader Agent.

Analysis Services Command Run an Analysis Services command.

Analysis Services Query Run an Analysis Services query.

SSIS package execution Run an SSIS package.







 

Any non sysadmin Sql Server user who needs to create, execute and maintain sql server jobs must be member of one of these database fixed roles in msdb database.

• SQLAgentUserRole


• SQLAgentReaderRole


• SQLAgentOperatorRole





When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent.

Permissions of SQL Server Agent Fixed Database Roles

The SQL Server Agent database role permissions are concentric in relation to one another -- more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). For example, if members of least-privileged SQLAgentUserRole have been granted access to proxy_A, members of both SQLAgentReaderRole and SQLAgentOperatorRole automatically have access to this proxy even though access to proxy_A has not been explicitly granted to them. This may have security implications, which are discussed in the following sections about each role.

SQLAgentUserRole Permissions

SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.

Security Note:

Consider the security implications before granting proxy access to members of the SQL Server Agent database roles. The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole and SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to the SQLAgentUserRole and can use those proxies.

The following table summarizes SQLAgentUserRole permissions on SQL Server Agent objects.

Action Operators Local jobs (owned jobs only) Job schedules (owned schedules only) Proxies

Create/modify/delete No Yes 1 Yes No

View list (enumerate) Yes 2 Yes Yes Yes 3

Enable/disable No Yes Yes Not applicable

View properties No Yes Yes No

Execute/stop/start Not applicable Yes Not applicable Not applicable

View job history Not applicable Yes Not applicable Not applicable

Delete job history Not applicable No 4 Not applicable Not applicable

Attach/detach Not applicable Not applicable Yes Not applicable

1 Cannot change job ownership.

2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.

3 List of proxies only available in the Job Step Properties dialog box of Management Studio.

4 Members of SQLAgentUserRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs.

 



SQLAgentReaderRole Permissions

SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

Security Note:

Consider the security implications before granting proxy access to members of the SQL Server Agent database roles. Members of SQLAgentReaderRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole have access to all SQL Server Agent proxies that have been granted to SQLAgentUserRole and can use those proxies.

The following table summarizes SQLAgentReaderRole permissions on SQL Server Agent objects.

Action Operators Local jobs Multiserver jobs Job schedules Proxies

Create/modify/delete No Yes 1 (owned jobs only) No Yes (owned schedules only) No

View list (enumerate) Yes 2 Yes Yes Yes Yes 3

Enable/disable No Yes (owned jobs only) No Yes (owned schedules only) Not applicable

View properties No Yes Yes Yes No

Edit properties No Yes (owned jobs only) No Yes (owned schedules only) No

Execute/stop/start Not applicable Yes (owned jobs only) No Not applicable Not applicable

View job history Not applicable Yes Yes Not applicable Not applicable

Delete job history Not applicable No 4 No Not applicable Not applicable

Attach/detach Not applicable Not applicable Not applicable Yes (owned schedules only) Not applicable

1 Cannot change job ownership.

2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.

3 List of proxies only available in the Job Step Properties dialog box of Management Studio.

4 Members of SQLAgentReaderRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs.

 



SQLAgentOperatorRole Permissions

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. Only the parameters that specify the job or schedule name or identifier and the @enabled parameter can be specified by members of SQLAgentOperatorRole. If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own.

The Jobs, Alerts, Operators, and Proxies nodes in SQL Server Management Studio Object Explorer are visible to members of SQLAgentOperatorRole. Only the Error Logs node is not visible to members of this role.

Security Note:

Consider the security implications before granting proxy access to members of the SQL Server Agent database roles. Members of SQLAgentOperatorRole are automatically members of SQLAgentUserRole and SQLAgentReaderRole. This means that members of SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to either SQLAgentUserRole or SQLAgentReaderRole and can use those proxies.

The following table summarizes SQLAgentOperatorRole permissions on SQL Server Agent objects.

Action Alerts Operators Local jobs Multiserver jobs Job schedules Proxies

Create/modify/delete No No Yes 2 (owned jobs only) No Yes (owned schedules only) No

View list (enumerate) Yes Yes 1 Yes Yes Yes Yes

Enable/disable No No Yes 3 No Yes 4 Not applicable

View properties Yes Yes Yes Yes Yes Yes

Edit properties No No Yes (owned jobs only) No Yes (owned schedules only) No

Execute/stop/start Not applicable Not applicable Yes No Not applicable Not applicable

View job history Not applicable Not applicable Yes Yes Not applicable Not applicable

Delete job history Not applicable Not applicable Yes No Not applicable Not applicable

Attach/detach Not applicable Not applicable Not applicable Not applicable Yes (owned schedules only) Not applicable

1 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.

2 Cannot change job ownership.

3 SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.

4 SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and specifying values for the @enabled and the @schedule_id (or @name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.





To execute cmdexec and Active X Script for sqltest



1. Login as sysadmin

2. Go to Object Explorer - > Security - > Credentials

3. New Credential

Credential name : sqltest credential

Identity : domain\sqljobrun

Password: xxxxx

Confirm password: xxxxx

OK

4. SQL Server Agent - > Proxies

5. New Proxy

Proxy name: sqltest proxy

Credential name: sqltest credential

Active to the following Subsystem



Check Box :

ActiveX Script

OperatingSystem(CmdExec)

6. Principals : Add sqltest



With this sqltest can create jobs. For job step type Operating System(cmdexec) sqltest should select sqltest proxy to run the job