You are developing a project that contains multiple SQL Server Integration Services (SSIS)
packages. The packages will be deployed to the SSIS catalog. One of the steps in each
package accesses an FTP site to download sales transaction data.
You create project parameters to store the username and password that are used to access
the FTP site.
You need to ensure that the username and password values are encrypted when they are
deployed.
What should you do?
A.
Set the Sensitive property of the parameters to True.
B.
Set the ProtectionLevel property of the package to EncryptSensitiveWithUserKey.
C.
Change the parameters to package parameters.
D.
Change the project to the Legacy Deployment model.
I’d say B…?
AGREED
why cant A be right?
setting the ProtectionLevel property of the package to EncryptSensitiveWithUserKey provides a higher level of encryption.
There is implicit encryption performed by the SSISDB once a package deployed via the Project Deployment Model has the ‘Sensitive Property’ of a parameter set to True. The encryption key is generated with the password requested when creating your SSISDB catalog inside SSMS. It is implicit, and it works fine.
hmmm, so A might be right as well?
I am now puzzled thank you 🙂
agreed..
B – Set the ProtectionLevel property of the package to EncryptSensitiveWithUserKey
Encrypt sensitive with user key (EncryptSensitiveWithUserKey) uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption.
Encrypt SENSITIVE With User Key is not the right answer!!
You have to set a parameter to ‘sensitive’ in order for it to be encrypted by SSDT.
EncryptSensitiveWithUserKey is the default ProtectionLevel setting for all packages. If you do not specify that a parameter (package or project-scoped), is ‘sensitive’, it would NOT be encrypted.
So, option A is the right answer!!
Yeah…the correct answer is A, and another important point is that “the package WILL be deployed to the SSIS Catalog”.
Based on MS documentation, the SSISDB catalog uses the ServerStorage protection level. When you deploy an Integration Services project to the Integration Services server, the catalog automatically encrypts the package data and sensitive values.
https://msdn.microsoft.com/pt-br/library/ms141747(v=sql.110).aspx
So, the parameter must has the ‘sensitive’ propertie set in order to be automatically encrypted.
Agreed, A is correct
ANSWER IS – A
From a first sight, i went definitely for answer B.
But after i did some tests in my local machine, by deploying my SSIS solution to SSIS Catalog using EncryptSensitiveWithUserKey only, i found out that my connection strings in Project.Parameters were still plain, no encryption was applied for my Parameters.
So A. is the most appropriate solution when it comes to encrypt Parameters when deploying to SSIS Catalog