decryptbycert-transact-sql.md

title	DECRYPTBYCERT (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	DecryptByCert_TSQL DECRYPTBYCERT
dev_langs	TSQL
helpviewer_keywords	certificates [SQL Server], decryption decryption [SQL Server], certificates DECRYPTBYCERT function
ms.assetid	4950d787-40fa-4e26-bce8-2cb2ceca12fb
caps.latest.revision	38
author	MashaMSFT
ms.author	mathoma
manager	craigg

DECRYPTBYCERT (Transact-SQL)

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

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

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