Tests whether digitally signed data has been changed since it was signed.
VerifySignedByCert( Cert_ID , signed_data , signature )
Is the ID of a certificate in the database. Cert_ID is int.
Is a variable of type nvarchar, char, varchar, or nchar that contains data that has been signed with a certificate.
Is the signature that was attached to the signed data. signature is varbinary.
Returns 1 when signed data is unchanged; otherwise 0.
VerifySignedBycert decrypts the signature of the data by using the public key of the specified certificate, and compares the decrypted value to a newly computed MD5 hash of the data. If the values match, the signature is confirmed to be valid.
Requires VIEW DEFINITION permission on the certificate.
A. Verifying that signed data has not been tampered with
The following example tests whether the information in
Signed_Data has been changed since it was signed with the certificate called
Shipping04. The signature is stored in
DataSignature. The certificate,
Shipping04, is passed to
Cert_ID, which returns the ID of the certificate in the database. If
VerifySignedByCert returns 1, the signature is correct. If
VerifySignedByCert returns 0, the data in
Signed_Data is not the data that was used to generate
DataSignature. In this case, either
Signed_Data has been changed since it was signed or
Signed_Data was signed with a different certificate.
SELECT Data, VerifySignedByCert( Cert_Id( 'Shipping04' ), Signed_Data, DataSignature ) AS IsSignatureValid FROM [AdventureWorks2012].[SignedData04] WHERE Description = N'data signed by certificate ''Shipping04'''; GO
B. Returning only records that have a valid signature
This query returns only records that have not been changed since they were signed using certificate
SELECT Data FROM [AdventureWorks2012].[SignedData04] WHERE VerifySignedByCert( Cert_Id( 'Shipping04' ), Data, DataSignature ) = 1 AND Description = N'data signed by certificate ''Shipping04'''; GO
CREATE CERTIFICATE (Transact-SQL)
ALTER CERTIFICATE (Transact-SQL)
DROP CERTIFICATE (Transact-SQL)
BACKUP CERTIFICATE (Transact-SQL)