Skip to content

verifysignedbyasymkey-transact-sql.md

Latest commit

 

History

History
100 lines (80 loc) · 3.78 KB

verifysignedbyasymkey-transact-sql.md

File metadata and controls

100 lines (80 loc) · 3.78 KB
title VERIFYSIGNEDBYASYMKEY (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/06/2017
ms.prod sql
ms.prod_service database-engine, sql-database
ms.reviewer
ms.suite sql
ms.technology t-sql
ms.tgt_pltfrm
ms.topic language-reference
f1_keywords
VERIFYSIGNEDBYASYMKEY_TSQL
VERIFYSIGNEDBYASYMKEY
dev_langs
TSQL
helpviewer_keywords
verifying digitally signed data for changes
VERIFYSIGNEDBYASYMKEY
testing digitally signed data for changes
checking digitally signed data for changes
signatures [SQL Server]
digital signatures [SQL Server]
ms.assetid 9f7c6e0b-5ba4-4dbb-994d-5bd59f4908de
caps.latest.revision 34
author MashaMSFT
ms.author mathoma
manager craigg

VERIFYSIGNEDBYASYMKEY (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]

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

Topic link icon Transact-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. signature is varbinary.

Return Types

int

Returns 1 when the signatures match; otherwise 0.

Remarks

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.

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 [AdventureWorks2012].[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 [AdventureWorks2012].[SignedData04]   
WHERE VerifySignedByAsymKey( AsymKey_Id( 'WillisKey74' ), Data,  
     DataSignature ) = 1  
AND Description = N'data encrypted by asymmetric key ''WillisKey74''';  
GO

See Also

ASYMKEY_ID (Transact-SQL)
SIGNBYASYMKEY (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)
Encryption Hierarchy