1. Home
  2. EASendMail SMTP Component
  3. KB
  4. MS SQL Server
  5. Send Email over SSL in SQL Server Stored Procedure

Tutorial Index

Send Email over SSL/TLS 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.

SSL and TLS Introduction

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

You can use ConnectType property to set different connection type.

TLS 1.2

TLS is the successor of SSL, more and more SMTP servers require TLS 1.2 encryption now.

If your operating system is Windows XP/Vista/Windows 7/Windows 2003/2008/2008 R2/2012/2012 R2, and you got connection error with SSL/TLS connection, you need to enable TLS 1.2 protocol in your operating system like this:

Enable TLS 1.2 on Windows XP/Vista/7/10/Windows 2008/2008 R2/2012

[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, 'ConnectType', 2

/* 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, 'ConnectType', 3

/* 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_OASetProperty @oSmtp, 'ConnectType', 1

EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 25
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 1

/* 4 means if server supports SSL/TLS connection, SSL/TLS is used automatically */
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 25
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 4

EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 587
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 4

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.

See Also