Configure Database Mail

When it comes to monitoring servers and software, I like to have them notify me of issues rather than my having to periodically ask them how they are doing. The foundation of that is being able to have them send me email.

SQL Server has a feature called Database Mail that works very well for this purpose, but it can be a bit difficult to configure correctly, especially if you try to do it manually. I will show several ways to make use of Database Mail in future posts, but in this post I will show how to get it configured correctly. The necessary steps are:

1. Enable Database Mail and SQL Agent: This sets the features so they can be used, as they are generally disabled by default.
2. Setup Database Mail
1. Create a Database Mail profile: A profile is a collection of mail accounts, and it must exist before we can add an account to it.
2. Create a Database Mail account: This is the account we are going to use for sending email; it contains all the information about the SMTP server and the address to be used as the sender.
3. Add the account to the profile: The account cannot be used unless it is a member of a profile.
4. Grant the DBMailUsers role access to the profile: This provides the security right that is needed and also makes the profile the default.
3. Send a test message: At this point, Database Mail should be ready to send a message and this will confirm that it works properly. Depending on your mail client, you may need to refresh your InBox view or give it a command to fetch new mail to avoid waiting for the standard interval for checking if new mail has arrived.
4. Configure SQL Agent to use the profile and operator: Without this step, SQL Agent cannot use Database Mail. Unfortunately, it requires that the SQL Agent service be restarted. I didn't add the restart to the script as it is likely that security would prevent it from working in many environments. You can use SSMS (right-click on SQL Agent in Object Explorer and select Restart), SQL Server Configuration Manager, or the Windows Services Control Panel applet to restart the service manually.

The script below is what I use to accomplish all of this except the service restart. Just substitute your own values in the block of SET statements near the top and execute it. Each of these values is described in the script.

/* Configure Database Mail.sql

Purpose:        Configures Database Mail and sends a test message
Author:         Mark Freeman
Last Edited:    2011-11-20
Instructions:   Update the values in the block of SET statements as appropriate,
                 then execute.
References:     Adapted from:
                 http://www.kannade.com/tech/database-mail-and-sql-agent-mail-setup-using-script-t30.html and
                 http://www.mssqltips.com/sqlservertip/1736/sql-server-database-mail-and-sql-agent-mail-setup-by-using-a-script/
Compatibility:  SQL Server 2005 and newer
License:        This work is licensed under a Creative Commons
                 Attribution-NonCommercial-ShareAlike 3.0 Unported License.
                 http://creativecommons.org/licenses/by-nc-sa/3.0/
*/

SET NOCOUNT ON
USE [master]

DECLARE @ProfileName                    SYSNAME,
        @SmtpServer                     SYSNAME,
        @OperatorName                   SYSNAME,
        @ToEmail                        SYSNAME,
        @RecipientDomainName            SYSNAME,
        @SenderDomainName               SYSNAME,
        @ReplyToEmail                   SYSNAME,
        @SmtpUserName                   NVARCHAR(128),
        @SmtpPassword                   NVARCHAR(128),
        @SmtpPort                       INT,
        @SmtpUseDefaultCredentials      BIT,
        @SmtpEnableSsl                  BIT;

-- Please update the values in the section below as needed:
SET @ProfileName                = 'MyProfile';          /* This could be your company name,
                                                            domain name, or something
                                                            generic such as
                                                            'Database Mail Profile' */
SET @SmtpServer                 = 'smtp.mydomain.com';  /* Usually something like
                                                            'smtp.mydomain.com' */
SET @SmtpPort                   = 25;                   /* This is the standard port,
                                                            but yours may differ */
SET @RecipientDomainName        = 'mydomain.com';     /* This may differ from the domain
                                                            of the sender if, for example,
                                                            you arehaving servers at other
                                                            companies send you emails */
SET @SenderDomainName           = @RecipientDomainName; /* This is usually the domain of the
                                                            SMTP server */
SET @SmtpUserName               = NULL;                 /* Only needed if your SMTP server 
                                                            requires authentication */
SET @SmtpPassword               = NULL;                 /* Only needed if your SMTP server 
                                                            requires authentication */
SET @SmtpUseDefaultCredentials  = 0                     /* Change to 1 to use the
                                                            credentials of the SQL Server
                                                            Database Engine and ignore the 
                                                            user name and password
                                                            settings */
SET @SmtpEnableSsl              = 0;                    /* Change to 1 if your SMTP server
                                                            uses SSL encryption */
SET @ToEmail                    = 'dba' + @RecipientDomainName;
SET @ReplyToEmail               = 'sqlserver' + @SenderDomainName;
-- Please update the values in the section above as needed


-- Enable Database Mail and SQL Agent
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Database Mail XPs', 1;
EXEC sp_configure 'Agent XPs',1;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;

-- Setup Database Mail
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile)
    PRINT 'Database Mail already configured';
ELSE BEGIN
    DECLARE @FromEmail  SYSNAME,
            @FromName   SYSNAME;
    SELECT  @FromEmail  = REPLACE(@@SERVERNAME,'\','_') + @SenderDomainName,
            @FromName   = 'SQL Server ' + @@SERVICENAME;

    -- Create a Database Mail profile
    EXEC msdb.dbo.sysmail_add_profile_sp
        @profile_name = @ProfileName
        ,@description = 'Mail profile setup for email from this SQL Server';

    -- Create a Database Mail account, assuming Windows Authentication
    EXEC msdb.dbo.sysmail_add_account_sp
        @account_name              = @ToEmail
        ,@description              = 'Mail account for use by database processes'
        ,@email_address            = @FromEmail
        ,@replyto_address          = @ReplyToEmail
        ,@display_name             = @FromName
        ,@mailserver_name          = @SmtpServer
        ,@mailserver_type          = 'SMTP'
        ,@port                     = @SmtpPort
        ,@username                 = @SmtpUserName
        ,@password                 = @SmtpPassword
        ,@use_default_credentials  = @SmtpUseDefaultCredentials
        ,@enable_ssl               = @SmtpEnableSsl;

    -- Add the account to the profile
    EXEC msdb.dbo.sysmail_add_profileaccount_sp
        @profile_name              = @ProfileName
        ,@Account_name             = @ToEmail
        ,@sequence_number          = 1;

    -- Grant access to the profile to the DBMailUsers role
    EXEC msdb.dbo.sysmail_add_principalprofile_sp
        @profile_name              = @ProfileName
        ,@is_default               = 1;
END

-- Send a test message
DECLARE @MessageText VARCHAR(100);
SET @MessageText = 'Test from SQL Server ' + @@SERVICENAME;
EXEC msdb..sp_send_dbmail
    @profile_name                  = @ProfileName
    ,@recipients                   = @ToEmail
    ,@subject                      = @MessageText
    ,@body                         = @MessageText;

-- Create operator
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysoperators WHERE name = @ToEmail)
    EXEC msdb.dbo.sp_add_operator
        @name                      = @ToEmail 
        ,@enabled                  = 1 
        ,@email_address            = @ToEmail;

EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator = @ToEmail;

-- Configure SQL Agent to use the profile and operator
USE [msdb];
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1;
EXEC master.dbo.xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    ,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
    ,N'UseDatabaseMail'
    ,N'REG_DWORD'
    ,1;
EXEC master.dbo.xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    ,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
    ,N'DatabaseMailProfile'
    ,N'REG_SZ'
    ,@ProfileName;
PRINT '**** You must manually restart the Service: SQL Server Agent ('
    + @@SERVICENAME + ')';