Azure SQL Dbs are in an inaccessible state on the Azure Portal

April 4, 2023

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.