Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 13, 2010

Protecting the Integrity of SSIS Packages Using Digital Signatures

By Marcin Policht

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 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 utility with /VerifyS[igned] switch (for example, by executing DTExec.exe /VerifySigned /F .Package.dtsx, where Package.dtsx is the name of the corresponding .dtsx file) or via its GUI counterpart DTExecUI.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).

Additional Resources

SQL Server 2008 R2 Index

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM