SQL Server Agent Security | Inside SQL Server 2005 Tools

How can a user create and run tasks using SQL Server Agent? How can I let him or her use a set of credentials for performing certain types of tasks? To answer these questions you need to look at the new SQL Server Agent security model.

Database Roles

SQL Server Agent is no longer accessible to every authenticated user, as in previous releases of SQL Server. User access and rights for SQL Server Agent are controlled through one of three major security-related database roles in MSDB. The most basic role is SQLAgentUserRole. Members of this role can create SQL Server Agent jobs. Most users will be associated with this role. The next level up is SQLAgentReaderRole. Members of this role can read job details and job history for jobs created by other users. Therefore, these users can perform simple monitoring of the Agent execution environment. The next level up is SQLAgentOperatorRole. This role is even more powerful than the other two and was introduced to provide basic administrative functionality. Members of this role can view, start, stop, enable, and disable jobs created by other users. None of the members of SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole can modify any job details. If they could, it would be possible for them to inject malicious contents into a job step and therefore cause elevation of privileges during job execution. That is why this is not allowed by the system.

Warning

In an ideal world, members of SQLAgentReaderRole and SQLAgentOperatorRole would be restricted to viewing and acting upon only those jobs that run in the databases to which they have access. Unfortunately, the SQL Server Agent security model does not allow this finer-granularity filtering. Therefore, when you make a user a member of these roles you must realize the implied security consequences to avoid unintentional information disclosure.

Proxy Accounts

Why does SQL Server Agent need proxy accounts? Every job step executes under a specific set of credentials that defines its execution context. It would be wrong for SQL Server Agent to let an average user run his job under the credentials of the SQL Server Agent service account. If this happened, the user could execute dangerous operating system commands, and see and modify SQL Server data not normally accessible. SQL Server Agent has no access to the job owner's password, so it cannot impersonate a job owner directly. Therefore SQL Server Agent needs to rely on a known set of credentials and a mapping that instructs SQL Server Agent to use these credentials on behalf of the user for a given subsystem task. This logical mapping is provided through a proxy account, that is, an account to be used as a proxy for the user. Most subsystems, except T-SQL, use proxy accounts.

The T-SQL subsystem does not need a proxy account because it uses a different impersonation mechanism provided directly by SQL Server Database Engine. Upon each connection and before executing a T-SQL subsystem task, SQL Server Agent runs the EXECUTE AS LOGIN = N'<login_name>' WITH NO REVERT command, providing the job owner's login as a parameter. SQL Server Database Engine switches execution context of the current session to this login and does not allow reverting back to the account that initiated the connection.

Figure 8-8 shows a logical diagram that should help you understand the mapping that the proxy account provides.

Figure 8-8. Proxy account logical diagram.

By itself, the proxy account object does not store usernames and passwords. The account needs to be mapped to a specific credential object that contains the username and password. The proxy account also needs to be associated with a subsystem that is going to use impersonated context for task execution. Finally, a proxy account needs to be tied to a user, allowing the user to create tasks belonging to a subsystem to be run under the aforementioned set of credentials. The term "user" can be viewed in a rather broad sense. Access to the proxy account can be granted to a valid MSDB user, MSDB role, or a fixed SQL Server role. Please note that to use SQL Server Agent in SQL Server 2005, a the non-administrative login has to have a valid user in the MSDB database and be part of one of the special SQL Server Agent roles (SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole) anyway.

Using Proxy Accounts to Restrict Access

In comparison to earlier releases of SQL Server, SQL Server 2005 allows for much finer granularity when it comes to proxy accounts. There used to be a single proxy sccount shared by all SQL Server Agent subsystems and bythe xp_cmdshell extended stored procedure. In SQL Server 2005, xp_cmdshell runs under its own unique credential, which is no longer tied to SQL Server Agent, and the SQL Server Agent administrator can define multiple proxy accounts and tie them to multiple subsystems. Thus, the SysAdminOnly Registry flag and corresponding @sysadmin_only configuration parameter no longer work against SQL Server 2005 and are kept only for backward compatibility reasons.

In practical applications, sometimes, it makes sense to define multiple SQL Server Agent proxy accounts and link them to the same credential object to restrict different users to specific subsystems.

Some examples might include the following:

User A needs to be able to create ActiveScripting tasks and run them under Account X.
User B needs to be able to create CmdExec tasks and run them under Account X.
User C needs to be able to create both ActiveScripting and CmdExec tasks and run them under account X.
User D needs to be able to create both ActiveScripting and CmdExec tasks and run them under account Y.

An administrator can satisfy these requirements by performing the following actions:

Create proxy account "X Active Scripting" and associate it with User A and User C.
Create proxy account "X CmdExec" and associate it with User B and User C.
Create proxy account "Y Active Scripting and CmdExec" and associate it with User D.

Setting up the multiple SQL Server Agent proxy accounts in this fashion creates the fine-grained control required.

When a proxy account is first created, SQL Server Agent verifies that it is linked to a valid set of credentials. Upon each job step execution, SQL Server Agent makes sure the user is still granted access to the proxy account and fails the job if the user does not have access. Underlying credential objects can be manipulated while jobs are running, (such as when updating passwords and changing the security context of the object). One thing to keep in mind is that all jobs linked to the proxy account associated with this credential object will be affected.

Setting Up a Proxy Account

Now that you understand the theory behind proxy accounts, let's go through the process of creating one. Suppose you need to have a special account for running a CmdExec task whose purpose is to run queries against Analysis Server and generate textual reports.

Under the Proxies folder in Object Explorer, there are multiple subfolders that contain proxies associated with different SQL Server Agent subsystems. There is also a special folder at the end that contains unassigned proxies. Right-clicking on the Proxies folder and selecting New Proxy opens the New Proxy Account dialog.

Tips and Tricks

Make sure you created an appropriate Credential object before attempting to create a proxy account. The Credentials folder is located under the Security folder in Object Explorer. The main requirement for a Credentials object to be used later for proxy account association is to have a valid Windows account identity and password. For most tasks that do not establish connections to other computers across the network, it may be sufficient to create a local computer account instead of a domain account. In this scenario, job steps using such an account run faster because account authentication can occur locally without contacting a domain controller.

For a new proxy account you need to fill in the proxy account name, a valid previously created credential object name, and subsystem associations (see Figure 8-9).

Figure 8-9. Creating a new proxy account.

If you already know which users will be using this proxy account, you can add them at this stage as well by clicking on Principals in the left tree view (See Figure 8-10).

Figure 8-10. Proxy account principal associations.

Now that the proxy account is ready, you can create the job running the CmdExec task (see Figure 8-11). One additional caveat here is the need to preserve full job step output because this is how a report actually gets generated. By default, only the first 1024 characters of any job step output are persisted in the database, and even then most of this space is consumed by statistical information about runtime, execution credentials, and so on.

Figure 8-11. CmdExec task creation.

Following normal job creation procedure, we will concentrate more on CmdExec job step creation. One thing to notice here is the proxy account selection under the Run as option. The drop-down list will be populated with all available proxies associated with the current subsystem to which the user has access.

Click on the Advanced item in the left tree view to get to the job step logging options.