Tests whether digitally signed data has been changed since it was signed.
Transact-SQL Syntax Conventions
VerifySignedByAsymKey( Asym_Key_ID , clear_text , signature )
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. signature is varbinary.
int
Returns 1 when the signatures match; otherwise 0.
VerifySignedByAsymKey decrypts the signature of the data by using the public key of the specified asymmetric key, 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 asymmetric key.
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 [AdventureWorks2012].[SignedData04]
WHERE Description = N'data encrypted by asymmetric key ''WillisKey74''';
GO
RETURN;
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 [AdventureWorks2012].[SignedData04]
WHERE VerifySignedByAsymKey( AsymKey_Id( 'WillisKey74' ), Data,
DataSignature ) = 1
AND Description = N'data encrypted by asymmetric key ''WillisKey74''';
GO
ASYMKEY_ID (Transact-SQL)
SIGNBYASYMKEY (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)
Encryption Hierarchy