As we have demonstrated in one of our recent articles dedicated to SQL Server 2012 Integration Services published on this forum, there are several different ways of restricting access to SSIS packages and confidential informationthey contain. However, preventing unauthorized users from running or viewing the content of your code is not the only security measure that you should take into consideration. With proliferation of Internet-based technologies and globalization of the software marketplace, it is also important to provide assurances to those who are intended customers that packages they obtained come from a legitimate source and have not been altered without consent and oversight of their original designers. This objective can be accomplished by applying concepts from the field of cryptography to procedures dealing with SSIS package creation and maintenance. In our presentation we will briefly explain these concepts and describe how they can be implemented in SQL Server 2012 Integration Services.
In the most basic terms, the solution that provides sufficient assurances in such scenarios relies on digital signatures. Just like their traditional, hand-written counterparts, their primary purpose is to uniquely identify one entity (such as a company developing SSIS packages) to another (such as a customer who decided to acquire these packages to automate its internal business processes). Their underlying technology is digital cryptography, which, in this particular case involves the use of a pair of asymmetric keys. One key (designated as private) remains in the exclusive possession of its owner (the software company in our example), while the other (referred to as public) is readily available to anyone who requests it (such as a prospective customer).
Signing involves calculating a unique hash (using a specially crafted algorithm) based on some content that will be available to both parties (private and public), encrypting it with a private key, and attaching the result to the original content. 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. In addition, since private and public keys are inherently linked, the authenticity of the content creator (and signer) can be also easily confirmed.
Effectiveness of the protection provided by asymmetric keys depends on the credibility of their issuer. More specifically, those utilizing public keys need to trust that the corresponding private keys are in the possession 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 signer (and a private key owner). Commercial CAs are well-known and established companies, 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 actions are successfully completed, a CA packages a public key in the form of a certificate, which provides information identifying both the owner (i.e. the original requestor) and the issuer (i.e. the CA), its purpose (such as encryption, secure Web communication, digital signing, smart card authentication, etc.), as well as its expiration date. The certificate content is digitally signed with a private key that belongs to the CA. At this point, the certificate can be made available to anyone who wants to be able to verify the identity of the private key owner (and, in the case of code signing, also the authenticity of the digitally signed content). This process works as intended providing that those relying on the certificate trust the issuing CA (which is typically accomplished by adding its certificate in the Trusted Root Certification Authorities store on their computers).
In order to obtain a certificate necessary to digitally sign SSIS packages, you can take one of the following approaches:
- request it from a third-party, commercial Certificate Authority (preferably one included in the Root Certificate Program Member List, as described in the Knowledge Base article 931125). This approach is primarily intended for scenarios involving external customers or distributed corporate environments where using an internal Certificate Authority is not an option.
- make it available by leveraging an internal Certificate Authority (which could be implemented using the Windows Server 2012 built-in role). This solution is well-suited for any implementation where the certificate issuer, private key owners, and customers are managed by the same technology organization (which can ensure that certificates issued by an internal CA will be trusted by all end users).
- create it by using makecert.exe utility available from the Windows Software Deployment Driver Kit and automatically installed with Visual Studio (you can also obtain it as a separate download from Technet Gallery). This should be limited to testing only.
Let’s step through an example illustrating the last of these methods. In this case, you need to execute makecert.exe twice. The first run creates and configures a root certificate (emulating the process of adding a certificate of an issuing CA to the local machine’s Trusted Root Certification Authorities store on a customer’s computer). To accomplish this, you would use the following syntax:
makecert -r -n "CN=SSIS Root Certificate" -a sha1 -eku 1.3.6.1.5.5.7.3.3 -sv root.pvk root.cer -len 1024 -ss Root -sr localMachine
where -r designates the resulting certificate as self-signed, -n specifies a certificate name (its format has to follow X.500 specifications), -a dictates the signing algorithm (which you could omit here since sha1 is the default), -eku assigns the enhanced key usage OID (an identifier, which in this case, indicates that certificates to be issued will be used for code signing only), -ss and -sr determine the store where the certificate will be placed (which points here to the Trusted Root Certificate Authority of the local computer), -len sets the key length (in bits), and -sv specifies the name of a file where the autogenerated private key will be saved (root.pvk). The command also includes the name of a file that will contain the root certificate (root.cer). Once you launch makecert.exe, you will be prompted for a password to secure the private key. The execution will result in the creation of both root.pvk and root.cer files in the current directory, as well as the addition of SSIS Root Certificate in the Trusted Root Certification Authorities store of the local computer (which you can easily verify by viewing the content of Certificates Microsoft Management Console snap-in).
At this point, you are ready for the second step, which generates a code signing certificate that you can subsequently use to facilitate and validate authenticity of SSIS packages. This time, the command line takes the following format:
makecert.exe -pe -n "CN=SSIS Signing Certificate" -a sha1 -eku 1.3.6.1.5.5.7.3.3 -iv root.pvk -ic root.cer -ss MY
where -pe ensures that you will be able to export associated private key (in case you need to sign packages from another system and you are not using roaming profiles or credential roaming, since, according to the -ss MY switch, the certificate with the associated key pair will be placed in the current users’s Personal store), and -iv and -ic point to the existing issuer’s private key and certificate files (which you created in the previous step). You will be prompted for the password you set earlier, since issuing a certificate requires access to the private key of the SSIS Root Certificate.
If you want to make an equivalent certificate available from an internal Enterprise Certificate Authority (which is the second method we listed above) in an Active Directory environment, you first need to issue the corresponding template. To accomplish this, launch the Certificate Templates Console (this can be done by choosing the Manage item from the context-sensitive menu of the Certificate Templates node in the Certificate Authority console) and locate Code Signing template. If your intention is to modify its non-security specific properies (such as, for example, validity period), select Duplicate Template from its context-sensitive menu and modify its copy as desired. Next, on the Security tab of the template’s Properties dialog box, modify the default permissions to ensure that users who will be signing SSIS packages have both Read and Enroll permissions. After you have made all planned changes, return to the Certificate Authority console, right-click on the Certificate Templates node, and use New->Certificate Template to Issue the menu item to make the newly created template available for enrollment.
In order to obtain a certificate based on the Code Signing template, from the computer on which you will be signing SSIS packages, launch Microsoft Management Console and add to it the Certificates snap-in with My user account as its focus (ensure that your account has Read and Enroll permissions to the template). Navigate to the Certificates subfolder of the Personal folder and select All Tasks->Request New Certificate… from its context-sensitive menu. In the resulting Certificate Enrollment wizard, choose Active Directory Enrollment Policy, locate the Code Signing entry (representing the newly issued template) on the Request Certificates page, select it and click on the Enroll command button.
Regardless of the method you choose, once a Code Signing certificate resides in your Personal store, you are ready to apply it to your SSIS packages. To do so, there are two basic methods that you can use (the latter is by far better suited for signing a larger number of packages):
- via the graphical interface provided by SQL Server Data Tools – from the SSIS menu, select Digital Signing and click on the Sign command button. The resulting dialog box should automatically display the appropriate certificate (assuming that there is one with the Code Signing enhanced key usage in your Personal store). The package needs to be saved afterwards.
- via the command-line interface faciliated by the /Sign switch of DTUtil.exe utility. In this case, you need to provide a thumbprint of the certificate (which is a hash uniquely identifying it), which you can extract by viewing the Details tab of its Properties dialog box, accessible from the Certificates snap-in. Alternatively, you can also get there it by running certutil -user My “SSIS Signing Certificate” (assuming that you were following our naming convention). In either case, make sure to remove extra spaces. For example, with a package in its default file format (Package.dtsx) and the thumbprint value of 1234567891011121314151617181920212223, you could create its signed copy by running:
DTUtil.exe /FILE Package.dtsx /SIGN FILE;SignedPackage.dtsx;1234567891011121314151617181920212223
Once you have signed the SSIS packages in your environment, you can perform signature verification, which allows you to identify those which are unsigned, as well as those whose signature is invalid (indicating unauthorized modifications to its content), expired, revoked, or untrusted (signed with a certificate issued by a Certificate Authority that is not considered trusted). The verification can be implemented in two distinct scenarios:
- At the design time – in the SQL Server Data Tools interface, select Options… from the Tools top-level menu, and navigate to the General page of Integration Services Designers subsection within the Business Intelligence Designers section. In the top area of the Options dialog box, you will find two settings (in the form of checkboxes) that control this behavior during package loading, labeled Check digital signature when loading a package and Show warning if a package is unsigned. Since only the first of them is selected by default, you should expect to be notified if a package signature is not valid (indicating that a package has been modified – either accidentally or maliciously – without access to the private key) or the underlying certificate is expired, revoked, or has not been issued by a trusted Certificate Authority. At the same rate, any prompts during loading of unsigned packages are automatically supressed (if you want to change this, enable the second checkbox).
- At the execution time – when launching a package using DTExec.exe command-line utility, you can add the /VerifySigned (or simply /VerifyS) switch, which will perform signature check at that point (the execution will fail if the signature is invalid, based on a certificate from an untrusted Certificate Authority, or not present).
According to SQL Server 2012 Books Online, you also should be able to control signature validation behavior by manipulating the BlockedSignatureStates entry of DWORD type residing in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server100SSIS registry key (if you combine it with either of the two methods described above, the more restrictive one takes precedence), which can take one of the following four values:
- 0 – ignores package signing status.
- 1 – prevents from loading and executing packages with signatures that are invalid, but allows unsigned packages.
- 2 – prevents from loading and executing packages with signatures that are either invalid or are based on certificates issued by a non-trusted Certificate Authority including self-signed ones. This setting, however, does allow unsigned packages.
- 3 – prevents from loading and executing packages that are unsigned or have invalid, untrusted, or self-signed signatures.
This concludes our overview of SSIS package signing in SQL Server 2012 Integration Services.
