AWS RDS Basic TSQL DBA Tasks SQL Server

SQL Server Database Mail Configuration

By David Speight / April 8, 2025

Today I want to be able to use database mail to send alerts to my team from custom code I’ve written. But FIRST, here are some PROs and CONs to using database mail.

CONS

  • Email virus and vulnerability issues.
  • Denial of Service attacks possible.
  • May expose your database to public facing internet
  • Uses resources

PROS

  • Allows custom emails to be sent directly from SQL Server
  • Allows custom alerts to be sent to team
  • Allows use of the SQL Server Agent Notification feature

Keeping the CONS in mind, I have locked my server down behind a firewall and only allow internal alert emails to be sent to emails on my domain. I will not be sending emails to the general public. I will be checking with my Network Administrator on how I can relay these emails outbound more securely.

INITIAL SET UP

In order to use DATABASE MAIL, I must first enable this feature. Here is how I will do this on a regular SQL Server as well as how it may be done on a AWS RDS Server.

In both instances I will be setting the database configuration option to enable DATABASE MAIL XPS.

USING SSMS FOR EC2 and ON-PREM SQL

-- INITIAL set up of database mail
sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'Database Mail XPs', 1; 
GO 
RECONFIGURE; 
GO

USING AWS RDS

  • Open the CUSTOM PARAMETER GROUP associated with your SQL Server instance.
  • Search on the word “Mail”
  • Change the value for DATABASE MAIL XPS from 0 to 1.
  • SAVE CHANGES

SET UP EMAIL ACCOUNT

ADD A PROFILE

-- Add a profile
EXECUTE msdb.dbo.sysmail_add_profile_sp 
@profile_name = 'LoveNote', 
@description = 'Profile used for sending outgoing notifications.';

exec msdb.dbo.sysmail_help_profile_sp;

ADD PRINCIPAL TO PROFILE

--Add principles to the profile; use public so any user can access the profile:
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
@profile_name = 'LoveNote', 
@principal_name = 'public', 
@is_default = 1;

exec msdb.dbo.sysmail_help_principalprofile_sp;

CREATE DATABASE MAIL ACCOUNT

--Create the Database Mail account (make sure to enter the correct SMTP credentials):
EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = 'LoveNoteAcct', 
@description = 'Mail account for sending outgoing notifications.', 
@email_address = 'dave@lovethesql.com', 
@display_name = 'Automated Mailer', 
@mailserver_name = 'smtp.myemailserver',
@port = 465, 
@enable_ssl = 1,
@username = 'dave@lovethesql.com',
@password = 'MyPassword'; 

exec msdb.dbo.sysmail_help_account_sp;

LINK MAIL ACCOUNT TO PROFILE

--Add Database Mail account to the Database Mail profile:
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
@profile_name = 'LoveNote', 
@account_name = 'LoveNoteAcct', 
@sequence_number = 1 ; 

exec msdb.dbo.sysmail_help_profileaccount_sp

TEST SENDING EMAIL

-- Send TEST Email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'LoveNote',
@recipients = 'dave@lovethesql.com',
@body = 'The database mail configuration was completed successfully.',
@subject = 'DB MAIL TEST Automated Success Message';

-- View Log
SELECT top 2  last_mod_date, sent_status, * FROM msdb.dbo.sysmail_allitems order by 1 desc;
select top 6 * from msdb.dbo.sysmail_event_log   order by 1 desc ;

In this example, notice that the email test FAILED. When this occurs, I view the message in the LOG DESCRIPTION to help identify the issue. In my case, I needed to contact my NETWORK ADMIN to help out since there was extra security on my network and the IP ADDRESS I was sending from needed to be WHITE-LISTED. I has already expected that I might need to coordinate this effort with my NETWORKING TEAM.

Additional Helpful Queries

-- CHECK database mail settings
exec msdb.dbo.sysmail_help_profile_sp
exec msdb.dbo.sysmail_help_principalprofile_sp
exec msdb.dbo.sysmail_help_account_sp
exec msdb.dbo.sysmail_help_profileaccount_sp

-- DELETION COMMANDS
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'LoveNote';
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'LoveNoteAcct';
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'LoveNote';
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'LoveNote';

-- UPDATE PASSWORD
EXECUTE msdb.dbo.sysmail_update_account_sp 
@account_name = 'LoveNoteAcct', 
@description = 'Mail account for sending outgoing notifications.', 
@email_address = 'dave@lovethesql.com', 
@display_name = 'Automated Mailer', 
@mailserver_name = 'smtp.myeamilserver',
@port = 465, 
@enable_ssl = 1,
@username = 'dave@lovethesql.com',
@password = 'MyNewPassword';

Tags: , , , , , ,

You may also like