Code Validation with Digital Signatures

One of the advantages of digital signatures is to authenticate certain information. How this is normally accomplished is that a person would use their private key to encrypt a given document or the document's fingerprint. Initially a hash function would be applied to the document generating a message digest. The user would then encrypt the message digest using their private key. That way if the recipient of the document wishes to authenticate that it has not been tampered with they could use the public key of the sender to decrypt the message digest. Then using the same hash function on the message the two results could be compared to determine if they match. If so, then the recipient can be ensured that the document has not been altered.

Situations can occur where two different messages result in the same message digest. This is called a collision.

Adding a digital signature to code in SQL Server provides a way to ensure that the code has not been tampered with. To check if a stored procedure, trigger, or assembly is authentic one can query the sys.crypt_properties table. The thumbprint column will contain the value of the signing certificate's thumbprint for the object that has been signed.

For Example, I have a stored procedure getEmployees that has been signed by Certificate Cert_01.

The following query shows that getEmployees has been signed.

SELECT
OBJECT_NAME(crp.major_id) 'Procedure',
cer.name 'Certificate',
crp.thumbprint
FROM
sys.crypt_properties crp INNER JOIN sys.certificates cer
ON crp.thumbprint = cer.thumbprint

Results:

Procedure Certificate thumbprint
--------- ----------- -----------------------------
getEmployees Cert_01 0xE879E7ECF3702DE6D27A9A28E8B7052D77695E41

Now if I alter the stored procedure getEmployees no rows are returned.

Results:
Procedure Certificate thumbprint
--------- ----------- -----------------------------
(0 row(s) affected)


Given that the procedures was changed, the digital signature has been dropped by SQL Server.