SSIS Runtime Connection Error when PackageProtectionLevel set to DoNotSaveSensitive

Home Page Forums Issues – SSIS PowerPack SSIS Runtime Connection Error when PackageProtectionLevel set to DoNotSaveSensitive

This topic contains 0 replies, has 1 voice, and was last updated by  ZappySys 2 years, 9 months ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #2561

    ZappySys
    Keymaster

    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).

      Parameterizing OAuth Connection Manager property "RefreshToken"

    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

      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.

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.