DECRYPTBYPASSPHRASE (Transact-SQL)DECRYPTBYPASSPHRASE (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

此函数可对最初使用密码加密的数据进行解密。This function decrypts data originally encrypted with a passphrase.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
DecryptByPassPhrase ( { 'passphrase' | @passphrase }   
    , { 'ciphertext' | @ciphertext }  
  [ , { add_authenticator | @add_authenticator }  
    , { authenticator | @authenticator } ] )  

参数Arguments

passphrase passphrase
用于生成解密密钥的密码。The passphrase used to generate the decryption key.

@passphrase
类型变量A variable of type

  • charchar
  • ncharnchar
  • nvarcharnvarchar

或多个or

  • varcharvarchar

包含用于生成解密密钥的密码。containing the passphrase used to generate the decryption key.

'ciphertext' 'ciphertext'
使用密钥加密的数据字符串。The string of data encrypted with the key. ciphertext 具有 varbinary 数据类型 。ciphertext has a varbinary data type.

@ciphertext
varbinary 类型的变量,包含使用密钥加密的数据 。A variable of type varbinary containing data encrypted with the key. @ciphertext 变量的最大大小为 8,000 字节。The @ciphertext variable has a maximum size of 8,000 bytes.

add_authenticator add_authenticator
指示原始加密过程是否包含验证器和纯文本以及是否对其进行加密。Indicates whether the original encryption process included, and encrypted, an authenticator together with the plaintext. 如果加密过程使用验证器,则 add_authenticator 具有 1 值 。add_authenticator has a value of 1 if the encryption process used an authenticator. add_authenticator 具有 int 数据类型 。add_authenticator has an int data type.

@add_authenticator
变量,指示原始加密过程是否包含验证器和纯文本以及是否对其进行加密。A variable indicating whether the original encryption process included, and encrypted, an authenticator together with the plaintext. 如果加密过程使用验证器,则 @add_authenticator 具有值 1。Is @add_authenticator has a value of 1 if the encryption process used an authenticator. @add_authenticator 具有 int 数据类型。@add_authenticator has an int data type.

authenticator authenticator
用作验证器生成基础的数据。The data used as the basis for the generation of the authenticator. authenticator 具有 sysname 数据类型 。authenticator has a sysname data type.

@authenticator
包含用作验证器生成基础的数据的变量。A variable containing data used as the basis for the generation of the authenticators. @authenticator 具有 sysname 数据类型。@authenticator has a sysname data type.

返回类型Return Types

varbinary(最大大小为 8,000 个字节) 。varbinary, with a maximum size of 8,000 bytes.

RemarksRemarks

DECRYPTBYPASSPHRASE 不需要执行权限。DECRYPTBYPASSPHRASE requires no permissions for its execution. 如果 DECRYPTBYPASSPHRASE 收到错误的密码或错误的验证器信息,则返回 NULL。DECRYPTBYPASSPHRASE returns NULL if it receives the wrong passphrase or the wrong authenticator information.

DECRYPTBYPASSPHRASE 使用密码生成解密密钥。DECRYPTBYPASSPHRASE uses the passphrase to generate a decryption key. 此解密密钥不会保留。This decryption key will not persist.

如果在 ciphertext 加密时包含验证器,DECRYPTBYPASSPHRASE 必须接收该解密过程的同一验证器。If an authenticator was included at the time of the ciphertext encryption, DECRYPTBYPASSPHRASE must receive that same authenticator for the decryption process. 如果解密过程中提供的验证器值与最初用于加密数据的验证器值不匹配,则 DECRYPTBYPASSPHRASE 操作将失败。If the authenticator value provided for the decryption process does not match the authenticator value originally used to encrypted the data, the DECRYPTBYPASSPHRASE operation will fail.

示例Examples

此示例解密 EncryptByPassPhrase 中更新的记录。This example decrypts the record updated in EncryptByPassPhrase.

USE AdventureWorks2012;  
-- Get the pass phrase from the user.  
DECLARE @PassphraseEnteredByUser nvarchar(128);  
SET @PassphraseEnteredByUser   
= 'A little learning is a dangerous thing!';  
  
-- Decrypt the encrypted record.  
SELECT CardNumber, CardNumber_EncryptedbyPassphrase   
    AS 'Encrypted card number', CONVERT(nvarchar,  
    DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber_EncryptedbyPassphrase, 1   
    , CONVERT(varbinary, CreditCardID)))  
    AS 'Decrypted card number' FROM Sales.CreditCard   
    WHERE CreditCardID = '3681';  
GO  

另请参阅See Also

选择加密算法 Choose an Encryption Algorithm
ENCRYPTBYPASSPHRASE (Transact-SQL)ENCRYPTBYPASSPHRASE (Transact-SQL)