decryptbykey-transact-sql.md

title	DECRYPTBYKEY (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/06/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database
ms.service
ms.component	t-sql|functions
ms.reviewer
ms.suite	sql
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	DecryptByKey_TSQL DECRYPTBYKEY
dev_langs	TSQL
helpviewer_keywords	symmetric keys [SQL Server], DecryptByKey function decryption [SQL Server], keys decryption [SQL Server], symmetric keys DECRYPTBYKEY function
ms.assetid	6edf121f-ac62-4dae-90e6-6938f32603c9
caps.latest.revision	39
author	edmacauley
ms.author	edmaca
manager	craigg
ms.workload	On Demand

DECRYPTBYKEY (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]

Decrypts data by using a symmetric key.

Transact-SQL Syntax Conventions

Syntax

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

Arguments

ciphertext
Is data that has been encrypted with the key. ciphertext is varbinary.

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

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

authenticator
Is data from which to generate an authenticator. Must match the value that was supplied to EncryptByKey. authenticator is 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.

Returns NULL if the symmetric key used for encrypting the data is not open or the ciphertext is NULL.

Remarks

DecryptByKey uses a symmetric key. This symmetric key must already be open in the database. There can be multiple keys open at the same time. You do not have to open the key immediately before decrypting the cipher text.

Symmetric encryption and decryption is relatively fast, and is suitable for working with large amounts of data.

Permissions

Requires the symmetric key to have been opened in the current session. For more information, see OPEN SYMMETRIC KEY (Transact-SQL).

Examples

A. Decrypting by using a symmetric key

The following example decrypts ciphertext by using a symmetric key.

-- First, open the symmetric key with which to decrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
GO  
  
-- Now list the original ID, the encrypted ID, and the   
-- decrypted ciphertext. If the decryption worked, the original  
-- and the decrypted ID will match.  
SELECT NationalIDNumber, EncryptedNationalID   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalID))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO  

B. Decrypting by using a symmetric key and an authenticating hash

The following example decrypts data that was encrypted together with an authenticator.

-- First, open the symmetric key with which to decrypt the data  
OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  
GO  
  
-- Now list the original card number, the encrypted card number,  
-- and the decrypted ciphertext. If the decryption worked,   
-- the original number will match the decrypted number.  
SELECT CardNumber, CardNumber_Encrypted   
    AS 'Encrypted card number', CONVERT(nvarchar,  
    DecryptByKey(CardNumber_Encrypted, 1 ,   
    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))   
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO  
  

See Also

ENCRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Encryption Hierarchy
Choose an Encryption Algorithm