How to fix Recovery Pending databases in Synapse SQL Serverless pool.

Question

I have two serverless databases in my Synapse Analytics Workspace. From this morning onwards both databases went into a Recovery Pending status but I do not seem to be able to make any changes to the status due to the fact that it is serverless and completely controlled by Microsoft.

What can I do to get more information on why it is not recovering?

Or otherwise what can I do to change the status?

Answer 1

Hello Jonathan Wols | Vintus,

I'm glad that your issue was resolved by the support team and thank you for sharing the resolution. This helps others experiencing the same issue.

Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue:

• Synapse SQL Serverless pool databases went into recovery pending

Solution:

• The issue was resolved by the support team. The cause of the issue was a backend service that failed to rotate the System Assigned Managed Identity Certificate. The support team managed to resolve it by manually resetting the certificate, which led the Serverless Pool starting up correctly again

If you have any other questions or are still running into more issues, please let me know. Thank you again for your time and patience throughout this issue.

Please remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution.

Answer 2

Thanks for all the ideas that you proposed however they unfortunately did not resolve my issue.

I send a technical support request and got in contact with the product team of Synapse to resolve this issue. The cause of the issue was a back end service that failed to rotate the System Assigned Managed Identity Certificate. They managed to resolve it by manually resetting the certificate leading to the Serverless Pool starting up correctly again.

Answer 3

Hello Jonathan Wols | Vintus,

Welcome to the Microsoft Q&A forum.

Scenario 1:

If customers changed the encryption from SAMI(System aligned managed identity) to UAMI

Using UAMI 'User Assigned managed identity' + Firewall and this is not supported.

Reference document

https://docs.microsoft.com/en-us/azure/synapse-analytics/security/workspaces-encryption#using-a-user-assigned-managed-identity

and We do not support UAMI CMK with firewall on AKV https://docs.microsoft.com/en-us/azure/synapse-analytics/security/workspaces-encryption#using-a-user-assigned-managed-identity

Mitigation

1. Remove firewall from AKV
2. Deactivate and activate ondemand pool. - this should recover all databases.
3. After that we should change the identity to System assigned.
4. Validate that both ondemand and dedicated pool use System assigned. Configure firewall on AKV

Scenario 2:

The customer Removed the encryption and this encryption is activly in use.

If customers made the key which was used for CMK encryption unavailable for the Managed Identity.

This prevented DB startup, as DB could not be decrypted without the key.

The necessary permissions for accessing the key are covered by the below documentation: https://docs.microsoft.com/en-us/azure/synapse-analytics/security/workspaces-encryption#key-access-and-workspace-activation

https://docs.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-byok-overview?view=azuresql#accidental-tde-protector-access-revocation

Scenario 3:

Customer disabled a key version in AKV which was still in use. Reenabling the key version should resolve the issue.

I hope this helps.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.