SQL CLR stored procedure fails to send mail SMTP while console exe succeeds

Question

I'm trying to implement a CLR stored procedure that will send emails upon request using office365 SMTP.

This is my code:

[Microsoft.SqlServer.Server.SqlProcedure()]
public static void SendMail(string recipients, string subject, string from, bool isHtml, string body, string smtpServer, int smtpPort, string smtpUser, string smtpPassword, string cc = "", string bcc = "")
{
    using (MailMessage mailMessage = new MailMessage())
    {
        mailMessage.From = new MailAddress(from);   

        mailMessage.Subject = subject;
        mailMessage.IsBodyHtml = isHtml;
        mailMessage.Body = body;

        string[] strTo = recipients.Split(';');
        foreach (var item in strTo)
            mailMessage.To.Add(new MailAddress(item));

        if (cc != "")
        {
            string[] strCc = cc.Split(';');
            foreach (var item in strCc)
                mailMessage.CC.Add(new MailAddress(item));
        }

        if (bcc != "")
        {
            string[] strBcc = bcc.Split(';');

            foreach (var item in strBcc)
                mailMessage.Bcc.Add(new MailAddress(item));
        }

        SmtpClient smtpClient = new SmtpClient
        {
            Host = smtpServer,
            Port = 587,
            UseDefaultCredentials = false,
            DeliveryMethod = SmtpDeliveryMethod.Network,
            Credentials = new System.Net.NetworkCredential(smtpUser, smtpPassword),
            TargetName = smtpServer,
            EnableSsl = true
        };
        smtpClient.Send(mailMessage);
    }
}

The above code runs without any errors while in a console app (on the same target machine), but when I run it as a CLR stored procedure, I get this error:

Msg 6522, Level 16, State 1, Procedure brx.uspSendMail, Line 0 [Batch Start Line 2]
A .NET Framework error occurred during execution of user-defined routine or aggregate "uspSendMail":

System.Net.Mail.SmtpException: Failure sending mail.

System.IO.IOException: Unable to read data from the transport connection: net_io_connectionclosed.

System.IO.IOException:

at System.Net.Mail.SmtpReplyReaderFactory.ProcessRead(Byte[] buffer, Int32 offset, Int32 read, Boolean readLine)
at System.Net.Mail.SmtpReplyReaderFactory.ReadLines(SmtpReplyReader caller, Boolean oneLine)
at System.Net.Mail.SmtpReplyReaderFactory.ReadLine(SmtpReplyReader caller)
at System.Net.Mail.CheckCommand.Send(SmtpConnection conn, String& response)
at System.Net.Mail.MailCommand.Send(SmtpConnection conn, Byte[] command, MailAddress from, Boolean allowUnicode)
at System.Net.Mail.SmtpTransport.SendMail(MailAddress sender, MailAddressCollection recipients, String deliveryNotify, Boolean allowUnicode, SmtpFailedRecipientException& exception)
at System.Net.Mail.SmtpClient.Send(MailMessage message)

System.Net.Mail.SmtpException:

at System.Net.Mail.SmtpClient.Send(MailMessage message)
at StoredProcedure.SendMail(String recipients, String subject, String from, Boolean isHtml, String body, String smtpServer, Int32 smtpPort, String smtpUser, String smtpPassword, String cc, String bcc)

The strange thing is this same stored procedure worked up till lately (30/5/2022 to be exact) - when google stopped supporting gmail SMTP.

Any thoughts?

Thanks in advance, Erez

"when google stopped supporting gmail SMTP" isn't that your answer? You say you're using Office365 SMTP but nothing about your code shows that specifically. Could there be a reference to Gmail SMTP somewhere in a config? — Simmetric, Jun 15 '22 at 08:51
@ErezM What version and edition of SQL Server are you using? — Solomon Rutzky, Jun 15 '22 at 08:58
Try using port 465. Also is SQLCLR really the place to be sending emails from? — Charlieface, Jun 15 '22 at 11:39
Hi @Simmetric, i only mentioned google's SMTP as this used to work, and now - with only change being using office365 parameters - the code fails. the failure has nothing to do with google. — ErezM, Jun 16 '22 at 07:24
And i tried ports 25, 465, and 587. Main thing is its working in a console app, so it's something with the CLR environment — ErezM, Jun 16 '22 at 07:32

score 1 · Answer 1 · edited Jun 20 '22 at 00:22

1

If anyone stumbles upon this error, I found a solution: Add the following to the top of the code:

System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12

I'm not sure why this is needed, and why gmail's SMTP didn't need it to work properly, but at least all is good at last.

I found this solution on another Stack Overflow thread, by @Simon Fallai.

edited Jun 20 '22 at 00:22

Jeremy Caney

7,102
69
48
77

answered Jun 19 '22 at 19:34

ErezM

63
4

SQL CLR stored procedure fails to send mail SMTP while console exe succeeds

1 Answers1