This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
When I am not able to connect to the Azure SQL db, and I get this error message,
My first step would be to check the status of the database on the Azure Portal, and in this case, I can see that the dbs are in inaccessible state.
When I check the Activity logs, I see the following events:
If I click on the JSON events, I can see the reasons why the dbs are inaccessible
and there's another status which shows that the dbs are waiting for access to be reestablished again.
When I click on Revalidate Key, at the bottom of the screen, it gives the correct error.
Now when I go to Identity, I can see that the System assigned managed identity was turned to off.
Resolution
===========
Step 1 : Turn it on.When I try to turn it on I get the following error msg
Step 2: The way to resolve this error is to go back to the Azure key vault access policies and then add the system assigned managed identity with the correct permissions: Get, Wrap and Unwrap keys.
Step 3: Click on Create. Select the permissions and then click Next
Step 4: Use the system assigned managed identity and then click on Next
Step 5: Click on Create.
Step 6: Once the access policy was created successfully, you can go back to the Identity tab of the SQL Server and then see the System assigned managed identity has been turned to On automatically.
Step7: Go back to TDE settings and click on Revalidate Key.
Step 8: You will see the access is back. Depending on the size of the dbs, it will take some time for the restoration process.
Step 9: Once the restoration process is completed, you will get a notification: Key Access restored.
Step 10: Dbs will be back in Online state as well and now you should be able to connect to the dbs as well.
Refer: Common errors with customer-managed keys in Azure Key Vault - SQL Server | Microsoft Learn