VerifySignedByAsmKey (Transact-SQL)

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

Topic link iconTransact-SQL Syntax Conventions

Syntax

VerifySignedByAsymKey( Asym_Key_ID , clear_text , signature )

Arguments

  • Asym_Key_ID
    Is the ID of an asymmetric key certificate in the database.
  • clear_text
    Is clear text data that is being verified.
  • signature
    Is the signature that was attached to the signed data. varbinary.

Return Types

int

Returns 1 if the signatures match, otherwise 0.

Remarks

VerifySignedByAsmKey derives a signature from previously signed data, using an asymmetric key stored in the database. VerifySignedByAsmKey compares the newly derived signature to the signature that was associated with the signed data when it was received. 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.

Permissions

Requires VIEW DEFINITION permission on the asymmetric key.

Examples

A. Testing for data with a valid signature

The following example returns 1 if the selected data has not been changed since it was signed with asymmetric key WillisKey74. The example returns 0 if the data has been tampered with.

SELECT Data,
     VerifySignedByAsymKey( AsymKey_Id( 'WillisKey74' ), SignedData,
     DataSignature ) as IsSignatureValid
FROM [AdventureWorks].[SignedData04] 
WHERE Description = N'data encrypted by asymmetric key ''WillisKey74'''
GO
RETURN

B. Returning a result set that contains data with a valid signature

The following example returns rows in SignedData04 that contain data that has not been changed since it was signed with asymmetric key WillisKey74. The example calls the function AsymKey_ID to obtain the ID of the asymmetric key from the database.

SELECT Data 
FROM [AdventureWorks].[SignedData04] 
WHERE VerifySignedByAsymKey( AsymKey_Id( 'WillisKey74' ), Data,
     DataSignature ) = 1
AND Description = N'data encrypted by asymmetric key ''WillisKey74'''
GO

See Also

Reference

AsymKey_ID (Transact-SQL)
SignByAsymKey (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)

Other Resources

Encryption Hierarchy

Help and Information

Getting SQL Server 2005 Assistance