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.

Sections:

Introduction
Gmail App Password
[SQL Stored Procedure Example - Send email using Gmail account over SSL connection on 465 port]
[SQL Stored Procedure Example - Send email using Gmail account over TLS connection on 587 port]
Gmail SMTP OAUTH

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.

Tutorial Index

Send Email using Gmail in SQL Server Stored Procedure¶

Introduction¶

Gmail App Password¶

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

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

Gmail SMTP OAUTH¶

Share

See Also