How to Send Email From SQL Server

You can easily send an E-mail in SQL Server using SMTP Server.

Here's a general approach using SQL Server Database Mail to help you send emails.

Opening your Microsoft SQL Server Management Studio, then login to your sql server -> select Management item.

How to configure database mail in SQL Server

Right-clicking on Database Mail

then select Configure Database Mail

Clicking Next button.

Selecting option Set up Database Mail by performing the following tasks, then click Next button.

Selecting Yes button.

Clicking Add button

sql server email security

Entering your email information.

Don't forget to tick 'This server requires a secure connection (SSL)

SMTP Server Details: You need to have access to an SMTP server that allows relaying from your SQL Server machine.

This SMTP server should provide you with:

- Server address (SMTP host)

- Port number (typically 25 for unencrypted SMTP, or 587 for SMTP with TLS)

- Authentication credentials (username and password, if required)

- Encryption method (if TLS is required)

Ensure that SQL Server has Database Mail configured and enabled.

sql server database mail profile

Entering your profile name, then click Next button.

Selecting your profile, then click Next button.

Clicking Next button.

Clicking Finish button.

Clicking Close button to finish.

Right-clicking on Database Mail, then enter your email need to send.

Clicking OK button, then check your mail box.

You can also run the sql script below to configure database mail.

sp_CONFIGURE 'show advanced', 1 
GO 
RECONFIGURE 
GO 
sp_CONFIGURE 'Database Mail XPs', 1 
GO 
RECONFIGURE 
GO 

Running the sql script above and you will get the following results.

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Database Mail XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.

Completion time: 2020-09-16T14:46:21.8130404+07:00

sql server send email from stored procedure

Once Database Mail is configured and tested, you can send emails from SQL Server using the sp_send_dbmail stored procedure.

If you want to send an email from stored procedure you can runt the sql script below.

USE msdb
GO
EXEC sp_send_dbmail @profile_name='lucy',
@recipients='[email protected]',
@subject='Test message',
@body=N'Welcome to FoxLearn...'

You can verify the email status, whether it sent successfully or not, and get other information using the query below:

use msdb  
go  
select * from sysmail_allitems  

If you want to view mail log, you can right-click on Database Mail, then select “View database Mail Log”.

Here are some details about the SMTP server for your reference.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2020-09-16T14:50:18). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Authentication Required

If you got an error above when sending an email, you should login to your google mail acount, then check 'Less secure app access'

You need to click enable it to allow you to send email from another software.