Wiki Home

SQL Agent Mail

Namespace: VFP
Mail component of the SQLAgent service, which is a component of SQL Server. Not to be confused with SQLMail, a component of the SQL Server service.

If you want to use the SQL Agent Mail service, follow these steps:
  1. You MUST set up your SQL Agent service to use something other than the system account, which is the default. To do this:
    1. It's best to create a dedicated account for the Agent and the SQL Server service. Some people create separate accounts, some use the same for both. Regardless, create an account specifically for this purpose. For now, make it part of the local admin group (NOT domain admins!). BOL describes what the minimum permissions are if you're worried about that. You may want to set the password not to expire, otherwise you'll have another admin headache when the password eventually expires.
    2. Open Enterprise Manager and expand your server, then the Management folder where SQL Server Agent resides.
    3. Right-click SQL Server Agent, and choose Properties.
    4. On the general tab, you'll see the Service startup account. Change it from System account and enter the username (such as mydomain\username) and the password.
    5. Restart the SQL Server Agent service (on SQL2k you'll be prompted to do so).
  2. Now that you've got your SQL Agent set up with a real account, you now need to set up a mail profile. Start by installing a MAPI-compliant mail client if one doesn't exist. I'll assume Outlook since I know that works - rumor has it other mail clients have problems.
  3. Log off of the computer and log back in using the same account and password that you used for the SQL Agent service (the one you entered in the properties dialog).
  4. Fire up Outlook, ensure it is set up properly, and send a test email. Make sure this step works before proceeding.
  5. Log off and log back in with an account that has admin access under SQL Server.
  6. Return to the properties dialog of the SQL Agent and select the mail profile from the drop-down. If the drop-down is disabled or doesn't show any profiles, something went wrong in the earlier setup of the mail profile under the Agent account. Log back in as the service account, delete the profile(s) and create a new one.
  7. Hit the Test button in the properties dialog adjacent to the profile drop-down

Once you have SQL Agent Mail set up properly, you can follow similar steps to set up SQLMail as well, which is found under the SQL Server properties dialog. SQLMail is used by xp_sendmail and other stored procs that deal with mail stuff.

-- Chuck Urwiler

( Topic last updated: 2004.01.09 02:17:21 PM )