Developing Matt

My Technical Journal

How to set up Database Mail in Sql 2005

leave a comment »

I’ve had to struggle with getting this to work. It should be clear cut, but can be a bit confusing when working with it, so I wrote this out when I did it last.  We were using Redemption to send emails, but since we have switched to sql server 2005 we have begun using database mail. So far I am very happy with it’s performance.

 


Enable Database Mail stored procedures

  • start…programs…Microsoft Sql Server…Configuration…Surface Aea Tool
  • Surface Area configuration for Features
  • Database mail…enable

Enable Service broker

  • Defined: “It provides a message-based communication platform that enables independent
    application components to perform as a functioning whole” (link)
    providing queuing and messaging components that can be used inside a single instance or
    inside a data/web farm (link)
  • Service Broker must be enabled in the msdb database, however database mail doesn’t
    use the networking piece of service broker, therefore no service broker endpoint is
    needed (link and link).
    To enable service broker:

    • Alter database msdb set enable_broker
      (do this for msdb as this is where the stored procedures for database mail are located)
    • If this process hangs stop the sql server agent long enough for it to run and restart
      the agent (link)
  • Ensure it worked: select is_broker_enabled, name from msdb.sys.databases

Configure Database Mail

  • WITH GUI: In the Management Studio under Management select Database Mail to configure
    database mail

    • Use the first option to set up everything
    • Deleting a profile doesn’t seem possible with the GUI, so don’t screw up. Even
      though you get confirmation on a deleted profile it still exists.
    • Make sure to enable your profile as public
  • WITH T-SQL: Start a new query in the msdb database
    • From http://www.sql-server-performance.com/da_email_functionality.asp
    • declare @Accountname varchar(15)
      set @Accountname = ‘account name you want to use’
      declare @Servername varchar(15)
      set @servername = ‘exchange server name’
      declare @accountemail varchar(15)
      SET @accountemail = ‘account email you are using’

      EXECUTE msdb.dbo.sysmail_add_account_sp

      @account_name = @Accountname,

      @description = @Accountname,

      @email_address = @accountemail,

      @display_name = @Accountname,

      @mailserver_name = @servername

      EXECUTE msdb.dbo.sysmail_add_profile_sp

      @profile_name = @Accountname,

      @description = @Accountname

      EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

      @profile_name = @Accountname,

      @account_name = @Accountname,

      @sequence_number = 1

      EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

      @profile_name = @Accountname,

      @principal_name = ‘public’,

      @is_default = 1 ;

Test Database Mail and check the log (right click database mail object in sql management
studio)

 

If calling procedure from within another security context you will need to enable
execute on msdb.sp_send_dbmail to USER (i.e. forms authentication uses a single
windows login for all users)

Advertisements

Written by matt

July 1, 2007 at 2:20 pm

Posted in Sql Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: