Skip to content

Latest commit

 

History

History
93 lines (73 loc) · 4.06 KB

verifysignedbycert-transact-sql.md

File metadata and controls

93 lines (73 loc) · 4.06 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
VERIFYSIGNEDBYCERT (Transact-SQL)
VERIFYSIGNEDBYCERT (Transact-SQL)
VanMSFT
vanto
03/06/2017
sql
t-sql
reference
VERIFYSIGNEDBYCERT
VERIFYSIGNEDBYCERT_TSQL
digitally signed data for changes [SQL Server]
verifying digitally signed data for changes
testing digitally signed data for changes
checking digitally signed data for changes
VERIFYSIGNEDBYCERT
signatures [SQL Server]
digital signatures [SQL Server]
TSQL

VERIFYSIGNEDBYCERT (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

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

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

VerifySignedByCert( Cert_ID , signed_data , signature )

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

Cert_ID
Is the ID of a certificate in the database. Cert_ID is 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. signature is varbinary.

Return Types

int

Returns 1 when signed data is unchanged; otherwise 0.

Remarks

VerifySignedBycert decrypts the signature of the data by using the public key of the specified certificate, 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 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 [AdventureWorks2022].[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 [AdventureWorks2022].[SignedData04]   
WHERE VerifySignedByCert( Cert_Id( 'Shipping04' ), Data,   
    DataSignature ) = 1   
AND Description = N'data signed by certificate ''Shipping04''';  
GO

See Also

CERT_ID (Transact-SQL)
SIGNBYCERT (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)
ALTER CERTIFICATE (Transact-SQL)
DROP CERTIFICATE (Transact-SQL)
BACKUP CERTIFICATE (Transact-SQL)
Encryption Hierarchy