VerifySignedByCert (Transact-SQL)

Tests whether digitally signed data has been changed since it was signed.

Topic link iconTransact-SQL Syntax Conventions

Syntax

VerifySignedByCert( Cert_ID , signed_data , signature )

Arguments

  • Cert_ID
    Is the ID of a certificate in the database. int
  • signed_data
    Is a variable of type nvarchar, char, varchar, or nchar that contains data that has been signed with a certificate.
  • signature
    Is the signature that was attached to the signed data. varbinary.

Return Types

int

Returns 1 if signed data is unchanged, otherwise 0.

Remarks

VerifySignedByCert derives a signature from previously signed data by using a certificate stored in the database. VerifySignedByCert compares the newly derived signature to the signature that was attached to the signed data when the data was retrieved. If the newly derived signature is the same as the signature that was attached to the signed data, this proves that the data was not changed after it was signed. The signature calculation is based on all characters in the module that is being verified, including leading spaces, trailing spaces, carriage returns, and line feeds.

Permissions

Requires VIEW DEFINITION permission on the certificate.

Examples

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 [AdventureWorks].[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 Shipping04.

SELECT Data FROM [AdventureWorks].[SignedData04] 
WHERE VerifySignedByCert( Cert_Id( 'Shipping04' ), Data, 
    DataSignature ) = 1 
AND Description = N'data signed by certificate ''Shipping04''';
GO

See Also

Reference

Cert_ID (Transact-SQL)
SignByCert (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)
ALTER CERTIFICATE (Transact-SQL)
DROP CERTIFICATE (Transact-SQL)
BACKUP CERTIFICATE (Transact-SQL)

Other Resources

Encryption Hierarchy

Help and Information

Getting SQL Server 2005 Assistance