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
permissions to .dtsx files,
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 CAs)
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 CA. 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
client computers).
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 CA,
running, for example, Windows Server 2008 Active Directory Certificate Services
(when all prospective users of SSIS
packages are part of your organization), or use self-signed certificates
created with the Makecert.exe
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
Studio.
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
with the /Si[gn] switch
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 status1–
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
utility with/VerifyS[igned]
switch (for example, by executingDTExec.exePackage.dtsx, where Package.dtsx
/VerifySigned /F .
is the name of the corresponding.dtsx
file) or via its GUI counterpartDTExecUI.exe
with 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).
