DECRYPTBYPASSPHRASE (Transact-SQL)

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

This function decrypts data originally encrypted with a passphrase.

Transact-SQL syntax conventions

Syntax

DecryptByPassPhrase ( { 'passphrase' | @passphrase }   
    , { 'ciphertext' | @ciphertext }  
  [ , { add_authenticator | @add_authenticator }  
    , { authenticator | @authenticator } ] )  

Arguments

passphrase
The passphrase used to generate the decryption key.

@passphrase
A variable of type char, nchar, nvarchar, or varchar containing the passphrase used to generate the decryption key.

'ciphertext'
The string of data encrypted with the key. ciphertext has a varbinary data type.

@ciphertext
A variable of type varbinary containing data encrypted with the key. The @ciphertext variable has a maximum size of 8,000 bytes.

add_authenticator
Indicates whether the original encryption process included, and encrypted, an authenticator together with the plaintext. add_authenticator has a value of 1 if the encryption process used an authenticator. add_authenticator has an int data type.

@add_authenticator
A variable indicating whether the original encryption process included, and encrypted, an authenticator together with the plaintext. Is @add_authenticator has a value of 1 if the encryption process used an authenticator. @add_authenticator has an int data type.

authenticator
The data used as the basis for the generation of the authenticator. authenticator has a sysname data type.

@authenticator
A variable containing data used as the basis for the generation of the authenticators. @authenticator has a sysname data type.

Return Types

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

Remarks

DECRYPTBYPASSPHRASE requires no permissions for its execution. DECRYPTBYPASSPHRASE returns NULL if it receives the wrong passphrase or the wrong authenticator information.

DECRYPTBYPASSPHRASE uses the passphrase to generate a decryption key. This decryption key will not persist.

If an authenticator was included at the time of the ciphertext encryption, DECRYPTBYPASSPHRASE must receive that same authenticator for the decryption process. If the authenticator value provided for the decryption process does not match the authenticator value originally used to encrypted the data, the DECRYPTBYPASSPHRASE operation will fail.

Examples

This example decrypts the record updated in EncryptByPassPhrase.

USE AdventureWorks2022;  
-- Get the passphrase from the user.  
DECLARE @PassphraseEnteredByUser NVARCHAR(128);  
SET @PassphraseEnteredByUser   
= 'A little learning is a dangerous thing!';  
  
-- Decrypt the encrypted record.  
SELECT CardNumber, CardNumber_EncryptedbyPassphrase   
    AS 'Encrypted card number', CONVERT(varchar,  
    DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber_EncryptedbyPassphrase, 1   
    , CONVERT(varbinary, CreditCardID)))  
    AS 'Decrypted card number' FROM Sales.CreditCard   
    WHERE CreditCardID = '3681';  
GO  

See Also

Choose an Encryption Algorithm
ENCRYPTBYPASSPHRASE (Transact-SQL)