You are creating SQL Server 2008 Integration Services (SSIS) packages which is stored in a
folder on the network for Company.com.
All members of the team can view the packages but not the attribute values.
Your task is to make the attribute values be protected by password.
Which is the correct answer below?
A.
You should Use a batch file that runs the dtutil program to set the
PackageProtectionLevel property to EncryptSensitiveWithPassword on each of the SSIS
packages you have
B.
You should Use a batch file that runs the dtutil program to set the
PackageProtectionLevel
property to ServerStorage on each of the SSIS packages you have created.
C.
You should Use the SSIS package designer to set the PackageProtectionLevel property
to EncryptAllWithUserKey on each of the previously saved packages.
D.
You should Use the SSIS package designer to set the PackageProtectionLevel property
to EncryptSensitiveWithUserKey on each of the previously saved packages.
Explanation:
You can deploy single SSIS packages manually, but a better choice is to use the DTUtil
command- line tool, which lets you write a command for the deployment and then automate
the process. You could deploy the package to the local SQL Server using Windows
Authentication by including a simple command, such as this: dtutil.exe / FILE
c:\IntegrateCRM.dtsx / COPY SQL;IntegrateCRM Other command-line switches let you
specify the destination SQL Server and SQL Server user name and password as needed.
Table 4-2 ProtectionLevel Options
Option Description
DontSaveSensitive Sensitive information is not saved in the package at all. Each time the
package is reopened in BIDS, the connection passwords must be reentered. For package
execution, the password needs to be stored in a package configuration or passed to the
package through the command-line setting. This is the recommended setting if you are
exclusively using Windows Authentication for your connections. EncryptAllWithPassword
The entire package is encrypted with a password that is set in the PackagePassword
property. To open the package in BIDS, you need to enter the correct package password,
and the package cannot be executed without passing the password to the package at
execution EncryptAllWithUserKey The entire package is encrypted based on the current
user and computer. Only the user who last designed or exported the package can design or
execute the package. A package cannot be opened in BIDS unless it is opened by the user
who developed it, on the computer on which it was developed. The package can be
executed only through the same user account on the same computer.
EncryptSensitiveWithPassword The sensitive information in the package is encrypted based
on a package password, which is set in the PackagePassword property. Each time the
package is reopened, a password must be provided. If the password is not provided, the
package will open, but all sensitive data will be replaced with blanks. If a user attempts to
execute the package without a password,the execution will fail.
EncryptSensitiveWithUserKey The sensitive information (connection passwords) in the
package is encrypted based on the current user and computer. If the same user reopens the
package, nothing is changed. If a different user opens the package, all the sensitive
information is cleared out. Other users can still design or execute the package, but they
need to enter the password or pass the password into the package through the command
line or SSIS configurations. ServerStorage This setting can be used only when a package is
stored in a SQL Server database. Server storage means that nothing in the package
definition is encrypted. Instead, the entire package is protected by using a SQL Server
database role. This option is not supported when a package is saved to the file system. If
encryption is enabled in the package by using any of the ProtectionLevel options except
ServerStorage and DontSaveSensitive, the encrypted data is saved along with the rest of
the package’s information in the .dtsx file. If the entire package is encrypted, all the .dtsxfile’s XML code is encrypted, and the XML cannot be read by a text editor. SSIS encrypts the
data by using the Microsoft Data Protection Application Programming Interface (DPAPI).