Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric
Encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.
Note
SQL Server 2017 and later versions encrypts data with a passphrase using an AES256 key.
Transact-SQL syntax conventions
Syntax
EncryptByPassPhrase ( { 'passphrase' | @passphrase }
, { 'cleartext' | @cleartext }
[ , { add_authenticator | @add_authenticator }
, { authenticator | @authenticator } ] )
Arguments
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.
Return Types
varbinary with maximum size of 8,000 bytes.
Remarks
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.
Examples
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.
Note
In Azure SQL Database and SQL database in Microsoft Fabric, the USE statement isn't available. Instead, connect directly to intended database and run the rest of the code sample.
USE AdventureWorks2022;
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
