ENCRYPTBYASYMKEY (Transact-SQL)

Encrypts data with an asymmetric key.

Transact-SQL Syntax Conventions

Syntax

EncryptByAsymKey ( Asym_Key_ID , { 'plaintext' | @plaintext } )

Arguments

• Asym_Key_ID
  Is the ID of an asymmetric key in the database. int.

• cleartext
  Is a string of data that will be encrypted with the asymmetric key.

• @plaintext
  Is a variable of type nvarchar, char, varchar, binary, varbinary, or nchar that contains data to be encrypted with the asymmetric key.

Return Types

varbinary with a maximum size of 8,000 bytes.

Remarks

Encryption and decryption with an asymmetric key is very costly compared with encryption and decryption with a symmetric key. We recommend that you not encrypt large datasets, such as user data in tables, using an asymmetric key. Instead, you should encrypt the data using a strong symmetric key and encrypt the symmetric key using an asymmetric key.

EncryptByAsymKey return NULL if the input exceeds a certain number of bytes, depending on the algorithm. The limits are: a 512 bit RSA key can encrypt up to 53 bytes, a 1024 bit key can encrypt up to 117 bytes, and a 2048 bit key can encrypt up to 245 bytes. (Note that in SQL Server, both certificates and asymmetric keys are wrappers over RSA keys.)

Examples

The following example encrypts the text stored in @cleartext with the asymmetric key JanainaAsymKey02. The encrypted data is inserted into the ProtectedData04 table.

INSERT INTO AdventureWorks2012.Sales.ProtectedData04 
    VALUES( N'Data encrypted by asymmetric key ''JanainaAsymKey02''',
    EncryptByAsymKey(AsymKey_ID('JanainaAsymKey02'), @cleartext) );
GO

See Also

Reference

DECRYPTBYASYMKEY (Transact-SQL)

CREATE ASYMMETRIC KEY (Transact-SQL)

Concepts

Encryption Hierarchy