SQL Server 2005 Integration Services – Digital Signing of Packages – Part 29

In our most recent article covering security-related features introduced in
the SQL Server 2005 Integration Services, we mentioned that, in addition to restricting
access when saving or running packages, it is also possible to detect
unauthorized changes to their content and prevent altered packages from being
launched. We will present here the mechanism that is used to provide this
functionality, explain the idea behind it and demonstrate its implementation.
Note that recognizing data integrity violations not only deals with security
issues differently than protecting data content (since it does not interfere
with rogue modifications, but instead takes an appropriate action once they
have already taken effect) but also leverages technologies other than access
controls described by us earlier (such as tightening database or file system
level permissions or using database roles). In particular, SSIS takes advantage
of cryptography (more specifically, public key certificates and digital

Cryptography is a discipline of science, which primary goal is protection of
confidentiality and integrity of information through its transformations.
Sensitivity can be shielded through the process called encryption, which
applies an algorithm and a key to the original clear text converting it into
obfuscated form – and which can not be easily reversed without knowledge of a decryption
algorithm and an equivalent key. Depending on whether both keys are identical,
encryption is called symmetrical or asymmetrical. While a symmetrical approach
is considerably more efficient, it is inherently less secure since decryption
requires possession of the original key (which might be difficult or impossible
to distribute in a safe manner if multiple parties are involved). For this
reason, asymmetrical methods (or hybrid solutions, with symmetric key being
encrypted with an asymmetric one) are more commonly used. The key used for
encryption is referred to as public (since it can be disclosed to any number of
people, without jeopardizing already encrypted content) with the other one
labeled as private (in exclusive possession of the authorized data addressee).
This allows an owner of the private key to accept data encrypted with the
corresponding public key from any number of sources knowing that it has not
been compromised in transit. The same asymmetrical characteristics can also be
used in order to ensure that specific data came from the original source using
the procedure called digital signing. The signature in this case is a numerical
hash, generated by applying a cryptographic function to data content. The hash
is then encrypted with a private key of the data owner and attached to the
data. The public key holder can determine whether any modifications took place
following the signing by calculating the hash again using the same
cryptographic function applied to the received data and comparing the result
against the one decrypted with the public key.

You have probably already have noticed a potential problem with both of
these scenarios. Its vulnerability results from the lack of a mechanism that
would verify the identity of the private key owner (who is trusted with
decryption and signing). How can you be sure that a public key you use to
encrypt your sensitive information (or to verify a digitally signed message)
has actually been published by a business or a person who claims to be its
owner? While this could be fairly easy to confirm when dealing with someone you
directly interact with, such a situation is rather unlikely in the era of
Internet transactions. This is the primary reason for use of certificates.

Certificates are digitally signed statements that contain key information
intended for secure data exchange. Certificates are issued following a request
from a client who wants to make such exchange possible, by an entity known as
Certificate Authority (or simply CA) – a reputable organization, whose responsibility
is to verify credentials of its applicants, evaluate legitimacy of their
request, as well as revoke certificates if, for some reason, such as a key
compromise, they are no longer considered to be valid prior to their expiration
date. CA creates a unique key pair, with a private one delivered in a safe
manner to the client and a public one packaged in the form of a public
certificate, which also contains the name and contact information of its owner
and issuer, its purpose (such as encryption, secure Web communication, digital
signing, smart card authentication, IPSec authentication, etc.), as well as
expiration date. The content is digitally signed with a private key that
belongs to the CA. The public certificate is then made available to anyone who
wants to participate in a secure communication with the certificate owner. How
does this resolve the identity dilemma? This resolution works based on the premise
that everyone who intends to use a certificate must trust the CA that issued
it. The trust can be implicit, based on your computer configuration (as is the
case with a number of trusted CAs pre-configured as part of the Windows
operating system installation or assigned by Active Directory administrators in
managed environments) or it might require your explicit acknowledgement
(assuming that you are confident that the certificate publisher credentials are

SQL Server 2005 Integration Services packages need to be signed with
certificates that have been issued for the purpose of Code Signing. This is
done either by applying the Digital Signing option of the SSIS menu in the SSIS
Designer interface of the Business Intelligence Development Studio or with the DTUtil
command line utility, combined with /SIGN switch followed by the package
location and hash identifier of the certificate (which can be retrieved by
examining certificate properties displayed in the Certificates Microsoft
Management Console snap-in). In order to take advantage of the signing process,
you need to also consider two additional configuration settings. The first one
is the Boolean value of the CheckSignatureOnLoad package property (assigned
True by default, and modifiable using programming methods), indicating whether
the certificate (stored in the CertificateObject package property) is checked
during package load. The second one, registry entry BlockedSignatureStates of
DWORD type located in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTS key
determines an action taken, depending on the signature presence or validity,
and has four possible values:

  • 0 – does not impact the loading process, regardless of the
    signature status

  • 1 – prevents from loading (and executing) packages with invalid

  • 2 – prevents from loading (and executing) packages with invalid
    or untrusted signatures (issued by non-trusted CA)

  • 3 – prevents from loading (and executing) packages that are
    unsigned or have invalid or untrusted signatures.

Let’s step through an example of setting up and verifying digital
signatures, including a case where package integrity has been violated. In our
scenario, the assumption is that the Windows system, running SQL Server 2005
Integration Services Service Pack 1, is a member of Active Directory domain
with Enterprise Certificate Authority server, accessible via a Web browser (for
the sake of simplicity). First, you need to make sure that Code Signing
certificates can be issued by your CA. To accomplish this, start the
Certification Authority Microsoft Management Console snap-in (part of the
Administrative Tools, which you can install by running the ADMINPAK.MSI file
located in the i386 folder on the Windows 2003 Server installation CD) and
point it to your CA Server. The server node in the left tree pane of the MMC
snap-in window consists of five subfolders named Revoked Certificates, Issued
Certificates, Pending Requests, Failed Requests, and Certificate Templates. If
the last one does not contain a Code Signing entry, right-click on it to
display its context sensitive menu and select the option labeled
New->Certificate Template to issue. From the Enable Certificate Template
dialog box, click on Code Signing and confirm with the OK button. This will
make the template available, so you can request certificates, which are based
on it (necessary for digital signatures). On the system hosting the SSIS
installation, launch Internet Explorer targeting the certsrv site on the CA
server (i.e. using http(s)://CAServerName/certsrv URL, where CAServerName
is the DNS name of your CA server). From the Welcome Web page, select the
"Request a certificate" link, followed by the "advanced
certificate request" option. Once you choose "Create and submit a request
to this CA", you will be prompted to provide details necessary to complete
the Advanced Certificate Request. Pick the Code Signing entry from the
Certificate Template list box, apply the remaining settings according to your
requirements (for the purpose of this example, leave the "Store
certificate in the local computer certificate store" blank), and click on
Submit. Depending on CA request handling policy, the certificate might be
available immediately or your request might need to be manually approved first (which
means you will need to revisit the http://CAServerName/certsrv site to
check the processing status). In either case, the issued certificate can be
installed via the "Install this certificate" link.

You can confirm the outcome of this installation by checking your personal
certificate store. This secure area contains such items as public key
certificates, certificate trust lists, and certificate revocation lists and is
created for every entity that might require its functionality, including
standard interactive users, services, or the computer account. Its content can
be viewed from the Certificates Microsoft Management Console snap-in, with
focus on the relevant account. In your case, simply type CERTMGR.MSC in the
Start->Run text box to display the relevant information for your user
account. Check the listing of the certificates in the Personal folder and
verify that the one you just installed appears there with Code Signing label in
the Intended Purpose column.

Before you sign the package, launch REGEDIT.EXE and create the BlockedSignatureStates
entry of DWORD type in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTS key. To
test its functionality, assign it a value of 3 (which should prevent any
unsigned package from loading). Next, launch the Business Intelligence
Development Studio and open a project whose integrity you want to protect. If
you try to view the associated .dtsx file in the SSIS Designer window, Visual
Studio should generate the following error: The package cannot be loaded
because the state of the digital signature violates signature policy. Error
0xC0014064 "The package is not signed."
You would experience the
same outcome if you attempted to execute the package via SQL Server Management Studio
or directly with the DTSRun utility.

In order to proceed with our example, change the value of the BlockedSignatureStates
to 0, which should allow you to load the package in the SSIS Designer
interface. Select Digital Signing from the SSIS menu. In the resulting dialog
box, note the message stating that the package is not signed. Click on Sign…
to bring up the Select Certificate window. You should be able to see the newly
installed certificate appearing in the list. From there, you can review its content
using the View Certificate button and install it by simply selecting the entry
and clicking on OK. At this point, switching the BlockedSignatureStates
registry entry back to the value of 3 should have no impact on execution or
loading of this package. Note that any future modifications to a digitally
signed package within the Business Intelligence Development Studio interface,
performed using the same user account (which Personal store contains the
relevant certificate), will automatically (and transparently) reapply the
signature. However, if you attempt to alter the package via an alternate method
(such as direct edit of the XML-formatted .dtsx file) with BlockedSignatureStates
set to a non-zero value, the subsequent loads or execution will fail (with
error indicating that the digital signature is not valid since the content of
the package has been modified).


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles