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
