Skip to content

Latest commit

 

History

History
79 lines (62 loc) · 3.42 KB

decryptbycert-transact-sql.md

File metadata and controls

79 lines (62 loc) · 3.42 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
DECRYPTBYCERT (Transact-SQL)
DECRYPTBYCERT (Transact-SQL)
VanMSFT
vanto
03/06/2017
sql
t-sql
reference
DecryptByCert_TSQL
DECRYPTBYCERT
certificates [SQL Server], decryption
decryption [SQL Server], certificates
DECRYPTBYCERT function
TSQL

DECRYPTBYCERT (Transact-SQL)

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

This function uses the private key of a certificate to decrypt encrypted data.

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

Syntax

DecryptByCert ( certificate_ID , { 'ciphertext' | @ciphertext }   
    [ , { 'cert_password' | @cert_password } ] )

Arguments

certificate_ID
The ID of a certificate in the database. certificate_ID has an int data type.

ciphertext
The string of data encrypted with the public key of the certificate.

@ciphertext
A variable of type varbinary containing data encrypted with the certificate.

cert_password
The password used to encrypt the private key of the certificate. cert_password must have a Unicode data format.

@cert_password
A variable of type nchar or nvarchar containing the password used to encrypt the private key of the certificate. @cert_password must have a Unicode data format.

Return Types

varbinary, with a maximum size of 8,000 bytes.

Remarks

This function decrypts data with the private key of a certificate. Cryptographic transformations that use asymmetric keys consume significant resources. Therefore, we suggest that developers avoid use of ENCRYPTBYCERT and DECRYPTBYCERT for routine user data encryption / decryption.

Permissions

DECRYPTBYCERT requires CONTROL permission on the certificate.

Examples

This example selects rows from [AdventureWorks2022].[ProtectedData04] marked as data originally encrypted by certificate JanainaCert02. The example first decrypts the private key of certificate JanainaCert02 with the password of certificate pGFD4bb925DGvbd2439587y. Then, the example decrypts the ciphertext with this private key. The example converts the decrypted data from varbinary to nvarchar.

SELECT CONVERT(NVARCHAR(max), DecryptByCert(Cert_Id('JanainaCert02'),  
    ProtectedData, N'pGFD4bb925DGvbd2439587y'))  
FROM [AdventureWorks2022].[ProtectedData04]   
WHERE Description   
    = N'data encrypted by certificate '' JanainaCert02''';  
GO

See Also

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