DECRYPTBYCERT (Transact-SQL)

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

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

Transact-SQL syntax conventions

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

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.

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

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.

DECRYPTBYCERT requires CONTROL permission on the certificate.

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  

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