General theory about Digital Signing & data security:
In SSIS we have some options to secure our package’s content by applying certain protection levels. These defined protection levels protects the package from being viewed by unwanted users. By using these options we can save sensitive information like username, password etc in our package without letting other users to see them. These all come under protection levels in SSIS like ‘EncryptSensitiveWithUserKey’, ‘EncryptSensitiveWithPassword’ etc. In this we can protect the whole package or some part of it by means of ‘User Key’ or some password or using both.
But none of these options are sufficient to provide protection against attacker who are educated enough or having rights to open up the package & do some modifications to that. But if we can come to know about the changes done to some package by some unauthorized user, we can restrict them to deploy those packages to be executed.
To achieve this we make use of a great concept called as ‘Cryptography’. This works with some keys to encrypt & decrypt the data. This can either be Symmetric or Asymmetric in terms of their usage. In symmetric encryption\decryption we use one unique key called as Public Key. This public key is used for both encryption as well as for decryption process. On the other hand, in case of asymmetric encryption\decryption we use a pair of keys, a public key for encryption and a private key for decryption of the data. Also in this case, public key is what anybody can see & use it to encrypt the data but only owner of the private key decrypt that data. So it provides the security for the data.
But to be sure about and to trust the provider of the data, we somehow have to validate the data provided by the source provider. Here we come up with Signatures, in fact Digital Signatures. Signature is the HASH data generated by applying the cryptographic function using the private key of the owner. This hash data (signature) is then attached to the actual data to be passed over or shared across. The public key holder can determine if any modification has been made to the actual data by generating the Hash code again by decrypting the data using its public key. Then this new hash code can be compared with the existing hash code (i.e. signature) which is attached along with the data, as this signature cannot be modified.
Digital Signing of a SSIS package:
Now the important idea is to recognize whether any change is made to a package or the data integrity has been violated or not. I mean we must have some mechanism to know about the authenticity of the owner from which we are getting the encrypted data. And here come Certificates into picture.
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 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. And this way user can check whether the user associated with the source data is genuine or not.
Code Signing – SSIS
SSIS packages can also be secured using the same kind of digital signatures i.e. digital certificates. These become very useful when we want not to allow any unwanted user to load or execute the package if package has been modified by an authorized user. And one cannot deploy that package which is not digitally signed.
This can be done by either:
- From Business Intelligence Development Studio, using Digital Signing option in the SSIS tab.
- OR using DTUtil command line utility combined with the /SIGN switch.
Also we need to set two addition configuration settings to control the behavior of digitally signed package. These are: 1. CheckSignatureOnLoad and 2. BlockedSignatureStates
1. CheckSignatureOnLoad indicates whether certificate is checked during the package load.
2. BlockedSignatureStates is the registry entry of type DWORD to be created in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS key. It can have 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 the packages with invalid or un-trusted signatures (issued by non-trusted CA).
- 3 – Prevents from loading and executing the packages that are unsigned or have invalid or un-trusted signatures.
Applying the certificate to SSIS package:
The certificate to added/used can be requested from a Certificate Authority (CA). This can be a third party issuing certificates or it can be a Windows Server that acts as a CA. Details of how to get the certificates using windows server can be seen from:
Now once we get the certificate in our personal folder, it can be added to the SSIS package as shown.
If some certs are available in the personal folder, those will be shown here to be added.
One more important setting which is to select the CheckSignatureOnLoad checkbox to enable the checking for the signature each time it is loaded.
Note: Before signing the package, create the BlockedSignatureStates entry in the respective key in the registry using REGEDIT.exe utility. Set the value of this entry as per the requirement.
The above mentioned functionality can be checked using a certificate for a SSIS package. Set the BlockedSignatureStates to 3 and check CheckSignatureOnLoad option. Now if the user who is the owner of the certificate & have rights on the personal folder of the certificate makes some changes then it would apply the same digital signature automatically to the package. But on the contrast, if somebody else makes the changes say by using some other means like .dtsx file (XML file) or so and saves it. Then digital signature would not be applied automatically to that and thus subsequent loads or execution of that package would fail saying that signature is not valid since content of the package has been modified.