Configuring SQL Server Agent

The SQL Server Agent is accessed through the Management folder of Enterprise Manager. To configure the Agent, right-click SQL Server Agent and select Properties. Figure 18.1 shows the SQL Server Agent Properties dialog box.

Figure 18.1. The Agent Properties dialog box.

The following sections outline some of the key configuration options.

Startup Account

When running SQL Server on Windows NT or 2000, the agent runs as a Windows service. From the General tab of the Agent properties (Figure 18.1), you have the choice of running the Agent under the local system account or with a Windows domain account. The local system account is, as the name suggests, restricted to the local system and has no network access rights. This can be restrictive , particularly if you have multiple servers. The preferred method is to set the Agent to use its own domain user account. This account should have the "logon as a service" user right, and be in the local administrators group . Resist the temptation to use the Administrator account for the Agent; create an account just for the Agent, or use the account under which the MSSQLServer service runs. If you use Administrator, the service will fail on startup the next time you change the Administrator's password.

Mail Profile

A mail profile can also be specified for the SQL Server Agent. This mail account is the one the Agent will use when sending notifications to operators. Create a mail account by logging on to the server with the account the Agent will run under, and set up the mail client as you would any other e-mail user. After the mail profile is configured, you can select it from the Mail Profile drop-down box. The test button checks whether SQL Server can start and stop a MAPI session with the specified profile. If this fails on initial setup, stop and start the SQL Server Agent and retry the test. If you still have problems, log in as the Agent account and ensure you can send and receive e-mail using the mail client.

User Connection

The SQL Server Agent can be configured to use a SQL Server login as opposed to the local system or a domain account, but this is only used in a Windows 98 or Windows CE installation where the Agent runs as an application as opposed to a service. This is configured from the Connection tab, as shown in Figure 18.2, and the account must be in the sysadmin role.

Figure 18.2. The Agent Properties Connection tab.

SQL Agent Proxy Account

By default, only users in the Sysadmin role can execute jobs that contain CmdExec (command line) and ActiveScripting job steps. If you choose to allow others this privilege, on the Job System tab of the Properties dialog box, you must unselect the Only Users with Sysadmin Privileges check box. The first time you do this, a pop-up box will appear asking you to specify an account that will be used to execute these tasks . After the account is set up, it can be edited through the Reset buttons . Figure 18.3 shows the SQL Server Agent Job System tab.

Figure 18.3. The Agent Job System tab.