Skip to content

Latest commit

 

History

History
88 lines (70 loc) · 3.58 KB

decryptbycert-transact-sql.md

File metadata and controls

88 lines (70 loc) · 3.58 KB
title ms.custom ms.date ms.prod ms.prod_service ms.component ms.reviewer ms.suite ms.technology ms.tgt_pltfrm ms.topic f1_keywords dev_langs helpviewer_keywords ms.assetid caps.latest.revision author ms.author manager
DECRYPTBYCERT (Transact-SQL) | Microsoft Docs
03/06/2017
sql
database-engine, sql-database
t-sql|functions
sql
t-sql
language-reference
DecryptByCert_TSQL
DECRYPTBYCERT
TSQL
certificates [SQL Server], decryption
decryption [SQL Server], certificates
DECRYPTBYCERT function
4950d787-40fa-4e26-bce8-2cb2ceca12fb
38
edmacauley
edmaca
craigg

DECRYPTBYCERT (Transact-SQL)

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

Decrypts data with the private key of a certificate.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

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.

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

Permissions

Requires CONTROL permission on the certificate.

Examples

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.

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