VERIFYSIGNEDBYASYMKEY (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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 [AdventureWorks2022].[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 [AdventureWorks2022].[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