Protecting the Integrity of SSIS Packages Using Digital Signatures
September 13, 2010
The confidentiality of SQL Server 2008-based Integration Services packages can be protected by encrypting their content or controlling access to them through restrictive permissions. Additionally, you have the option of detecting whether their code has been modified since its original release. This article discusses how this can be accomplished by leveraging digital signatures.
As we have recently demonstrated on this forum, you have the ability to
protect the confidentiality of SQL Server 2008-based
Encryption and signing constitute two most common practical applications of cryptography. Both of them leverage the concept of digital keys, which in essence are strings of characters generated by specially crafted algorithms. In general, keys can be divided into two categories, depending on whether they are intended to function independently or in related pairs, with one of them designated as public and the other as private. In the first case, a single key, known exclusively to its owner, operates in a symmetric manner, capable of handling both encryption and decryption. While this approach tends to be efficient from a performance standpoint, it introduces the challenge if encrypted data needs to be shared (since this requires an additional, secure method of transmitting the key). In the second case, one key (designated as private) remains in control of its owner, while the other (the public one) is readily available to anyone who requests it. Such mechanism is not only more versatile (due to its support for encryption as well as digital signatures), but also eliminates the challenge associated with its symmetric counterpart, since there is no need to transport the private key between the party encrypting data and its intended recipient. (Access to the private key is sufficient to decrypt any content protected with the corresponding public key). However, due to the inferior performance associated with this approach, it is very common to combine both methods, with data being encrypted using a symmetric key, which in turn is encrypted with a public key. On the receiving end, a holder of the private key applies it to retrieve the symmetric key, which subsequently is used to decrypt the original content.
Signing works in a somewhat opposite manner. Since its purpose is to ensure authenticity of data, its implementation involves calculating a unique hash based on its content, encrypting it with a private key, and including the result in the original document. Anyone with access to the public key can retrieve the hash and compare it to a result of its own calculations based on the same algorithm. Any discrepancies indicate that the original data has been modified.
It is important to note that in every scenario involving asymmetric keys, effectiveness of the protection they are supposed to provide depends on the trust in credibility of an issuer of public keys. More specifically, those utilizing public keys need to trust that the corresponding private keys are in the hands of their rightful owner. Obviously, this raises a question about the basis of such trust, which is where digital certificates come into play.
Certificates are digitally signed statements issued by entities known as Certificate
Authorities (or simply
SQL Server 2008 Integration Services provides the ability to sign
packages as well as to detect their presence and verify their validity. In
order to implement these provisions, you need to start by obtaining the appropriate
certificate along with the corresponding public key pair. In general, you can
request it from a commercial certification authority (if you have external
clients), an internal
At this point, you are ready to digitally sign your packages. The most
straightforward way to accomplish this is to open each of them in Business
Intelligence Development Studio and use the Digital Signing option of the SSIS
menu in the Designer interface. Once presented with the Digital Signing dialog
box (which should indicate that the package is currently not signed), click on
the Sign... command button. The resulting Select Certificate dialog box
automatically identifies the code signing certificates present in your personal
store. Simply pick the one you intend to use and click on the OK command button
to confirm your choice. Alternatively, you can use the
DTUtil.exe /File .Package.dtsx /Sign File;Package.dtsx;001122334455667788991011121314151617181920
According to the
Interestingly, the behavior of Business Intelligence Development Studio, in regard to signed packages, appears to be unaffected by either the registry values or General settings configurable via the Tools menu, with the exception of the Show warning if package is unsigned option, which actually does trigger an Unsigned Package notification. Instead, attempts to load a package with an untrusted or invalid signature consistently trigger notification referencing the corresponding error. The latter of these conditions is typically an indication that the target package has been modified either by a non-authorized user or in a non-standard manner (the signature of a package is automatically updated if its code signing certificate resides in the Personal certificate store of the user editing it using Business Intelligence Development Studio).