Gonzo TechNet
How to Set Up DBMail in SQL 2005
Jump to: Configure Database Mail Test Database Mail
Database Mail Configuration

Here are the steps necessary to setup/enable mail in SQL 2005. This has been greatly improved and simplified since SQL 2000.

  1. To begin, verify that that service broker for mail is enabled in the msdb database.
    Run the following query in the master database:
    SELECT name,is_broker_enabled FROM sys.databases;
    If is_broker_enabled = 0, you will need to turn it on by executing the following:

    Note: If the command above takes a long time to execute, cancel it and execute the following command

  2. Once the broker is enabled, run the DBMail configuration wizard SQL Server Management Studio (SSMS) From the SSMS console expand the Management section then right-click on the Database MAil icon then select Configure Database Mail Configure Database Mail
  3. From the Database Mail Configuration Wizard screen, click Next
    (This is a one-time step that can be part of the SQL 2005 VM image used by the Intel team) Database Mail Configuration Wizard
  4. From the Configuration Task screen, select the Set up Database Mail by performing the following tasks: option and then click Next Database Mail Configuration Task
  5. If this is the first time the wizard has been run, you will be prompted to Enable the Database Mail feature. Click on the Yes button. Enable Database Mail
  6. You will now be presented with the New Profile screen. If no profiles or SMTP accounts have been created, you will need to create and configure them now. In the Profile name: box enter dbmon and then click the Add button. Database Mail New Profile
  7. You will now be in the New Database Mail Account screen.
    Enter the following values:
    1. Account name: dbmon
    2. Description: dbmon mail profile
    3. E-Mail address:
    4. Display Name: CHW-SQL-xxx [Set this to the HOSTNAME!]
      This is what will appear in the "From" of the emails that are sent to the operator/user.
      By setting it to the hostname of the source server, it will be easier to identify which system is reporting a problem.
    5. Server name:
    On the bottom of the screen be sure to enable Anonymous authentication and then clikc on the Next button. Create DBMAil account
  8. When the Manage Profile Security screen appears:
    1. Check the "PUBLIC" box to set the the dbmon profile as a public one
    2. Click in the "Default Profile" box to display the drop-down and set to YES
    3. Click on the Next button to save your changes and continue
    Manage Database Mail Profiles
  9. The wizard will now configure the system parameters for the profile and account. When it completes, click on the next button. Configure System Parameters
  10. The wizard will now display the actions it will be performing. Verify the the information presented and click on the Finish button to continue. Verify Database Mail Configuration
  11. When the wizard completes the configuration, click on the Close. Database Mail wizard complete
  12. Once you have completed the database mail configuration, you will need to create a new operator. From SSMS, click on the SQL Server Agent icon to expand it, right-click on the Operators icon, then select New Operator. New Operator
  13. From the New Operator screen, enter the following values:
    1. Name: DBMon
    2. Check the Enable box
    3. E-mail
    When you are finished, click the Ok button. Create DBMAil Operator
  14. Once you have completed creating the Operator, you will need to enable and configure the mail session for the SQL Server Agent. In SSMS, expand right-click on the SQL Server Agent and select properties. Database Mail SSA Enable
  15. In the SQL Server Agent Properties window, click on the Alert System icon to display its properties and set the following values: Enable DBMail in SQL Server Agent
  16. ***CRITICAL****
    You MUST now RESTART the SQL Server Agent for the changes to take effect!

You have now successfully configured the SQL Server for Database Mail. To test your configuration, perform the following steps.

  1. In SSMS, expand the Management tab, right-click on the Database Mail icon and select Send Test E-Mail... Database Mail Test
  2. From the Send Test E-Mail screen, enter your email address in the To: field, then click on the Send Test E-Mail button. A screen will then appear informing you of the system's attempt to send a test e-mail. In a few moments you should receive an email. Database Mail Send Test E-Mail

Using the newly simplified features of SQL 2005 DBMail makes the complexity of writing CODE and individual steps for EACH SSA job to use Blat email obsolete. In the future, each time you create a new job, you set the failure notification to the Operator(DBMon) you created in the previous steps.

Set Job Operator
Home | TechNet | ADO.Net | DOS | ASP.NET | IIS | VB.NET | VIM (vi) | Windows | XHtml
MS-SQL | T-SQL | SSIS | Oracle