| title | DECRYPTBYASYMKEY (Transact-SQL) | Microsoft Docs | |||
|---|---|---|---|---|
| ms.custom | ||||
| ms.date | 03/06/2017 | |||
| ms.prod | sql-non-specified | |||
| ms.reviewer | ||||
| ms.suite | ||||
| ms.technology |
|
|||
| ms.tgt_pltfrm | ||||
| ms.topic | language-reference | |||
| f1_keywords |
|
|||
| dev_langs |
|
|||
| helpviewer_keywords |
|
|||
| ms.assetid | d9ebcd30-f01c-4cfe-b95e-ffe6ea13788b | |||
| caps.latest.revision | 34 | |||
| author | BYHAM | |||
| ms.author | rickbyh | |||
| manager | jhubbard | |||
| ms.workload | Inactive |
[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx_md]
Decrypts data with an asymmetric key.
Transact-SQL Syntax Conventions
DecryptByAsymKey (Asym_Key_ID , { 'ciphertext' | @ciphertext }
[ , 'Asym_Key_Password' ] )
Asym_Key_ID
Is the ID of an asymmetric key in the database. Asym_Key_ID is int.
ciphertext
Is a string of data that has been encrypted with the asymmetric key.
@ciphertext
Is a variable of type varbinary that contains data that has been encrypted with the asymmetric key.
Asym_Key_Password
Is the password that was used to encrypt the asymmetric key in the database.
varbinary with a maximum size of 8,000 bytes.
Encryption/decryption with an asymmetric key is very costly compared to encryption/decryption with a symmetric key. We do not recommend using an asymmetric key when you work with large datasets, such as user data in tables.
Requires CONTROL permission on the asymmetric key.
The following example decrypts ciphertext that was encrypted with asymmetric key JanainaAsymKey02, which was stored in AdventureWorks2012.ProtectedData04. The returned data is decrypted using asymmetric key JanainaAsymKey02, which has been decrypted with password pGFD4bb925DGvbd2439587y. The plaintext is converted to type nvarchar.
SELECT CONVERT(nvarchar(max),
DecryptByAsymKey( AsymKey_Id('JanainaAsymKey02'),
ProtectedData, N'pGFD4bb925DGvbd2439587y' ))
AS DecryptedData
FROM [AdventureWorks2012].[Sales].[ProtectedData04]
WHERE Description = N'encrypted by asym key''JanainaAsymKey02''';
GO
ENCRYPTBYASYMKEY (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)
Choose an Encryption Algorithm
Encryption Hierarchy