Prevent opening each time the symmetric key

Question

Please how we can create a symmetric key from provider with SQL Database azure step by step.

We need to use the symmetric key from provider to prevent opening/closing each time the key.

Example:

CREATE Symmetric KEY CONTOSO_KEY  
    FROM PROVIDER [AzureKeyVault_EKM_Prov]
    WITH PROVIDER_KEY_NAME = 'ContosoMasterKey', 
    CREATION_DISPOSITION = OPEN_EXISTING 

Then without open statement use "ENCRYPTBYKEY"

SELECT @DATA = ENCRYPTBYKEY(KEY_GUID('DATA_ENCRYPTION_KEY'), CONVERT(VARBINARY,'Plain text data to encrypt'));

I've created Azure key vault in Azure portal and provider in SQL but didn't know how to link the SQL connector with the provider and how to create the symmetric key

(we are using an ASP.net application with SQL and we have encrypt/decrypt function in C# used in SQL through CLR)

Answer 1

Per the documentation, the key must be open.

EncryptByKey uses a symmetric key. This key must be open. If the symmetric key is already open in the current session, you do not have to open it again in the context of the query.

https://docs.microsoft.com/en-us/sql/t-sql/functions/encryptbykey-transact-sql?msclkid=17c8891fb0f311ec8569b89832d831b3&view=sql-server-ver15