DECRYPTBYCERT (Transact-SQL)

SQL Server 2012

Decrypts data with the private key of a certificate.

Topic link icon Transact-SQL Syntax Conventions

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

certificate_ID

Is the ID of a certificate in the database. certificate_ID is int.

ciphertext

Is a string of data that has been encrypted with the public key of the certificate.

@ciphertext

Is a variable of type varbinary that contains data that has been encrypted with the certificate.

cert_password

Is the password that was used to encrypt the private key of the certificate. Must be Unicode.

@cert_password

Is a variable of type nchar or nvarchar that contains the password that was used to encrypt the private key of the certificate. Must be Unicode.

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, EncryptByCert and DecryptByCert are not suited for routine encryption of user data.

Requires CONTROL permission on the certificate.

The following example selects rows from [AdventureWorks2012].[ProtectedData04] that are marked as data encrypted by certificate JanainaCert02. The example decrypts the ciphertext with the private key of certificate JanainaCert02, which it first decrypts with the password of the certificate, pGFD4bb925DGvbd2439587y. The decrypted data is converted from varbinary to nvarchar.

Copy 
SELECT convert(nvarchar(max), DecryptByCert(Cert_Id('JanainaCert02'),
    ProtectedData, N'pGFD4bb925DGvbd2439587y'))
FROM [AdventureWorks2012].[ProtectedData04] 
WHERE Description 
    = N'data encrypted by certificate '' JanainaCert02''';
GO
Show: