Send Email using Gmail in SQL Server Stored Procedure

In previous section, I introduced how to send email over SSL connection. In this section, I will introduce how to use your Gmail account to send email in SQL stored procedure.

Introduction

Gmail SMTP server address is smtp.gmail.com. It requires implicit SSL or explicit SSL (TLS) connection, and you should use your Gmail email address as the user name for ESMTP authentication.

Server Port SSL/TLS
smtp.gmail.com 25, 587 TLS
smtp.gmail.com 465 SSL

Gmail App Password

To help keep your account secure, starting May 30, 2022, ​​Google will no longer support the use of third-party apps or devices which ask you to sign in to your Google Account using only your username and password.

Therefore, you should sign in using App Passwords. An App Password is a 16-digit passcode that gives a less secure app or device permission to access your Google Account. App Passwords can only be used with accounts that have 2-Step Verification turned on. You need to use App Password instead of the user password for user authentication.

Another solution is Gmail OAUH, please see Gmail SMTP OAUTH section.

Note

Remarks: All of samples in this section are based on first section: Send email in a simple SQL stored procedure. To run the following example codes successfully, please click here to learn how to create the test enivronment and use EASendMail in your project.

[SQL Stored Procedure Example - Send email using Gmail account over SSL connection on 465 port]

The following example codes demonstrate how to use EASendMail SMTP component to send email using Gmail account.

Note

To get the full sample projects, please refer to Samples section.

/* Gmail SMTP server address */
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.gmail.com'

/* Set your Gmail email address */
DECLARE @From nvarchar(128)
Set @From = 'gmailid@gmail.com'

DECLARE @To nvarchar(1024)
/*You can input multiple recipients and use comma (,) to separate multiple addresses */
Set @To = 'support@emailarchitect.net'

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from Gmail account'

DECLARE @Bodytext nvarchar(512)
Set @BodyText = 'This is a test text email from MS SQL server, do not reply.'

/* Gmail user authentication should use your
 Gmail email address as the user name.
For example: your email is "gmailid@gmail.com", then the user should be "gmailid@gmail.com" */
DECLARE @User nvarchar(128)
Set @User = 'gmailid@gmail.com'

/* Create app password in Google account*/
/* https://support.google.com/accounts/answer/185833?hl=en */
DECLARE @Password nvarchar(128)
Set @Password = 'your app password'

/* Enable SSL/TLS */
DECLARE @SSL int
Set @SSL = 1

/* If you want to use TLS, please set it to 25 or 587 */
DECLARE @Port int
Set @Port = 465

PRINT 'start to send email ...'
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

[SQL Stored Procedure Example - Send email using Gmail account over TLS connection on 587 port]

The following example codes demonstrate how to use EASendMail SMTP component to send email using Gmail account.

Note

To get the full sample projects, please refer to Samples section.

/* Gmail SMTP server address */
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.gmail.com'

/* Set your Gmail email address */
DECLARE @From nvarchar(128)
Set @From = 'gmailid@gmail.com'

DECLARE @To nvarchar(1024)
/*You can input multiple recipients and use comma (,) to separate multiple addresses */
Set @To = 'support@emailarchitect.net'

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from SQL server using gmail account'

DECLARE @Bodytext nvarchar(512)
Set @BodyText = 'This is a test text email from MS SQL server, do not reply.'

/* Gmail user authentication should use your
 Gmail email address as the user name.
For example: your email is "gmailid@gmail.com", then the user should be "gmailid@gmail.com" */
DECLARE @User nvarchar(128)
Set @User = 'gmailid@gmail.com'

/* Create app password in Google account*/
/* https://support.google.com/accounts/answer/185833?hl=en */
DECLARE @Password nvarchar(128)
Set @Password = 'your app password'

/* Enable SSL/TLS */
DECLARE @SSL int
Set @SSL = 1

/* Use 587 port */
DECLARE @Port int
Set @Port = 587

PRINT 'start to send email ...'
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

Gmail SMTP OAUTH

The Gmail IMAP and SMTP servers have been extended to support authorization via the industry-standard OAuth 2.0 protocol. Using OAUTH protocol, user can do authentication by Gmail Web OAuth instead of inputting user and password directly in application. This way is more secure, but a little bit complex.

Using Gmail SMTP OAUTH

Next Section

At next section I will introduce how to send email with Yahoo account.

Appendix

Comments

If you have any comments or questions about above example codes, please click here to add your comments.