This post is similar to another question listed here. Encrypting data by OPEN SYMMETRIC KEY by ASYMMETRIC KEY created with Azure provider in SQL server takes more than 2 min
We are also facing the similar problem on our PROD server randomly, did anyone find a fix for this one. Below is the SQL statement we are executing with timestamp, randomly the time taken between the statement print 'before open ....' and print 'after open ....' is more than a minute and at a time is less than a second.
print 'before open ' + convert(varchar(50),getdate(),114)
OPEN SYMMETRIC KEY SYMMETRIC_KEY_NAME BY ASYMMETRIC KEY ASYMMETRIC_KEY_MECHANISM;
print 'after open ' + convert(varchar(50),getdate(),114)
-- SQL Select query
CLOSE SYMMETRIC KEY SYMMETRIC_KEY_NAME
print 'after close ' + convert(varchar(50),getdate())
Few findings from our end.
There is no error reported, just a continous info logged in eventviewer
The description for Event ID 0 from source SQL Server Connector for Microsoft Azure Key Vault cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
Vault Name: vaultname.vault.azure.net Operation: Constructor Key Name: Message: [error:0, info:0, state:0] The Operation was successful.
the message resource is present but the message is not found in the string/message table
There are outbound network calls in firewall logs which connects to Microsoft Azure, and there are continous denials (resets) from Microsoft. The same setup works on our lower Test environment, just PROD is affected. Already raised this issue in another forum. https://learn.microsoft.com/en-us/answers/questions/1275244/sql-decryption-randomly-slow?page=1&orderby=Helpful&comment=answer-1245040#newest-answer-comment
