Send Email over SSL in SQL Server Stored Procedure

In previous section, I introduced the basic things of email sending in SQL server stored procedure. In this section, I will introduce the SSL connection.

Introduction

SSL and TLS

SSL connection encrypts data between the SMTP component and SMTP server to protects user, password and email content in TCP/IP level. Now this technology is commonly used and many SMTP servers are deployed with SSL such as Gmail, Yahoo and Hotmail. There are two ways to deploy SSL on SMTP server:

  • Explicit SSL (TLS)

    Using STARTTLS command to switch SSL channel on normal SMTP port (25 or 587);

  • Implicit SSL

    Deploying SSL on another port (465 or other port, you may query it from your server administrator

[SQL Stored Procedure Exmaple - SSL/TLS]

/* Send email by normal TCP/IP without SSL connection */
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 25

/* Send email by SSL connection with STARTTLS command switching */
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 25
EXEC @hr = sp_OASetProperty @oSmtp, 'SSL_starttls', 1
EXEC @hr = sp_OAMethod @oSmtp, 'SSL_init', NULL

/* Send email by SSL connection with direct SSL */
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 465
EXEC @hr = sp_OASetProperty @oSmtp, 'SSL_starttls', 0
EXEC @hr = sp_OAMethod @oSmtp, 'SSL_init', NULL

/* Send email by SSL/TLS connection with auto-detect.
 If port is 25 or 587, STARTTLS TLS will be used; otherwise direct SSL will be used. */

EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 465
EXEC @hr = sp_OAMethod @oSmtp, 'SSL_init', NULL
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 25
EXEC @hr = sp_OAMethod @oSmtp, 'SSL_init', NULL

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 over direct SSL on 465 port]

The following example codes demonstrate how to send email with direct SSL connection on 465 port.

Note

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

DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

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'

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

/*User and password for ESMTP authentication, if your server doesn't require
User authentication, please set @User and @Password to '' */
DECLARE @User nvarchar(128)
Set @User = 'test@emailarchitect.net'
DECLARE @Password nvarchar(128)
Set @Password = 'testpassword'

/* Set SSL connection*/
DECLARE @SSL int
Set @SSL = 1

/* Set 465 port */
DECLARE @Port int
Set @Port = 465
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

[SQL Stored Procedure Example - Send email over TLS on 25 or 587 port]

The following example codes demonstrate how to send email with TLS (STARTTLS command) connection on 25 port.

Note

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

DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

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'

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

/*User and password for ESMTP authentication, if your server doesn't require
User authentication, please set @User and @Password to '' */
DECLARE @User nvarchar(128)
Set @User = 'test@emailarchitect.net'
DECLARE @Password nvarchar(128)
Set @Password = 'testpassword'

/* Set SSL connection*/
DECLARE @SSL int
Set @SSL = 1

/* Set 25 port */
DECLARE @Port int
Set @Port = 25

exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

Next Section

At next section I will introduce how to send email using Gmail account.

Appendix

Comments

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