Chapter 18. Securing Packages

In This Chapter

• Controlling Package Access

• Identifying Sensitive Data

• Protecting Packages and Other Files

• Integrating with SQL Server Agent

• Detecting Package Modification

• Security Scenario Settings

"EXCUSE ME. I'M IN CHARGE OF SECURITY HERE, MILADY."

ANAKIN SKYWALKER

With all the hackers out there, it's easy to narrowly think of Integration Services security as a set of features that protect against malicious attacks. Although that's certainly part of the security equation, protecting packages is a much broader and more complex problem. You need to prevent the wrong people from changing or executing the package while still allowing the right people that access. You need to provide for access combinations such as allowing some individuals to execute, but not change, a package. Whether by malicious intent or accidental corruption, you need to detect if packages have changed. But you might also need to keep people from viewing certain packages. You might need to protect certain parts of packages that might be more sensitive than others or you might want to send the package via unsecured channels to a partner and need to protect the entire package from being viewed by anyone but the individual to whom you sent it. Finally, you want these features to be easy to use and virtually transparent so that they don't get in the way of day-to-day operations. These are some of the scenarios the Integration Services security feature set supports.

Like other areas of the product, the Integration Services security features have been conceived and developed as a platform of separate but interoperable primitives that you can use exclusively or in combination with other security features to establish a custom security policy that matches the needs of your organization. The security features fall into six functional categories, as follows:

• Controlling package access Controlling who has access to packages

• Sensitive data marking and identification Identifying passwords and other sensitive data so that they are easier to protect

• Data protection Encrypting packages or parts of packages either in the package file or in transit

• Digital package signing Marking a package so it is possible to detect if it has changed

• Integration with SQL Server Agent Securely executing packages in a noninteractive environment

• Server security Securely accessing stored and executing packages enumerated via the Integration Services server

This chapter covers how the SSIS security features address each of these categories and provide some helpful tips on how to diagnose and resolve security-related problems that you might encounter. Finally, the chapter wraps up with some recommended security settings for certain sample environments.