Home Library Learn Downloads Support Community
Sign in | United States - English | |
0 out of 2 rated this helpful - Rate this topic

ENCRYPTBYPASSPHRASE (Transact-SQL)

SQL Server 2008

Encrypt data with a passphrase.

Topic link iconTransact-SQL Syntax Conventions

Copy 

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.

Copy 
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
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Maximum Size of Cleartext

Although the maximum size of cleartext (or @cleartext as the case may be) is 8,000 bytes, the the true maximum size is slightly less. The true maximum size is lowered by the header information prepended to the encrypted to the encrypted ciphertext by SQL Server, as well as by the padding added by the encryption algorithm. The true maximum size of the cleartext is 7,975 bytes if no authenticator is used, or 7,955 bytes if an authenticator is used. If your cleartext exceeds this maximum size this function will return NULL.

The return value indicates a maximum return value size of 8,000 bytes, but it cannot exceed 7,996 bytes in practice.

History