DIR Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
HTML https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
DIR Return to: General
*****************************************************
#Post#: 18--------------------------------------------------
Credential, Proxies, SQL Server Agent Jobs
By: srinivasma_exceldbp Date: March 6, 2014, 1:27 am
---------------------------------------------------------
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
*****************************************************