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.

Solution 1

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.

Solution 2

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

Solution 3

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;
    }

Solution 4

Follow Below steps

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

2. Type your Mail server name and click OK

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

4. Select ConnectionString from the property list

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

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

View more solutions