1

So I have a script task in an SSIS package that accesses email. 

  ExchangeService service = new Microsoft.Exchange.WebServices.Data.ExchangeService(ExchangeVersion.Exchange2010_SP1);
  service.Credentials = new NetworkCredential("email@domain.com", "123");

I have encrypted the package itself, but am still feeling uneasy that it's in plain text on the ScriptMain.cs page. In the past I was able to encrypt this data on say WPF applications, but not sure how that can translate to a script task. Am I over thinking this? Is there a way to set this up in connection managers or something else that would add one more layer of protection?

I wanted to add I'm using the exchange services API to connect to the email.

user3486773
  • 1,174
  • 3
  • 25
  • 50

2 Answers2

1

You can use so called sensitive package or project parameters; environment params mapped to such params are stored in SSIS Catalog encrypted. Just create one for your password and get it in your Script task with .GetSensitiveValue() method like 

Dts.Variables["$Package::YourPassword"].GetSensitiveValue().ToString()

as described by Matt Masson.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • This apparently is only available in the [project deployment mode](https://learn.microsoft.com/en-us/sql/integration-services/packages/deploy-integration-services-ssis-projects-and-packages?view=sql-server-2017#compare-project-deployment-model-and-legacy-package-deployment-model). I wonder how to achieve it in the package deployment mode too? – GSerg Aug 02 '19 at 18:31
0

According to EWS Managed API clients , 'Domain-joined clients that target an on-premises Exchange server can use the default credentials of the user who is logged on, assuming the credentials are associated with a mailbox'

So you may be able to use;

service.UseDefaultCredentials = true;

If that isn't possible, I'd recommend storing it in a database to be retrieved. You'll be able to secure/encrypt it in a Database.

Also, if you're using SQL 2012 or later, you could use a sensitive parameter. This is assuming you want to parameterize the password and/or username.

    public void Main()
    {
        try
        {
            string value = Dts.Variables["$Package::ExchangePassword"].GetSensitiveValue().ToString();

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception e)
        {
            Dts.Log(e.Message, 0, null);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }
dpimente
  • 487
  • 5
  • 9