SQL: Set up Database Mail

Configure Database Mail in your SQL Server

• Open SQL Server Management Studio > Management > Database Mail. Right-click and select Configure Database Mail

 

 

• Enable the feature if it's not available. Create new database mail account, and select Basic Authentication. Supply your network username and password.

• Set up Profile Security

• Test sending mail

Notes:

• Ensure that your Database Mail XPs is configured in your server as an option.
• Ensure Service Broker is enabled in your database.
• If you're getting this error, contact relevant IT teams within the organisation to set up an exchange relay for the server.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-07-27T14:16:13). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.3.2 Service not available). )

• If you'd like to configure Gmail account, use the following:

Server Name: smtp.gmail.com
Port number: 587
Requires a secure connection (SSL). Choose Basic Authentication, and enter your Gmail ID and password.

• Ensure 'Database Mail XPs' is configured.

sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

• To confirm that the Database Mail is started

EXEC msdb.dbo.sysmail_help_status_sp;

To start it:
EXEC msdb.dbo.sysmail_start_sp;

• To view the error messages returned by Database Mail:

SELECT * FROM msdb.dbo.sysmail_event_log;