Redigera

Dela via

ENCRYPTBYASYMKEY (Transact-SQL)

  • Artikel

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

This function encrypts data with an asymmetric key.

Transact-SQL syntax conventions

Syntax

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

Arguments

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

cleartext
A string of data that ENCRYPTBYASYMKEY will encrypt with the asymmetric key. cleartext can have a

  • binary
  • char
  • nchar
  • nvarchar
  • varbinary

or

  • varchar

data type.

@plaintext
A variable holding a value that ENCRYPTBYASYMKEY will encrypt with the asymmetric key. @plaintext can have a

  • binary
  • char
  • nchar
  • nvarchar
  • varbinary

or

  • varchar

data type.

Return Types

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

Remarks

Encryption and decryption operations that use asymmetric keys consume significant resources, and so become expensive compared with symmetric key encryption and decryption. We suggest that developers avoid asymmetric key encryption and decryption operations on large datasets - for example, user data datasets stored in database tables. Instead, we suggest that developers first encrypt that data with a strong symmetric key, and then encrypt that symmetric key with an asymmetric key.

Depending on the algorithm, ENCRYPTBYASYMKEY returns NULL if the input exceeds a certain number of bytes. The specific limits:

  • a 512-bit RSA key can encrypt up to 53 bytes
  • a 1024-bit key can encrypt up to 117 bytes
  • a 2048-bit key can encrypt up to 245 bytes

In SQL Server, both certificates and asymmetric keys serve as wrappers over RSA keys.

Examples

This example encrypts the text stored in @cleartext with the asymmetric key JanainaAsymKey02. The statement inserts the encrypted data into the ProtectedData04 table.

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

See Also

DECRYPTBYASYMKEY (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
Encryption Hierarchy