ENCRYPTBYPASSPHRASE (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
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 } ] )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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.
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
See Also
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho