Configure Database Mail in SQL Server

How to configure Database Mail in SQL Server ?

Introduction : Database mail is a SQL Server component that uses the 'Simple Mail Transfer Protocol' or SMTP to send email messages. Follow the below given steps to configure database mail:

STEP 1 : Connect to SQL Server Management Studio (Preferably run as Administrator). Go to Management and double click on Database Mail. A wizard as shown below will open. Click Next.

STEP 2 : Select Configuration Task window will appear. If you are installing Database Mail for the 1st time select the set up option radio button : Set up database mail by performing the following tasks. In case you want to modify or manage a previously configured database mail chose other options as applicable to you.

STEP 3 :  Choose Create a new account :

STEP 4 : Provide the following values

Account Name : Any Name
Description : Optional
Email address : Your Email Address
Display Name : Any Name
Reply Email Address : Optional
Server Name : Where\How to get server name is described below :
Port : Usually 25
Tick server requires SSL
SMTP Authentication Method : Choose any, I have chosen basic authentication

Now  Where\How to get server name ? The easiest approach :

Send a mail from your outlook in the format : wrongname@yourcompany.com (Please note that part of email id before @ should be wrong and not the part after @ )

You will get back MAIL DELIVERY FAILURE MESSAGE as shown below from the Server. Find the GENERATING SERVER name in this mail.

Provide this server name in Server Name.

STEP 5 : Complete the wizard. Finish.

And you Database mail is configured now.

Now lets TEST : Run this query to test :


EXEC msdb.dbo.sp_send_dbmail 

 @recipients='Provide email id here whom you want to send mail',  

 @profile_name = 'Account Name you gave in STEP 4',  

 @subject = 'Any Subject Line',  

 @body = 'Type Message Body Text',  

 @file_attachments='Attachment Full File path with file name';

The recipient must receive mail provided if all is good. To check the quue and status of mail like : Sent\Unsent\failed\Retry etc, Run this query :

USE MSDB
GO

Select * from sysmail_allitems

To check error messages :

USE MSDB;

GO

SELECT items.subject,

    items.last_mod_date

    ,l.description FROM dbo.sysmail_faileditems as items

INNER JOIN dbo.sysmail_event_log AS l

    ON items.mailitem_id = l.mailitem_id

WHERE items.recipients LIKE '%%' 

    OR items.copy_recipients LIKE '%%'

    OR items.blind_copy_recipients LIKE '%%'

GO

 In some cases if you are using ssis send mail task or using a ssis job to send mails with attachment; the SQL Server account or SQL Server Agent User may not have access to the Folder from which you are taking the file to be attached. You may get error : SQL Service account not having the correct access to the shared drive :

To solve this :

Grant access to the folder : 

NT SERVICE\MSSSQLSERVER if running SSIS Package or Running a SQL SERVER to send mails :

If using SSIS JOB then add NT Service\SQLSERVERAGENT User :

Follow the screenshots :

STEP 1 : Right click the folder containing the attachment and go to properties Click Edit then Add :

STEP 2 : Browse for NT SERVICE\MSSSQLSERVER or NT Service\SQLSERVERAGENT, change location if required; Check Names and OK: Once users added tick Full control and Modify under Allow : Apply

That's it.

Thanks for reading. Please like and follow & Suggest improvements.

Follow me on twitter @msbigyan

Thanks.

Paras Singh

Senior Consultant : BI & Analytics