Digitally signing an SQL Server database record or an insert/update operation

Question

Is there a way to somehow digitally sign the current state of a database record or a particular field with a particular user's certificate or AD credential, etc? I need to implement some kind of record level approval where the correctness and validity of a particular field or fields or a row is signed by a username/password combination or with some certificate. Data should be always visible in the record, all I need is to be able to tell: who and when approved the data. I do not need to prevent a change in state (lock down), only to verify that the current state was signed.

Answer 1

You can use the SIGNBYASYMKEY-function to create a signature using an asymmetric key:

DECLARE @id = 2; -- The primary key of the row we need to sign
DECLARE @keyid = AsymKey_Id( 'KeyID_for_the_User' );
DECLARE @keypw = N'pGFD4bb925DGvbd2439587y'; 
                 -- Password the user entered to sign the values
DECLARE @clear_text_data nvarchar(max);
set @clear_text_data = function_that_returns_a_concatenation_of_the_columns(@id)

UPDATE table
SET signature = SignByAsymKey( @keyid, @clear_text_data, @keypw )   
WHERE id = @id;

Answer 2

[Disclosure: I work for CoSign]

How standard a digital signature do you want? Do you want an X.509 signature, the "real thing?"

If so:

1. serialize (flatten) the data fields in a specific, documented and repeatable way into a buffer.
2. use a signing library to sign the buffer.
3. The resulting digital signature should be a "detached digital signature." The recommended standard for detached signatures is the .p7b file type–-a PKCS#7 digital signature structure without the data.
4. Store the detached digital signature as an additional field in the dbms record.

You will also need to provide a signature verification service to assure the person (or machine) that the data was not changed since it was signed, and that the signer's identity is trusted:

1. Using the same algorithm as above, serialize the data into a buffer
2. Call the signing library's verification method
3. Present the results to the user.

If you want to improve the audit capabilities of your system, then you should enable the serialized data and the external signature to be downloaded. This will enable an audit check: using third party software to duplicate the effect of your verification software.

Ideally, the serialized version of the data should be easily checked that it matches the plaintext data. Eg serialize into a json, csv, or xml utf-8 format rather than a binary format. Any bundle of bits can be signed, but a plain text format will be much easier to audit.

If you also want the signing library to handle all of the security issues of properly protecting signer's private keys, check out the CoSign SAPI APIs.

Remember that storing signers' private keys in a file on the filesystem means that the overall application is not providing proof of signature. The only way to provide real proof of signature is to use a Secure Signature Creation Device (SSCD). An easy way to do that is to use a CoSign Central signing appliance or similar. The main alternative is smart cards which can be quite difficult to implement in a client/server/web browser environment.