2 out of 6 rated this helpful - Rate this topic

DecryptByPassPhrase (Transact-SQL)

SQL Server 2005

Decrypts data that was encrypted with a passphrase.

Topic link icon Transact-SQL Syntax Conventions


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

Arguments

passphrase: Is the passphrase that will be used to generate the key for decryption.

@passphrase: Is a variable of type nvarchar, char, varchar, or nchar that contains the passphrase that will be used to generate the key for decryption.

' ciphertext ': Is the ciphertext to be decrypted.

@ciphertext: Is a variable of type varbinary that contains the ciphertext. The maximum size is 8,000 bytes.

add_authenticator: Indicates whether an authenticator was encrypted together with the plaintext. Is 1 if an authenticator was used. int.

@add_authenticator: Indicates whether an authenticator was encrypted together with the plaintext. Is 1 if an authenticator was used. int.

authenticator: Is the authenticator data. sysname.

@authenticator: Is a variable that contains data from which to derive an authenticator.

Return Types

varbinary with a maximum size of 8,000 bytes.

Remarks

No permissions are required for executing this function.

Returns NULL if the wrong passphrase or authenticator information is used.

The passphrase is used to generate a decryption key, which will not be persisted.

If an authenticator was included when the ciphertext was encrypted, the authenticator must be provided at decryption time. If the authenticator value provided at decryption time does not match the authenticator value encrypted with the data, the decryption will fail.

Examples

The following example decrypts the record updated in EncryptByPassPhrase.

USE AdventureWorks;
-- Get the pass phrase 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(nvarchar,
    DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber_EncryptedbyPassphrase, 1 
    , CONVERT(varbinary, CreditCardID)))
    AS 'Decrypted card number' FROM Sales.CreditCard 
    WHERE CreditCardID = '3681';
GO

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD

DecryptByPassPhrase (Transact-SQL)

Reference

Other Resources

Help and Information

Community Additions