Export (0) Print
Expand All

ENCRYPTBYPASSPHRASE (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.

Applies to: SQL Server (SQL Server 2008 through current version), SQL Database V12 (Preview in some regions).

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 AdventureWorks2012;
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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft