Updated: March 16, 2015
Decrypts data that was encrypted with a passphrase.
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.
The following example decrypts the record updated in EncryptByPassPhrase.
USE AdventureWorks2012; -- 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