DECRYPTBYASYMKEY (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This function uses an asymmetric key to decrypt encrypted data.
Transact-SQL syntax conventions
Syntax
DecryptByAsymKey (Asym_Key_ID , { 'ciphertext' | @ciphertext }
[ , 'Asym_Key_Password' ] )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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
See Also
ENCRYPTBYASYMKEY (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)
Choose an Encryption Algorithm
Encryption Hierarchy