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

4

8

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.

Tammeratammi answered 26/4, 2017 at 14:25 Comment(0)
S
4

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.

Soleure answered 26/4, 2017 at 14:44 Comment(0)
B
11

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

  1. Create connection using New Connection... option selecting SMTP as type.
  2. Save without any connection settings. Give it any name you want.
  3. Right click the connection and select Parameterize...
  4. Select Property = ConnectionString
  5. Select Create new parameter (e.g. SMTPConnectionManager_ConnectionString)
  6. Set Value to connection string (e.g. SmtpServer=aspmx.l.google.com; port=25; UseWindowsAuthentication=False;EnableSsl=False; [email protected]; password=password123)
  7. Set scope at appropriate level for your deployment method (Package or Project).
  8. Click OK
Barre answered 29/9, 2017 at 10:25 Comment(0)
S
4

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.

Soleure answered 26/4, 2017 at 14:44 Comment(0)
C
4

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;
    }
Cornellcornelle answered 9/7, 2018 at 14:28 Comment(4)
Worth mentioning that for this solution to work, one must add using System.Net; and using System.Net.Mail; to the namespace.Peadar
I found I needed System.Diagnostics too because of Debug.WriteLine()Cerracchio
Thanks but how to send attachment using above script?Adalineadall
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)Ambulator
F
2

Follow Below steps

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

enter image description here

  1. Type your Mail server name and click OK

enter image description here

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

enter image description here

  1. Select ConnectionString from the property list

enter image description here

  1. Add username, password and port to your connection string value

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

enter image description here

Fatidic answered 29/7, 2021 at 18:42 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.