E-mails in MSSQL– How to setup Database Mail

MSSQL has a service called Database mail and it is used from MSSQL 2005 till today. This service is predecessor of SQL Mail.
I will write about Database mail, and how to set it up.
First of all, you have to open a port 25 (SMTP), so you can send mail from server to the “world”.
Second, we must enable Database Mail

sp_CONFIGURE ‘show advanced’, 1
GO
RECONFIGURE
GO
sp_CONFIGURE ‘Database Mail XPs’, 1
GO
RECONFIGURE
GO

After that, we must give permission of sending mail to SQL Agents service account. My Agent is running under domain service account. Add that user to DatabaseMailUserRole in msdb database
image

image

After adding the user to msdb database, next we must create mail profile.
image
image
image

if your mail server requires authentication, enter it in SMTP authentication, if not leave it blank.
image

image
 
Leave it blank, because you setup an account in msdb database.
image
Set Database mail system parameters.
image 

Last, but not least you have to enable sending alerts from SQL Agent.
image

Now, test email settings
USE msdb
GO
EXEC sp_send_dbmail @profile_name=’SQL TEST’,
@recipients=’test@sql.com’,
@subject=TEST,
@body=’Body, Successfully sent an e-mail’

Database Mail, of course saves all outgoing e-mails.
You can check it in msdb database, in tables
sysmail_allitems
sysmail_sentitems
sysmail_unsentitems
sysmail_faileditems
And of course you can check the log in table sysmail_log.
image

Good Luck

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.