How Can I Specify Credentials for Simple Authentication in SSIS SMTP Connection Manager?

Question

We have several asp.net web apps that send emails, and the MailMessage object is configured with an SMTP server, username and password. The emails are sent with no problems.

In an SSIS package, I added an SMTP connection manager, and I configured the smtp server. I set UseWindowsAuthentication=True because I don't see where I type in username/password.

When I run the package from SQL Server Agent, the SSIS sends the email correctly, so apparently, the user/password is not needed.

So how can the SMTP package send an email without the user credentials? Does it make sense that the asp.net don't need the credentials either?

We're all under the same company network and we use Exchange Server.

Thanks.

score 10 · Answer 1 · answered Sep 29 '17 at 10:25

Create a SMTP Connection Manager with a parameterized ConnectionString property with a string which contains the smtp user and password.

Create connection using New Connection... option selecting SMTP as type.
Save without any connection settings. Give it any name you want.
Right click the connection and select Parameterize...
Select Property = ConnectionString
Select Create new parameter (e.g. SMTPConnectionManager_ConnectionString)
Set Value to connection string (e.g. SmtpServer=aspmx.l.google.com; port=25; UseWindowsAuthentication=False;EnableSsl=False; user=user@gmail.com; password=password123)
Set scope at appropriate level for your deployment method (Package or Project).
Click OK

score 4 · Accepted Answer · answered Apr 26 '17 at 14:44

Check out this link.

It explains that the package is using the Sql Server Agent account to connect to the host. Furthermore, the SMTP connection manager supports only anonymous authentication and Windows Authentication. It does not support basic authentication - as stated in the documentation.

score 4 · Answer 3 · edited Apr 24 '19 at 19:31

The answer from Alan Gaylor didn't work for me, but doing the following in a script task, not an email task, worked:

using System.Diagnostics;
using System.Net;
using System.Net.Mail;


    public void Main()
    {
        string UserName = Dts.Variables["UserName"].Value.ToString();
        string Password = Dts.Variables["Password"].Value.ToString();
        string EmailRecipient = Dts.Variables["EmailRecipient"].Value.ToString();
        string EmailSender = Dts.Variables["EmailSender"].Value.ToString();

        string SMTPEndPoint = Dts.Variables["SMTPEndPoint"].Value.ToString();
        Int32.TryParse(Dts.Variables["SMTPPort"].Value.ToString(), out int SMTPPort);

        string MessageSubject = Dts.Variables["MessageSubject"].Value.ToString();
        string MessageBody = Dts.Variables["MessageBody"].Value.ToString();

        MailMessage msg = new MailMessage();
        msg.To.Add(new MailAddress(EmailRecipient));
        msg.From = new MailAddress(EmailSender);
        msg.Subject = MessageSubject;
        msg.Body = MessageBody +
            "\n" +
            "\n" +
            "DISCLAIMER: The information contained in this transmission may contain privileged and confidential information. " +
            "It is intended only for the use of the person(s) named above.If you are not the intended recipient, " +
            "you are hereby notified that any review, dissemination, distribution or duplication of this communication " +
            "is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.";


        SmtpClient client = new SmtpClient(SMTPEndPoint, SMTPPort)
        {
            EnableSsl = true,
            DeliveryMethod = SmtpDeliveryMethod.Network,
            Credentials = new NetworkCredential(UserName, Password)
        };
        try
        {
            client.Send(msg);
        }
        catch (Exception e)
        {
            Debug.WriteLine(e);
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

Worth mentioning that for this solution to work, one must add `using System.Net;` and `using System.Net.Mail;` to the namespace. — pmdci, Jan 29 '19 at 12:19
I found I needed `System.Diagnostics` too because of `Debug.WriteLine()` — Trevor Reid, Apr 24 '19 at 17:33
It is also worth saying that if you have the smtp port set to 25, you must have ssl be false or `EnableSsl = false` (We struggled with this for a couple of hours) — Daniel L. VanDenBosch, May 06 '20 at 14:23

score 2 · Answer 4 · answered Jul 29 '21 at 18:42

Follow Below steps

Create a Send Mail Task, then create a new smtpConnection.

Type your Mail server name and click OK

Right-click on the SMTP Connection Manager and click Parameterize.

Select ConnectionString from the property list

Add username, password and port to your connection string value

SmtpServer=mail.yourServerName.com;UseWindowsAuthentication=False;EnableSsl=False;port=portnumber;user=YourUserName;Password=YourPassword;

How Can I Specify Credentials for Simple Authentication in SSIS SMTP Connection Manager?

4 Answers4

Linked