SSIS Runtime Connection Error when PackageProtectionLevel set to DoNotSaveSensitive

[ZappySys]

Problem

You have set PackageProtectionLevel to DoNotSaveSensitive  and you are getting login related error during runtime. Your preview or Test Connection works fine at design time but you getting various errors such as below during runtime.

Salesforce Connection

INVALID_LOGIN: Invalid username, password, security token; or user locked out. >> Reason: Password or SecurityToken was empty

MongoDB Connection

An error occurred with the following error message: “Unable to connect to server xxxxxxxxx:27017: Invalid credential for database ‘yyyyyy’

Google Data Connection (Google Analytics)

RefreshToken was empty.

–OR–

The remote server returned an error: (400) Bad Request.
Response Url: https://accounts.google.com/o/oauth2/token

Status Code: BadRequest

Response Body: {
“error” : “invalid_request”,
“error_description” : “Missing required parameter: refresh_token”
}

Amazon Storage Connection (S3, SQS, DynamoDB, Redshift)

SecretKey cannot be empty

Azure Storage Connection (Blob, Queue, Table Storage)

Please enter AccountKey to connect storage service
–or–
No storage account name/access key specified

Secure FTP Connection

A supplied password or user name is incorrect

Possible Cause

• You have not supplied Password or any sensitive information (e.g. Secret / RefreshToken) using dynamic approach (e.g. via Expression, Parameters, Config File or SSIS Environment)

Solution

Read this article to understand how PackageProtectionLevel works and how to parameterize certain property to read value from Package / Project Parameter.

If you have set PackageProtectionLevel = DoNotSaveSensitive  and you getting above error then make sure you supply Password / SecurityToken at runtime using below steps.

1. Change PackageProtectionLevel back to default (i.e EncryptSensitiveUsingUserKey). You have to do this for each Package and Project Level too. To change ProtectionLevel for package, right click in the control flow designer and select Properties. Change  PackageProtectionLevel. To change PackageProtectionLevel for Entire Project right click on the Project Node and Select Properties and change it there too.
2. Once ProtectionLevel is set you can execute package to confirm its working.
3. Now its time to parameterize sensitive property so when you deploy JOB to production you can easily configure passwords / tokens via parameters  and still not exposing passwords in your packages.
4. To create parameter for Password / Token Property. Right click on the Connection Manager in the bottom pane, select Parameterize… option from Popup menu. Now you can select Property (e.g. Password) and Name your name parameter, select Scope (e.g. Package Level, Project Level).

   

5. Save your package and deploy to server.
6. When you schedule a Job or Execute from catalog you will see it prompts to fill out Parameters you created earlier.

   

   Configuring parameter for SSIS package which is run from SSIS Catalog

7. Run your package

So basically to Fix our Password issue at runtime

• Leave PackageProtectionLevel to Default (i.e. EncryptSensitiveUsingUserKey)
• Use Package Parameters to configure Password / SecurityToken
  — OR —
  Use Expression Password / SecurityToken properties
  — OR —
  Use Config file to configure Full ConnectionString or Configure Password / SecurityToken Properties of Salesforce connection

For detailed steps read this article.

[Author]

Posts