Home Page › Forums › Issues – SSIS PowerPack › SSIS Runtime Connection Error when PackageProtectionLevel set to DoNotSaveSensitive
Tagged: amazon aws, connection, DoNotSaveSensitive, error, google analytics, Microsoft Azure, mongodb, PackageProtectionLevel, Salesforce, secure ftp, ssis
- This topic has 0 replies, 1 voice, and was last updated 6 years, 10 months ago by ZappySys.
-
AuthorPosts
-
November 6, 2017 at 4:46 am #2561ZappySysKeymaster
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/tokenStatus 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 specifiedSecure 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.
- 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.
- Once ProtectionLevel is set you can execute package to confirm its working.
- 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.
- 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).
- Save your package and deploy to server.
- When you schedule a Job or Execute from catalog you will see it prompts to fill out Parameters you created earlier.
- 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.
-
AuthorPosts
- You must be logged in to reply to this topic.