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 Jul 24, 2006

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

By Marcin Policht

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 signatures).

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 valid).

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_MACHINE\SOFTWARE\Microsoft\MSDTS 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 signatures
  • 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_MACHINE\SOFTWARE\Microsoft\MSDTS 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

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