How to make a script to send mail using MFA within a SSIS package

Question

Hello, everyone.

I have a Visual Basic script within a SSIS Script Task to send mail inside the organization. We are usinng Microsoft Azure to manage the users accounts. Now, Microsfot is enforcing the MFA.

Now we're getting this error:
User's image

This is the script that has been executing for years:

#Region "Help:  Introduction to the script task"
'The Script Task allows you to perform virtually any operation that can be accomplished in
'a .Net application within the context of an Integration Services control flow. 
'Expand the other regions which have "Help" prefixes for examples of specific ways to use
'Integration Services features within this script task.
#End Region

#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports System.Net.Mail
Imports Microsoft.SqlServer.Dts.Runtime
#End Region

Public Sub Main()
        '
        ' Add your code here
        '
        Dim varHTMLMail As MailMessage
        Dim varSMTPClient As SmtpClient
        Dim varDestinatarios As Object
        Dim contenido As String
        Net.ServicePointManager.SecurityProtocol = Net.SecurityProtocolType.Ssl3 Or Net.SecurityProtocolType.Tls12 Or Net.SecurityProtocolType.Tls11 Or Net.SecurityProtocolType.Tls
        varDestinatarios = "user1@test.com"
        Try
            varHTMLMail = New MailMessage()
            varHTMLMail.From = New MailAddress("sender@test.com")
            varHTMLMail.Subject = "Insert Text here"
            contenido = ""
            contenido = contenido + Dts.Variables("Insert Text0").Value.ToString()
            contenido = contenido + ""
            contenido = contenido + Dts.Variables("Insert Text1").Value.ToString()
            'contenido = contenido + ""
            contenido = contenido + ""
            contenido = contenido + Dts.Variables("Insert Text2").Value.ToString()
            'contenido = contenido + ""
            'MsgBox(contenido)
            varHTMLMail.Body = contenido
            varHTMLMail.To.Add("user1@test.com")
            varHTMLMail.To.Add("user2@test.com")
            varHTMLMail.To.Add("user3@test.com")
            varHTMLMail.To.Add("user4@test.com")
            varHTMLMail.To.Add("user5@test.com")
            varHTMLMail.IsBodyHtml = True
        Catch e As Exception
            MsgBox(e.ToString())
        End Try
        Try
            varSMTPClient = New SmtpClient("smtp.office365.com", 587)
            varSMTPClient.Credentials = New Net.NetworkCredential("sender@test.com", "Mypass")
            'varSMTPClient.UseDefaultCredentials = True
            varSMTPClient.EnableSsl = True
            varSMTPClient.Send(varHTMLMail)
        Catch e As Exception
            MsgBox(e.ToString)
        End Try
        Dts.TaskResult = ScriptResults.Success
    End Sub
#Region "ScriptResults declaration"
    'This enum provides a convenient shorthand within the scope of this class for setting the
    'result of the script.
    'This code was generated automatically.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
#End Region
End Class

Answer

Looks like SMTP auth is disabled. See the following article to mitigate this:

https://learn.microsoft.com/en-us/exchange/clients-and-mobile-in-exchange-online/authenticated-client-smtp-submission

Share via

How to make a script to send mail using MFA within a SSIS package

1 answer

Your answer