1 out of 2 rated this helpful - Rate this topic

DecryptByKeyAutoAsymKey (Transact-SQL)

SQL Server 2005

New: 14 April 2006

Decrypts using a symmetric key that is automatically decrypted using an asymmetric key.

Topic link icon Transact-SQL Syntax Conventions


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

Arguments

akey_ID: Is the ID of the asymmetric key that is used to protect the symmetric key. int.

akey_password: Is the password that protects the private key of the asymmetric key. Can be NULL if the private key is protected by the database master key. varchar.

' ciphertext ': Is the data that was encrypted with the key. varbinary.

@ciphertext: Is a variable of type varbinary that contains data that was encrypted with the key.

add_authenticator: Indicates whether an authenticator was encrypted together with the plaintext. Must be the same value that is passed to EncryptByKey when encrypting the data. Is 1 if an authenticator was used. int.

@add_authenticator: Indicates whether an authenticator was encrypted together with the plaintext. Must be the same value that is passed to EncryptByKey when encrypting the data.

authenticator: Is the data from which to generate an authenticator. Must match the value that was supplied to EncryptByKey. sysname.

@authenticator: Is a variable that contains data from which to generate an authenticator. Must match the value that was supplied to EncryptByKey.

Return Types

varbinary with a maximum size of 8,000 bytes.

Remarks

DecryptByKeyAutoAsymKey combines the functionality of OPEN SYMMETRIC KEY and DecryptByKey. In a single operation, it decrypts a symmetric key and uses that key to decrypt ciphertext.

Permissions

Requires membership in the public role.

Examples

The following example shows how DecryptByKeyAutoAsymKey can be used to simplify code that performs a decryption. This code should be run on a freshly installed copy of the AdventureWorks database.

--Create the keys and certificate.
USE AdventureWorks;
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

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD

DecryptByKeyAutoAsymKey (Transact-SQL)

Reference

Other Resources

Help and Information

Community Additions