DECRYPTBYKEYAUTOASYMKEY (Transact-SQL)

Artikel
12/29/2022

Applies to: SQL Server Azure SQL Managed Instance

This function decrypts encrypted data. To do this, it first decrypts a symmetric key with a separate asymmetric key, and then decrypts the encrypted data with the symmetric key extracted in the first "step".

Transact-SQL syntax conventions

Syntax

DecryptByKeyAutoAsymKey ( akey_ID , akey_password   
    , { 'ciphertext' | @ciphertext }  
  [ , { add_authenticator | @add_authenticator }   
  [ , { authenticator | @authenticator } ] ] )

Arguments

akey_ID
The ID of the asymmetric key used to encrypt the symmetric key. akey_ID has an int data type.

akey_password
The password protecting the asymmetric key. akey_password can have a NULL value if the database master key protects the asymmetric private key. akey_password has an nvarchar data type.

ciphertext The data encrypted with the key. ciphertext has a varbinary data type.

@ciphertext
A variable of type varbinary containing data encrypted with the symmetric key.

add_authenticator
Indicates whether the original encryption process included, and encrypted, an authenticator together with the plaintext. Must match the value passed to ENCRYPTBYKEY (Transact-SQL) during the data encryption process. add_authenticator has a value of 1 if the encryption process used an authenticator. 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. Must match the value passed to ENCRYPTBYKEY (Transact-SQL) during the data encryption process. @add_authenticator has an int data type.

authenticator
The data used as the basis for the generation of the authenticator. Must match the value supplied to ENCRYPTBYKEY (Transact-SQL). authenticator has a sysname data type.

@authenticator
A variable containing data from which an authenticator generates. Must match the value supplied to ENCRYPTBYKEY (Transact-SQL). @authenticator has a sysname data type.

authenticator
The data used as the basis for the generation of the authenticator. Must match the value supplied to ENCRYPTBYKEY (Transact-SQL). authenticator has a sysname data type.

@authenticator
A variable containing data from which an authenticator generates. Must match the value supplied to ENCRYPTBYKEY (Transact-SQL). @authenticator has a sysname data type.

Return Types

varbinary, with a maximum size of 8,000 bytes.

Remarks

DECRYPTBYKEYAUTOASYMKEY combines the functionality of both OPEN SYMMETRIC KEY and DECRYPTBYKEY. In a single operation, it first decrypts a symmetric key, and then decrypts encrypted ciphertext with that key.

Permissions

Requires VIEW DEFINITION permission on the symmetric key, and CONTROL permission on the asymmetric key.

Examples

This example shows how DECRYPTBYKEYAUTOASYMKEY can simplify decryption code. This code should run on an AdventureWorks2022 database that does not already have a database master key.

--Create the keys and certificate.  
USE AdventureWorks2022;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';  
CREATE ASYMMETRIC KEY SSN_AKey   
    WITH ALGORITHM = RSA_2048 ;   
GO  
CREATE SYMMETRIC KEY SSN_Key_02 WITH ALGORITHM = DES  
    ENCRYPTION BY ASYMMETRIC KEY SSN_AKey;  
GO  
--  
--Add a column of encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber2 varbinary(128);   
OPEN SYMMETRIC KEY SSN_Key_02  
   DECRYPTION BY ASYMMETRIC KEY SSN_AKey;  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber2  
    = EncryptByKey(Key_GUID('SSN_Key_02'), NationalIDNumber);  
GO  
--Close the key used to encrypt the data.  
CLOSE SYMMETRIC KEY SSN_Key_02;  
--  
--There are two ways to decrypt the stored data.  
--  
--OPTION ONE, using DecryptByKey()  
--1. Open the symmetric key.  
--2. Decrypt the data.  
--3. Close the symmetric key.  
OPEN SYMMETRIC KEY SSN_Key_02  
   DECRYPTION BY ASYMMETRIC KEY SSN_AKey;  
SELECT NationalIDNumber, EncryptedNationalIDNumber2    
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber2))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
CLOSE SYMMETRIC KEY SSN_Key_02;  
--  
--OPTION TWO, using DecryptByKeyAutoAsymKey()  
SELECT NationalIDNumber, EncryptedNationalIDNumber2   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('SSN_AKey') , NULL ,EncryptedNationalIDNumber2))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

Delen via