Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

EncryptByPassPhrase (Transact-SQL)

Encrypt data with a passphrase.

Topic link icon Transact-SQL Syntax Conventions


EncryptByPassPhrase ( { 'passphrase' | @passphrase } , 
                     { 'cleartext' | @cleartext }
                     [ , { add_authenticator | @add_authenticator }
                       , { authenticator | @authenticator } ]
                                        )

passphrase

A passphrase from which to generate a symmetric key.

@passphrase

A variable of type nvarchar, char, varchar, binary, varbinary, or nchar containing a passphrase from which to generate a symmetric key.

cleartext

The cleartext to be encrypted.

@cleartext

A variable of type nvarchar, char, varchar, binary, varbinary, or nchar containing the cleartext. Maximum size is 8,000 bytes.

add_authenticator

Indicates whether an authenticator will be encrypted together with the cleartext. 1 if an authenticator will be added. int.

@add_authenticator

Indicates whether a hash will be encrypted together with the cleartext.

authenticator

Data from which to derive an authenticator. sysname.

@authenticator

A variable containing data from which to derive an authenticator.

varbinary with maximum size of 8,000 bytes.

A passphrase is a password that includes spaces. The advantage of using a passphrase is that it is easier to remember a meaningful phrase or sentence than to remember a comparably long string of characters.

This function does not check password complexity.

The following example updates a record in the SalesCreditCard table and encrypts the value of the credit card number stored in column CardNumber_EncryptedbyPassphrase, using the primary key as an authenticator.

USE AdventureWorks;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard 
    ADD CardNumber_EncryptedbyPassphrase varbinary(256); 
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser 
    = 'A little learning is a dangerous thing!';

-- Update the record for the user's credit card.
-- In this case, the record is number 3681.
UPDATE Sales.CreditCard
SET CardNumber_EncryptedbyPassphrase = EncryptByPassPhrase(@PassphraseEnteredByUser
    , CardNumber, 1, CONVERT( varbinary, CreditCardID))
WHERE CreditCardID = '3681';
GO

Community Additions

ADD
Show:
© 2015 Microsoft