Skip to content

Latest commit

 

History

History
95 lines (71 loc) · 3.23 KB

encryptbyasymkey-transact-sql.md

File metadata and controls

95 lines (71 loc) · 3.23 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
ENCRYPTBYASYMKEY (Transact-SQL)
ENCRYPTBYASYMKEY (Transact-SQL)
VanMSFT
vanto
03/14/2017
sql
t-sql
reference
ENCRYPTBYASYMKEY
ENCRYPTBYASYMKEY_TSQL
ENCRYPTBYASYMKEY function
encryption [SQL Server], asymmetric keys
asymmetric keys [SQL Server], ENCRYPTBYASYMKEY function
TSQL

ENCRYPTBYASYMKEY (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

This function encrypts data with an asymmetric key.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 [!INCLUDEssNoVersion], 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