CHAPTER 4 Administering, Securing, and Executing SSIS Packages

Chapter Summary

• The SSIS service assists in the management and securing of SSIS packages deployed to SQL Server. You can include this service in a Windows cluster installation as needed.
• When you import or deploy packages to SQL Server, you can secure them by using msdb roles.
• By using the package ProtectionLevel setting, you can encrypt packages with a password or by the user account and server. You can encrypt either the entire package or just the sensitive information in the package.
• Besides providing package deployment capabilities, DTUtil can also set the encryption settings and digitally sign packages through command-line scripts.
• The DTExecUI command-line utility provides a user interface that builds SSIS commandline execution commands.
• The DTExec command-line utility can reference and execute a package, giving you several parameters for controlling the execution, such as changing connections, setting logging options, and applying configuration files.
• Packages can be loaded in SQL Server and executed in SSMS by connecting to the SSIS service.
• SQL Server Agent provides the ability to schedule packages for execution.

Lesson 1: Managing the SSIS Service and Configuring Package Security

1 . Can you configure the security of a package so that you can store a password in clear text in the underlying .dtsx file in the file system?

no, sensitive information such as connection passwords cannot be stored as clear text in the package file in the file system. The only way to store the connection password in the file is to encrypt the sensitive information by setting the ProtectionLevel to EncryptSensitiveWithPassword or EncryptSensitiveWithuser- Key. A better choice is to set the ProtectionLevel to DontSaveSensitive and use an SSIS configuration to store the connection password.

2 . What information stored in the package definition would cause a security risk if it were found?

Although a package file does not contain data, it does contain the schema details about input sources and destinations. Even if these sources and destinations cannot be accessed, this information can be a security risk because it exposes table and column names.

3 . If your database user login is assigned to the db_ssisoperator role, which has only read access and does not have write access to a package stored in msdb, what are you able to do with the package?

Because you are assigned the db_ssisoperator role, you can execute the package inside msdb, but you cannot delete the package from the msdb store or import packages into the store.

Lesson 2: Executing and Scheduling Packages

1 . What are the benefits and drawbacks of storing packages in SQL Server?

When packages are stored or deployed to SQL Server, you can back them up by backing up the msdb system database. In addition, when packages reside in SQL Server, you can assign package roles to manage security. However, packages stored in the msdb database require more management than packages that are not stored there. for example, to modify packages stored in the database, you have to export them and then reimport them to SQL Server.

2 . Can you schedule packages to execute through SQL Server Agent if you have the EncryptSensitiveWithuserKey or EncryptAllWithuserKey value set for the ProtectionLevel property?

A package can be executed through SQL Server Agent with the user key encryption only if the package is executed on the server on which it was created and by the user who created it. If the ProtectionLevel is set to EncryptSensitiveWithuserKey and Windows Authentication is used for the connection, a package can be executed on a different server or by a different user, but a warning will be returned.

3 . If you have a package for which a variable must be updated at the start of execution, what methods are available to you?

variables can be updated at execution by using a configuration or by using the Set value command-line parameter, where the property path is typed as \package.variables[user:: strusername].value and the value is passed in.

Securing and Scheduling SSIS Packages

Case scenario

Your SSIS ETL packages have been deployed to SQL Server, and it is now your responsibility as the database administrator (DBA) to secure and schedule the packages for execution on your production server. One of the shared connections references a legacy database system that requires a user name and password. Therefore, a SQL Server configuration has been set up to manage the user name and password and to share them between packages. Your task is to secure the packages and schedule them for execution, taking into account the following requirements:

1. The packages contain schema information about a financial database, so you need to be sure that the packages are encrypted. You also need to ensure that the shared connection password in the SQL Server configuration table is secure.
2. The packages need to be scheduled to run at 8:00 every morning by using SQL ServerAgent.

Answers

1. To encrypt all the content of the packages, you need to use the DTUtil utility with the /ENCRYPT command-line parameter, running the utility from the folder of the packages. Each DTUtil statement will look like the following:

dtutil.exe /file MyPackage.dtsx /encrypt file;MyPackage.dtsx;3;EncPswd

In addition, because all the packages contain a shared connection user name and password, you can use SSMS to deny access to the configuration table from all accounts except the SQL Server Agent service account or the SQL Server Agent Proxy account that will be executing the package.