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
Integration Services packages by
employing a variety of mechanisms, ranging from applying restrictive
through controlling access to SSIS-related tables in
msdb databases, to encrypting their
content. Choosing the most appropriate methodology is dependent to large extent
on your specific requirements, however, each of these options has its
drawbacks, affecting package portability or even resulting in a partial data
loss. More importantly, none of them allows you to detect whether original code
has been accidentally or intentionally modified since its release. Fortunately,
such functionality can be facilitated by leveraging digital signatures, which
will be the subject of this article.
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
following a request from a prospective owner of a private key.
CAs are publically known organizations, whose
reputability is contingent on their diligence in verifying the credentials of
their clients as well as evaluating authenticity and accuracy of their
requests. Once these tasks are successfully completed, a
CA generates a public key and packages
it in the form of a certificate, which provides information identifying both
the owner and issuer, its purpose (such as encryption, secure Web
communication, digital signing, smart card authentication, etc.), as well as
its expiration date. The content is digitally signed with a private key that
belongs to the
certificate becomes available to anyone who wants to participate in a secure
communication with its owner. This process works as intended providing that
certificate users trust (explicitly or implicitly) the issuing
CA (which is typically accomplished by
adding its certificate to the Trusted Root Certification Authorities store on
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
running, for example, Windows Server 2008 Active Directory Certificate Services
(when all prospective users of
packages are part of your organization), or use self-signed certificates
created with the
utility available as part of
Windows Software Development Kit. (Note
that this last approach should be limited to testing only). While that last
method automatically generates the appropriate certificate type, with the first
two you need to specify its purpose (Code Signing) explicitly when generating a
request. Once the certificate is issued, install it in the Personal store of
your user account on the computer hosting Business Intelligence Development
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 command line utility executed
followed by (semicolon-separated) package location and the thumbprint
identifying the certificate (to determine its value, examine certificate
properties in the Certificates Microsoft Management Console snap-in). For
example, assuming that you wanted to sign Package.dtsx residing in the current
folder with a certificate whose thumbprint has the value of 001122334455667788991011121314151617181920,
you would run:
DTUtil.exe /File .Package.dtsx /Sign File;Package.dtsx;001122334455667788991011121314151617181920
According to the
SQL Server Books Online, once all of
your packages are signed, you have the ability to enforce signature checking
(and specify an action to be taken depending on the outcome) when attempting
their edits via Business Intelligence Development Studio or launching their
execution. This can be accomplished in one of several ways:
- Enable the Check digital signature when loading a package checkbox on the General page of Integration Services Designers subsection of the Business Intelligence Designers section in the Options dialog box (accessible via Options... submenu of the Tools menu of Business Intelligence Development Studio). This is supposed to allow you to identify if a target package is signed as well as whether the corresponding certificate is valid and was issued by a trusted certificate authority. In addition, you have an option to Show warning if package is unsigned (via the same interface). Both of these settings affect all packages that are opened via Business Intelligence Development Studio.
- Set the BlockedSignatureStates registry entry of DWORD type located in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server100SSIS key to one of the following four values that determine the action carried out during package loading and execution:
0- ignores package signing status
1- prevents from loading and executing packages with signatures that are invalid (but allows unsigned packages). An invalid signature designates a mismatch between the hash decrypted via the corresponding public key and its value calculated during package load, which indicates that such package has been modified after it was signed (similarl to checks incorporated into Business Intelligence Development Studio, which are intended to provide equivalent functionality).
2- prevents from loading and executing packages with signatures that are either invalid or are based on certificates issued by a non-trusted CA (not present in the Trusted Root Certification Authority store of the user loading or executing the package) including self-signed ones (but allows unsigned packages)
3- prevents from loading and executing packages that are unsigned or have invalid, untrusted, or self-signed signatures.
- Launch a signed package using the DTExec.exe command line
/VerifyS[igned]switch (for example, by executing
DTExec.exe /VerifySigned /F .Package.dtsx, where Package.dtsx is the name of the corresponding
.dtsxfile) or via its GUI counterpart
DTExecUI.exewith the Execute only signed packages option (located in the Verification section of the Execute Package Utility dialog box) enabled.
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).