Have created a SYMMETRIC KEY for decryption by ASYMMETRIC KEY and the ASYMMETRIC KEY is created with Azure keyvault based key provider. I have used the details provided from Here and Here
I have used the 'SQL Server Connector for Microsoft Azure Key Vault' and then created a credential with the Azure Key Vault identity. I created an ASYMMETRIC KEY with the key present on the Azure Key Vault Created a SYMMETRIC KEY
CREATE SYMMETRIC KEY DATA_ENCRYPTION_KEY
WITH ALGORITHM=AES_256
ENCRYPTION BY ASYMMETRIC KEY CONTOSO_KEY;
In a stored procedure I am opening the Symmetric key.
--Open the symmetric key for use in this session
OPEN SYMMETRIC KEY DATA_ENCRYPTION_KEY
DECRYPTION BY ASYMMETRIC KEY CONTOSO_KEY;
SELECT @DATA = ENCRYPTBYKEY(KEY_GUID('DATA_ENCRYPTION_KEY'), CONVERT(VARBINARY, @xmlString));
INSERT INTO MYTable
(
Data
)
Value
(
@DATA
)
--Close the symmetric key
CLOSE SYMMETRIC KEY DATA_ENCRYPTION_KEY;
When I call this stored procedure form code with the ExecuteDataSet I get a timeout error with SQL execution. As soon I run the same SP on DB once and a session is created the next requests work fine until the session is active, and then the timeout occurs again.
When I run the stored procedure form the SQL Management Studio, the query execution completes in 2:15 minutes. I added form tracing to capture timings and I see that the 'OPEN SYMMETRIC KEY' takes up all the time. If you see the table below between the first log entry to second where it opens the key it takes more than 2 minutes.
Id Message time
**84 messages created 2022-11-16 15:13:09.027
84 opened symmetric key 2022-11-16 15:15:24.463**
84 encrypted with symmetric key 2022-11-16 15:15:24.467
84 encrypted data inserted 2022-11-16 15:15:24.470
84 Closed symmetric key 2022-11-16 15:15:24.470
