Managing the Core Database Engine - Microsoft SQL Server 2008 R2 Administration - page 121

Implementing security for SQL Server Agent
jobs management
The integrity of the data is only as good as your ability to secure the data platform. Typically,
security doesn't stop in authentication and authorization to access the SQL Server. The
additional process of encrypting data and providing a secured backup system is essential.
Similarly, scheduling such data management activities is carried over using SQL Server Agent
service. An SQL Agent job is a complex database object with many details involved in the
definition. There are even more details to consider while a job is actually running, but it's
not convenient to examine the details for SQL Agent jobs using SSMS and the filtering is
less than ideal.
In this recipe, we will be implementing security for SQL Server Agent Jobs management
by using the credentials and proxy methods. A credential is a record that contains the
authentication information required to connect to a resource outside of SQL Server. A proxy
is really just the mapping of an SQL Server credential to a specific type of SQL Server Agent
operation. The SQL Server Agent service manages many different objects on the server, each
one responsible for its own function in the larger automation infrastructure. Mastering these
objects is critical to effectively implement an automation plan.
The key aspect of securing the SQL Server Agent service is proxies and assigning a login to
start the agent service. Although most of the processing that an SQL Server Agent automation
structure will perform will be directed to SQL Server objects, a situation might arise in which
actions require access to resources that are outside of SQL Server at the operating system
level. In this case, authentication and permissions can be a problem. As the agent is an
important service that performs the necessary tasks for automated administration, you must
also restrict permissions to the minimum to perform the actions.
Getting ready
The SQL Server Agent offers the security context for a job through a proxy without having
elevated privileges for the users, not all SQL Server Agent jobs require a proxy. Each proxy
corresponds to a security credential that 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.
This process occurs in two steps:
F Firstly, you must create the credential at the server level
F Secondly, you must map that credential to a proxy, which specifies the contexts and
operation types with which you are allowed to use the credential