Microsoft SQL Server 2005 Integration Services

Part of securing a package is recognizing if it has changed. Digital signing is a feature in SSIS that lets you sign a package with a code-signing certificate. With this certificate, you can detect whether a package has been changed in some way. Whether the change was malicious or inadvertent, digital signing lets you ensure that the package you run today is the same package you deployed yesterday. Package signing is not related to package encryption. When a package author signs a package, he is simply making it possible for those who open the package to verify that the package came from a trusted source. Anyone, trusted or not, can still open the package.

Tip

As mentioned in Chapter 14, "Configuring and Deploying Solutions," if you configure all transitory settings, such as server names, filenames, and other such values, you'll never need to modify the package, meaning you'll never break the package signing.

Signing Requirements

To sign your package, your certificate must meet the following requirements:

• The root authority must be a trusted publisher.

• The certificate must be in the Current User Personal store.

• The certificate purpose must include "Code Signing."

Signing the Package

Complete the following to sign the package:

1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want to work with.

2. In the Solution Explorer, double-click the package to open it.

3. In SSIS designer, on the SSIS menu, click Digital Signing.

4. In the Digital Signing dialog box, click Sign.

5. In the Select Certificate dialog box, select a certificate.

6. Click the OK button twice.

7. Save the package.

You can also use DTUtil.exe, the command-line package management utility, to sign packages.

CheckSignatureOnLoad

The CheckSignatureOnLoad package property is a Boolean that indicates if the package should check the digital signature when it loads. If CheckSignatureOnLoad is enabled, all packages are examined for a digital signature when they are loaded. The SSIS runtime validates the signature and if the certificate is valid, loads the package with no messages to the user. If the certificate is not trusted, the SSIS runtime emits a warning and asks you if you want to trust the contents.

To configure the package so that it warns if the signature is trusted, open the Options dialog box by selecting the Tools menu and then selecting Options. In the Business Intelligence Designers node, select Integration Services Designers, General. There are two options:

• Check Digital Signature when Loading a Package Turn this on to check the signature and warn if not trusted.

• Show Warning if a Package Is Unsigned Turn this on to show an error message box giving you the option to cancel opening the package if the signature is invalid.