THIS TOPIC APPLIES TO:SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
Encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.
A passphrase from which to generate a symmetric key.
A variable of type nvarchar, char, varchar, binary, varbinary, or nchar containing a passphrase from which to generate a symmetric key.
The cleartext to be encrypted.
A variable of type nvarchar, char, varchar, binary, varbinary, or nchar containing the cleartext. Maximum size is 8,000 bytes.
Indicates whether an authenticator will be encrypted together with the cleartext. 1 if an authenticator will be added. int.
Indicates whether a hash will be encrypted together with the cleartext.
Data from which to derive an authenticator. sysname.
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