The Database Mail component is used in Microsoft SQL Server to send an SMTP notification to an administrator’s mailbox. You can enable and configure Database Mail to receive various email notifications about events on the MSSQL server.
- Run the Microsoft SQL Server Management Studio and connect to your MSSQL instance;
- Expand Management -> Database Mail;
- Right-click and select Configure Database Mail;
- This will run the Database Mail Configuration Wizard. Select Set up Database Mail by performing the following tasks;
- Create a new profile and specify the SMTP connection parameters: e-mail address, SMTP server address, TCP port, authentication settings, etc. If you have a relay configured to forward e-mail, you can enable anonymous authentication on it for the IP address of your SQL Server host. You can also create a smart host with postfix to send an e-mail via Microsoft (Office) 365;
- Set this SMTP profile as default;
- Set the following options in the Configure System Mail Parameters:
Logging Level: Normal
Account Retry Delay (seconds): 3600
Now you can send a test e-mail message. Right-click Database Mail and select Send Test E-mail.
Specify the recipient’s email address and check that the test email was successfully delivered to the user’s mailbox.
You can also send an e-mail using a simple T-SQL query:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'target@contoso.com',
@body = 'Check database mail configuration.',
@subject = 'MSSQL Test Message';
GO