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.
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