DECRYPTBYASYMKEY (Transact-SQL)

2024-09-03

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This function uses an asymmetric key to decrypt encrypted data.

Syntax

DecryptByAsymKey (Asym_Key_ID , { 'ciphertext' | @ciphertext }   
    [ , 'Asym_Key_Password' ] )

Arguments

Asym_Key_ID
The ID of an asymmetric key in the database. Asym_Key_ID has an int data type.

ciphertext
The string of data encrypted with the asymmetric key.

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

Asym_Key_Password
The password used to encrypt the asymmetric key in the database.

Return Types

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

Remarks

Compared to symmetric encryption / decryption, asymmetric key encryption / decryption has a high cost. When working with large datasets - for example, user data stored in tables - we suggest that developers avoid asymmetric key encryption / decryption.

Permissions

DECRYPTBYASYMKEY requires CONTROL permission on the asymmetric key.

Examples

This example decrypts ciphertext originally encrypted with asymmetric key JanainaAsymKey02. AdventureWorks2022.ProtectedData04 stored this asymmetric key. The example decrypted the returned data with asymmetric key JanainaAsymKey02. The example used password pGFD4bb925DGvbd2439587y to decrypt this asymmetric key. The example converted the returned plaintext to type nvarchar.

SELECT CONVERT(NVARCHAR(max),  
    DecryptByAsymKey( AsymKey_Id('JanainaAsymKey02'),   
    ProtectedData, N'pGFD4bb925DGvbd2439587y' ))   
AS DecryptedData   
FROM [AdventureWorks2022].[Sales].[ProtectedData04]   
WHERE Description = N'encrypted by asym key''JanainaAsymKey02''';  
GO

Feedback

Was this page helpful?